Mysql AES_DECRYPT(AES_ENCRYPT())

Question

I have a problem with mysql encryption, on a mysql server 5.5.38

If I do :

 SELECT AES_DECRYPT(AES_ENCRYPT("test", "123"), "123");

the result is 74657374

I saw in the mysql doc that DECODE() and ENCODE() are deprecated and that we are encouraged to use AES functions :

The ENCODE() and DECODE() functions are deprecated in MySQL 5.7, will be removed in a future MySQL release, and should no longer be used. Consider using AES_ENCRYPT() and AES_DECRYPT() instead.

Note : when I use SELECT DECODE(ENCODE("test", "123"), "123"), the result is the same : 74657374

I believe an AES encryption key is meant to be 32 characters long — samlev, Aug 25 '15 at 16:16
`74657374` looks like the ascii/hex characters for "test": 74-65-73-74. How are you running this query/displaying the results? It looks like your data is being cast into a different format. — gen_Eric, Aug 25 '15 at 16:20
Using a 32 character long key does the same : SELECT AES_DECRYPT(AES_ENCRYPT("test", "12345678901234567890123456789011"), "12345678901234567890123456789011"); — lsmpascal, Aug 25 '15 at 16:29
For the ascii/hex format, yes, it's surely involved in the problem, but I can't figure out where. — lsmpascal, Aug 25 '15 at 16:31

score 2 · Answer 1 · edited May 23 '17 at 12:22

2

Ok I have my answer :

SELECT CAST(AES_DECRYPT(AES_ENCRYPT('test admin','1234'),'1234') AS CHAR (50))

It was a cast issue.

Explanations here : How to use AES_ENCRYPT and AES_DECRYPT in mysql

edited May 23 '17 at 12:22

Community

1
1

answered Aug 25 '15 at 16:40

lsmpascal

762
8
24

1

So this is a duplicate then. No need to post an answer. You should able so see a button above your question to confirm that that is a duplicate. – Artjom B. Aug 25 '15 at 16:49
You're totally right. I'm using stack for years but some of the features and best practices are still weird for me. Sorry. – lsmpascal Aug 25 '15 at 16:56

score 0 · Answer 2 · answered Aug 25 '15 at 16:21

0

It's working correctly.

74657374 is a Hexidecimal representation of the string 'test':

74 = t
65 = e
73 = s
74 = t

Try using unhex:

SELECT UNHEX(AES_DECRYPT(AES_ENCRYPT("test", "123"), "123"));

answered Aug 25 '15 at 16:21

samlev

5,852
1
26
38

1

I thought it was the solution, but no, the UNHEX query returns NULL. – lsmpascal Aug 25 '15 at 16:33

Mysql AES_DECRYPT(AES_ENCRYPT())

2 Answers2