Hashing Algorithm in MySQL PASSWORD()

Investigating the MySQL PASSWORD() Hashing Algorithm

Recently we had a question from a customer: what is the hashing algorithm implemented in PASSWORD()? The manual doesn't give a straight answer in any of these two pages:

• MySQL Encryption Functions
• MySQL Password Hashing

It is enough to dig a bit more to find the solution in the MySQL Internals Client/Server Protocol that specifies:

"mysql.user.Password stores SHA1(SHA1(password))"

The Discrepancy: When Initial Tests Fail to Match Documentation

Instead of blindly trusting the documentation (even if I believe it is correct), I did some tests and was confused by the first result:

mysql> SELECT PASSWORD("this_is_a_random_string") `pass`\G pass: *12E76A751EFA43A177049262A2EE36DA327D8E50  mysql> SELECT SHA1(SHA1("this_is_a_random_string")) `pass`\G pass: 9b653fd9fb63e1655786bfa3b3e00b0913dfc177 

So it looked like SHA1(SHA1(password)) wasn't PASSWORD(password)), at least in this test.

Cracking the Case: The Binary SHA1 and UNHEX() Solution

The best documentation ever is the source code, so I read the source code and understood why my previous test was incorrect: the second SHA1() is applied to the binary data returned by the first SHA1() and not to its hex representation. Therefore, in SQL I have to UNHEX() it before applying the second SHA1.

In fact:

mysql> SELECT SHA1(UNHEX(SHA1("this_is_a_random_string"))) `pass`\G pass: 12e76a751efa43a177049262a2ee36da327d8e50 

So yes, I confirmed that mysql.user.password stores SHA1(SHA1(password)). I also hope this post is useful to understand how MySQL implements PASSWORD().

MySQL Database Consulting Services

Ready to optimize your MySQL Database for the future?