AES_ENCRYPT and AES_DECRYPT won’t work on MySQL 5.7

Question

When I run the following code I simply get NULL for both outputs. I have also tried using unhex() around the first decryption argument, but still the same result.

SELECT @encrypt = AES_ENCRYPT('hello', 'key');

SELECT @decrypt = AES_DECRYPT(@encrypt, 'key');

Why is this not working?

Answer 1

You are missing the : operator

query should be :

SELECT @encrypt := AES_ENCRYPT('hello', 'key');

SELECT @decrypt := AES_DECRYPT(@encrypt, 'key');

For the detailed explanation, read the mysql documentation for user defined variables.

While we are at it, AES_ENCRYPT and AES_DECRYPT return binary string in hexadecimal format. So if you want to get string value, cast it as char:

SELECT @encrypt := CAST(AES_ENCRYPT('hello', 'key') AS CHAR);

SELECT @decrypt := CAST(AES_DECRYPT(@encrypt, 'key') AS CHAR);