Search code examples
mysqlaesmysql-5.7

AES_ENCRYPT and AES_DECRYPT won’t work on MySQL 5.7

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?

Solution

  • 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);