Does MySQL Send Passwords In the Clear?
I was asked this question recently, and I thought it was a great little tidbit of knowledge to pass along. The short answer is “no”. The slightly longer answer was written up by Jan Kneschke when dealing with a forum post about proxy + connection pooling.
From http://forums.mysql.com/read.php?146,169265,169700
The clear-text password is _never_ transfered in the authentication phase.
On the network we have:
* client connects to server (no data)
* server sends a seed (40 char, one-time, random)
* client sends 40 char hash of (seed + PASSWORD(clear-text-password))
* server compares against the hash(seed + SELECT password FROM mysql.user WHERE username =) That way we never have the password as clear-text on the wire. (only in SET PASSWORD or GRANT statements).
Right; MySQL authentication never transmits passwords in the clear, but many web apps use their own authentication instead of MySQL passwords. It’s up to the application developer to encode passwords instead of transmitting them in the clear. In other words, what’s the difference between the following two queries:
SELECT (MD5(?) = a.password_hash) AS is_match FROM accounts a WHERE a.login = ?;
– send password in the clear and encode it in the SQL engine
SELECT (? = a.password_hash) AS is_match FROM accounts a WHERE a.login = ?;
– encode password in the application and send MD5 digest to the RDBMS
I kind of wish that the set password and grant weren’t put in clear text. Anyway to get the binary log to add the encrypted version of the password?
@Tom Krouper
It does already…
$ ./bin/mysqlbinlog data/katzs-binlog.000001 | grep PASSWORD
SET PASSWORD FOR ‘root’@'localhost’='*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA’/*!*/;