MYSQL | AES encrypt () / decrypt ()
I need to encrypt a string in a decrypted way. The best way is this password to get some protection. Security is not important here.
I have selected AES_ENCRYPT () so far, but I cannot decrypt it.
Encryption: SELECT AES_ENCRYPT('test', 'test')
Output: 87bd903885943be48a4e68ab63b0ec6a
Decrypt: SELECT AES_DECRYPT('87bd903885943be48a4e68ab63b0ec6a', 'test')
Conclusion: NULL
!
Simple question: Why the hell can't I decipher it? Couldn't find anything about this on the internet.
If the solutions get too big (I like it simple) I would be fine with a different encryption method too.
Thank you so much!
MySQL-Client-Version: 5.5.41
source to share
First, you need to convert the hex string to binary data:
SELECT AES_DECRYPT(UNHEX('87bd903885943be48a4e68ab63b0ec6a'), 'test') FROM DUAL;
Actually, I'm surprised your original operator SELECT
returned the 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 the 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)
source to share
MySql Workbench has an option in Editor / Preferences / Sql Editor / Sql Execution, check the box
[X] Treat BINARY/VARBINARY as nonbinary character string
Then restart the Workbench.
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 pain, even though I mentioned at the beginning of this answer, but ok. Of course this works fine in the mysql client and not in the Workbench.
Just thought I'd share this, feel your pain in your other question link . Client / version type.
source to share