Is it safe to use the ENCRYPT() function in MySQL to hash passwords?

Question

I am running a ProFTPD Server on Ubuntu 12.04 and do the user authentication with MySQL. I am looking for a secure way to hash passwords.

There a lot of built-in hash functions in MySQL, but a lot of them are deprecated, such as MD5 or SHA1. The best built-in hash function in MySQL I saw was encrypt, that uses the Unix function crypt. A positive aspect of encrypt is that the hash could be generated with salt.

According to the MySQL manual, encrypt is deprecated because it uses DES and it will be removed in a future version. But according to this question only the old version of the Unix crypt function uses DES and is outdated. Newer versions of the crypt function should be fine.

Do you recommend to use encrypt in MySQL? Does encrypt create a secure hash?

The notes in the link you provided tell you what encrypt function you should use instead. — schroeder, Feb 08 '17 at 08:05
Thank you for your comment. But I think it's not good to use AES_encrypt(), because AES is a two-way-encryption function and "encrypt()" is a one-way-hash-function. And for securely store passwords, it's better to use hash, instead of encryption. — Wubi, Feb 08 '17 at 08:10
Please, read the answer to the question you linked yourself. — Stephane, Feb 08 '17 at 08:20
Possible duplicate of [How to securely hash passwords?](http://security.stackexchange.com/questions/211/how-to-securely-hash-passwords) — Stephane, Feb 08 '17 at 08:20
@Wubi you have put yourself into a loop of logic errors. MySQL has a password hashing function: https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html#function_password Just manually add a salt. — schroeder, Feb 08 '17 at 08:28
The mysql manual says that you should not use the PASSWORD() function in your own application, because PASSWORD() is used by the authentication system of mysql. And PASSWORD() is also deprecated. I also read the forum post of how you can securely hash your own password and there, they write you could use the mysql ENCRYPT() function, because only older version of ENCRYPT() are insecure. But the mysql manual says that the ENCRYPT() in general is insecure and deprecated. So now I don't know, if ENCRYPT() is secure or not. — Wubi, Feb 08 '17 at 08:42
Then your question still appears to be confused. Either you are asking if the function is secure (the developers themselves say that it is not, which you appear to respect in other areas) or you are asking how to securely hash passwords for storage, which is covered by the Sec.SE question you linked. If you do not trust the built-in function, why not use the replacement? If you do not want to use `password()` why not use the recommended replacement? — schroeder, Feb 08 '17 at 09:38
The confusion ALSO lies in why you'd want to use the `encrypt()` function at all. If you want to hash, use the hashing functions. — schroeder, Feb 08 '17 at 09:43
But I still don't know why my question should be confusing. My goal is to securely hash passwords in mysql. And a lot of built-in hash function have the problem, that they are depreacted and insecure (MD5, SHA1, PASSWORD()). Also the ENCRYPT() is, according to the MySQL developer, depreacted. But according to the forum post I post above, only older ENCRYPT() function are insecure. — Wubi, Feb 08 '17 at 09:44

score 6 · Accepted Answer · answered Feb 08 '17 at 10:04

Yes, it is possible to make the MySQL ENCRYPT function more secure.

As you mentionted, ENCRYPT calls crypt internally. The crypt function uses DES encryption by default, which is insecure. However, it can be made to use other algorithms by passing specific values as the salt. For example, passing a salt starting with $6$ will use a variation on SHA512 on supporting systems.

mysql> SELECT ENCRYPT('abc', '$6$abc');
+-----------------------------------------------------------------------------------------------+
| ENCRYPT('abc', '$6$abc')                                                                      |
+-----------------------------------------------------------------------------------------------+
| $6$abc$feY2G1TnANZ0KTBaV0Kkb3kO0521w9Wfvr8bW8wL0T11tXMxEhkG9poIhCuNFR3zasFDn.iplGDXaEJxlLwPt0 |
+-----------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

As you can see this is a significant longer hash than the one you get with the default DES. However, keep in mind that you need to create a random salt if you create a new password.

SELECT ENCRYPT('abc', CONCAT('$6$', SHA2(RANDOM_BYTES(64), '256')));

To verify the password, you can use the whole hash as salt:

SELECT ENCRYPT('abc', '$6$e1c69ed016381250$5g0Bzbh29DWXdCdy5m0IhKolTwCqZXpElB2ISK.mUwe9ic.oA6zXoZ5CAiuASg8GYKGwljSCJrnxSZ.f76PFW/');

Thank you really much for your helpful answer. Now, I know that we can make ENCRYPT() more secure. But I ask myself, if it is reasonable to use ENCRYPT(), when the mysql dev remove it in further version. And why at all do they remove the ENCRYPT() function, when you write that we can make it more secure. — Wubi, Feb 08 '17 at 10:29
How can we use the AES_ENCRYPT() function to achieve the same result as described in your answer? The reason I'm asking it is to make the code still usable when MySQL removes ENCRYPT in the future versions. — Qian Chen, Sep 14 '17 at 09:00
The AES_ENCRYPT does something fundementally different than the ENCRYPT function, and can not be used in a straightforward way to correctly hash passwords. — Sjoerd, Sep 19 '17 at 09:04

Is it safe to use the ENCRYPT() function in MySQL to hash passwords?

1 Answers1