MySQL passwords SHA512 using AES_ENCRYPT instead of ENCRYPT

encryptionhashingMySQLpassword

A tutorial is asking me to encrypt passwords using SHA512 in the following manner:

INSERT INTO `vir_users`
(`id`, `domain_id`, `password` , `email`)
VALUES
('1', '1', ENCRYPT('pw1', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'email1@example.com'),
('2', '1', ENCRYPT('pw2', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'email2@example.com');

How would I replace this with a MySQL 5.7 non-deprecated AES_ENCRYPT instead of the old ENCRYPT way of doing things?

MySQL's documentation is severely lacking in this case at it does NOT provide any clear examples.

I found a way of doing it outside the DBMS, however I would like to keep it all contained and not dependant on external code and/or tools; that's why would like to know.

When I run this query now, I get this error message:

'ENCRYPT' is deprecated and will be removed in a future release. Please use AES_ENCRYPT instead

update:
The reason it's using this is because I need to store the password in such a way that Dovecot's default_pass_scheme (which is set to SHA512-CRYPT) can validate my passwords.

The $6$ part of the passwords seems to be important.. I don't know why, but all passwords that are generated the old way start with that. Please check the tutorial on why this is.

Best Answer

----- 2014-09-25 5.7.5 Milestone 15 -- Security Notes -- -----

Incompatible Change: MySQL 5.6 deprecated passwords that used the older pre-4.1 password hashing format. Support for these passwords is now removed, which involves the following changes. Applications that use any feature no longer supported must be modified.

Playing around with passwords seems like a dumb thing to have in a tutorial. Please skip that section and move on.

Related Question