0

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
2

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  - 
  • Thanks for your help. Do you know why there is different has from CLI? – HTF Jul 20 '13 at 23:29
  • What difference are you referring to? – ypercubeᵀᴹ Jul 21 '13 at 10:34
  • The hash that I've created via CLI (sha1sum) is different from the one from MySQL. – HTF Jul 21 '13 at 15:04
  • did you try applying sha1sum twice? – ypercubeᵀᴹ Jul 21 '13 at 15:05
0
  1. it isn't encryption, it's hashing, which is different.

  2. MD5 and SHA-1 are both broken and vulnerable and you should not use them for password hashing. Use BCrypt.

MD5 considered harmful

SHA-1 Broken

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.