Search code examples
mysqlaes

AES_ENCRYPT and AES_DECRYPT functions in MYSQL

I was trying the functions AES_ENCRYPT and AES_DECRYPT in MYSQL 8.0.19. When I run SELECT AES_ENCRYPT('foo','test'); I get 0x429292F7734FFE002C4E5B11239FD3A4, but when I run SELECT AES_DECRYPT('0x429292F7734FFE002C4E5B11239FD3A4','test'); I get 0x (instead of 'foo'). Why is the AES_DECRYPT function not working? Thank you all. Kind regards. NC

Solution

  • The input of aes_decrypt() needs to be a binary string not a character string. Since the return value of aes_decrypt() is also a binary string, you might need to cast it to a character string.

    Either don't quote the input.

    SELECT cast(aes_decrypt(0x429292F7734FFE002C4E5B11239FD3A4, 'test') AS char);

    Or remove the leading '0x' an prefix an x.

    SELECT cast(aes_decrypt(x'429292F7734FFE002C4E5B11239FD3A4', 'test') AS char);

    Or remove the leading '0x' and unhex() on the (character) string.

    SELECT cast(aes_decrypt(unhex('429292F7734FFE002C4E5B11239FD3A4'), 'test') AS char);