Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

2 Answers 2

up vote 2 down vote accepted

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  - 
share|improve this answer
    
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
  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

share|improve this answer
    
The relevant security.stackexchnage link regarding point 2 is How to securely hash passwords? –  Anti-weakpasswords Mar 1 at 8:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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