Mysql – Password encryption type

encryptionMySQLpassword

I would like to confirm if MySQL uses SHA1 for 'PASSSWORD' hashing and why test below doesn't work:

mysql> GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SELECT user,host,password FROM mysql.user WHERE user='test';
+------+------------+-------------------------------------------+
| user | host       | password                                  |
+------+------------+-------------------------------------------+
| test | localhost  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+------+------------+-------------------------------------------+
1 row in set (0.01 sec)

mysql> \q
Bye
[root@test ~]# echo -n "password" | sha1sum | awk '{print toupper($1)}'
5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8

Best Answer

The MySQL documentation Encryption and Compression Functions page has various functions that can be used for hashing passwords. It also clearly states that:

Note

The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, consider MD5() or SHA2() instead. Also see RFC 2195, section 2 (Challenge-Response Authentication Mechanism (CRAM)), for more information about handling passwords and authentication securely in your applications.

As for the other question, you can test at SQL-Fiddle that PASSWORD() (at least in versions 5.1, 5.5 and 5.6 that we can test at SQL-Fiddle) is implemented as applying SHA1() twice on the provided string:

SELECT 
  SHA1(UNHEX(SHA1('password'))),
  PASSWORD('password');

Thanks should go to Rene Cannao who has digged into the source code and provided the answer in the blog post: PalominoDB.com : Hashing Algorithm in MySQL PASSWORD()

There is also this, older answer on StackOverflow which is relevant: Simulating MySql's password() encryption using .NET or MS SQL

If you want to confim with Linux CLI, just tested at Ubuntu:

ypercube@Zeus:~$    echo -n "password" | sha1sum |
      perl -ne 's/([0-9a-f]{2})/print chr hex $1/gie' | sha1sum
2470c0c06dee42fd1618bb99005adca2ec9d1e19  -
Related Question