Site Reliability Engineering Ansible
Technical Track Open Source DevOps

Hashing Algorithm in MySQL PASSWORD()

by Pythian Marketing
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
Exporting the mysql.slow_log table into slow query log format
Site Reliability Engineering

Exporting the mysql.slow_log table into slow query log format

Oct 20, 2011 12:00:00 AM 1 min read
When the ALTER TABLE privilege is not enough to run ALTER TABLE

When the ALTER TABLE privilege is not enough to run ALTER TABLE

Mar 1, 2010 12:00:00 AM 2 min read
Installing Elasticsearch Using Ansible – the Quick Way!
Site Reliability Engineering

Installing Elasticsearch Using Ansible – the Quick Way!

Jan 20, 2021 12:00:00 AM 4 min read

Ready to unlock value from your data?

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

Speak with Pythian consultants now →

Follow Us     Pythian-LinkedIn   Pythian-Twitter   Pythian-FB

 

Pythian-logo-White

© 2025 All rights reserved.