Hashing Algorithm in MySQL PASSWORD()
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:
It is enough to dig a bit more to find the solution in http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#4.1_and_later that specifies "mysql.user.Password stores SHA1(SHA1(password))" .
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
mysql> SELECT SHA1(SHA1("this_is_a_random_string")) `pass`\G
So it looked like SHA1(SHA1(password)) wasn't PASSWORD(password)), at least in this test.
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
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().