CODEWITHSUNDEEP
mysqlencryptioncryptographyaes
C4d
C4d

Reputation: 3282

MYSQL | AES encrypt() / decrypt()

I need to encrypt a string in a decryptable way. Best way would be by a given password to get a little bit of protection in. Security isnt that important in here.

I picked out AES_ENCRYPT() so far but cant decrypt it.

Encrypt: SELECT AES_ENCRYPT('test', 'test')
Output : 87bd903885943be48a4e68ab63b0ec6a

Decrypt: SELECT AES_DECRYPT('87bd903885943be48a4e68ab63b0ec6a', 'test')
Output : NULL !

Simple question: Why the hell can't I decrypt it? Couldnt find anything about it online.

If the solutions becomes too big (I like it simple) I would also be fine with another method of encryption.

Thanks a lot!

MySQL-Client-Version: 5.5.41

Upvotes: 3

Views: 9538

Answers (2)

Drew
Drew

Reputation: 24959

In MySql Workbench there is a setting under Edit/Preferences/Sql Editor/Sql Execution, checkbox the [X] Treat BINARY/VARBINARY as nonbinary character string

Then a Workbench restart.

SELECT HEX(AES_ENCRYPT('secret message','myKey')) into @a FROM DUAL;
select @a; -- 'F5CF7120FF800ECEB4663785EFC19340'

SELECT AES_DECRYPT(UNHEX('F5CF7120FF800ECEB4663785EFC19340'), 'myKey') FROM DUAL;
-- secret message  (shows it fine)

SELECT AES_DECRYPT(unhex(@a), 'wrongKey') from dual;
-- NULL  (at least it is a tip-off that it failed)
SELECT AES_DECRYPT(unhex(@a), 'myKey') from dual;
-- BLOB

Now right click on BLOB, "Open Value in Viewer", go between Binary and Text tabs, see "secret message"

Kind of a pain, even despite what I mentioned at top of this Answer, but oh well. It works fine of course in mysql client as opposed to Workbench.

Just thought I would share that, feel your pain on your other question link. Sort of client/version specific.

Upvotes: 2

r3mainer
r3mainer

Reputation: 24557

You need to convert the hexadecimal string into binary data first:

SELECT AES_DECRYPT(UNHEX('87bd903885943be48a4e68ab63b0ec6a'), 'test') FROM DUAL;

Actually, I'm surprised your original SELECT statement returned a hex string in the first place. This is what I get:

mysql> SELECT AES_ENCRYPT('test','test') FROM DUAL;
+----------------------------+
| AES_ENCRYPT('test','test') |
+----------------------------+
| ???8??;?Nh?c??j                     |
+----------------------------+
1 row in set (0.02 sec)

I can only get a hex string if I call HEX() explicitly:

mysql> SELECT HEX(AES_ENCRYPT('test','test')) FROM DUAL;
+----------------------------------+
| HEX(AES_ENCRYPT('test','test'))  |
+----------------------------------+
| 87BD903885943BE48A4E68AB63B0EC6A |
+----------------------------------+
1 row in set (0.00 sec)

(This is in MySQL version 5.6.22)

Upvotes: 2

Related Questions