CODEWITHSUNDEEP
mysqlsecurityaes
serv92
serv92

Reputation: 121

How to use MySQL aes_encrypt and aes_decrypt?

I'm simply testing MySQL AES_ENCRYPT() and AES_DECRYPT() before I start using it in my app. So I write a simple query to test it like:

SELECT AES_DECRYPT(AES_ENCRYPT('SERV92','TESTTTTTTT'),'TESTTTTTTT') AS `TEST`

I get an error because there are to few parameters in AES_ENCRYPT() I do some research and find that my version(5.6) of MySQL does indeed take an extra parameter so I rewrite the query

SELECT AES_DECRYPT(AES_ENCRYPT('SERV92','TESTTTTTTT',RANDOM_BYTES(16)),'TESTTTTTTT',RANDOM_BYTES(16)) AS `TEST`

Result:

+-----------+
|    TEST   |
|-----------|
|    NULL   |
+-----------+

Important MySQL Variables:

block encryption mode=aes-256-cbc

I'm trying to use AES 256

Upvotes: 1

Views: 1676

Answers (2)

serv92
serv92

Reputation: 121

Okay I found the problem, AES_DECRYPT() returns data as a blob. Basically I just needed to tell it that it was utf8 text, as show below.

SET @a=RANDOM_BYTES(16); #Thanks Hartmut Holzgraefe
SELECT CONVERT(AES_DECRYPT(AES_ENCRYPT('SERV92','TESTTTTTTT',@a),'TESTTTTTTT',@a) USING utf8) AS `TEST`
+----------+
|   TEST   |
+----------+
|  SERV92  |
+----------+

Upvotes: 1

Hartmut Holzgraefe
Hartmut Holzgraefe

Reputation: 2765

You apparently need to use the same init_vector 3rd argument as this works:

> set @a=RANDOM_BYTES(16);
> SELECT AES_DECRYPT(AES_ENCRYPT('SERV92','TESTTTTTTT',@a),'TESTTTTTTT',@a) AS `TEST`;
+--------+
| TEST   |
+--------+
| SERV92 |
+--------+

In your case you used RANDOM_BYTES(16) twice so that different values are used in encrypt and decrypt.

Upvotes: 2

Related Questions