Hashing Algorithm in MySQL PASSWORD()

1 min read

Dec 4, 2011 12:00:00 AM

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: https://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_password https://dev.mysql.com/doc/refman/5.7/en/password-hashing.html It is enough to dig a bit more to find the solution in https://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 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. 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().

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Hashing Algorithm in MySQL PASSWORD()

Share this

Share this

More resources

Let DataGuard Broker Do ALL The Work

Exporting the mysql.slow_log table into slow query log format

Anthos: How Google changed the perception of hybrid cloud solutions

Ready to unlock value from your data?