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:

http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_password

http://dev.mysql.com/doc/refman/5.1/en/password-hashing.html

 

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 

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().

Comments

Nice find was looking for it for a long time

Aman
Tue, 07/30/2013 - 03:04

Rene -- this is great!  You should post this information to the manual pages -- once you login you can comment at the bottom of the manual page.  This is very useful to know, and I'm sure I will forget it when I really want to know it.....

Tue, 12/06/2011 - 11:57

Nice digging!I wonder about the double-SHA1. Cryptographically, there's no added security in invoking SHA1 twice instead of once.A minor advatntage would be that it would be less common to find lookup tables for the doubly-signed password than it is to singly-signed password (i.e. harder to google for "which password would encrypt as 'fb459a2103aa....').I always wondered about the PASSWORD() algorithm but never checked. Of course it's good that it relies on common, well-tested algorithms.

Mon, 12/05/2011 - 01:52

Ironically, the documentation in John the Ripper and Hashcat are very clear on which version of MySQL uses which algorithm ;)

Marcin Pohl
Sun, 12/04/2011 - 22:10

Reply

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.
Website by Digital Loom