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?

  • 1
    The notes in the link you provided tell you what encrypt function you should use instead. – schroeder Feb 8 '17 at 8:05
  • 1
    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 8 '17 at 8:10
  • 1
    Please, read the answer to the question you linked yourself. – Stephane Feb 8 '17 at 8:20
  • Possible duplicate of How to securely hash passwords? – Stephane Feb 8 '17 at 8:20
  • 1
    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 8 '17 at 8:42

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/');
  • 1
    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 8 '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 9: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 9:04

Your Answer

