CODEWITHSUNDEEP
mysqlencryptionaes
David Clough
David Clough

Reputation: 157

mysql aes_decrpyt returns incorrect value

Am using the following query in phpmyadmin on a XAMPP test environment:

SELECT AES_DECRYPT( AES_ENCRYPT('password1','secret'),'secret');

(see MySQL Encryption-Decryption Example AES DECRYPT

This query SHOULD return password1, however, it is actually returning 70617373776f726431

Any suggestions as to why?

Database server

Server: 127.0.0.1 via TCP/IP Server type: MySQL Server version: 5.6.16 - MySQL Community Server (GPL) Protocol version: 10 User: Server charset: UTF-8 Unicode (utf8)

Web server

Apache/2.4.9 (Win32) OpenSSL/1.0.1g PHP/5.5.11 Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503 - $Id: bf9ad53b11c9a57efdb1057292d73b928b8c5c77 $ PHP extension: mysqli

Upvotes: 2

Views: 1542

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179364

It looks like phpMyAdmin is trying to be "helpful."

70617373776f726431 is "password1" in hex.

When you pass data through AES_DECRYPT(AES_ENCRYPT()), the character set information is lost, so MySQL considers the result to be "binary" rather than character data. I suspect phpMyAdmin is making an incorrect assumption about how this data should be displayed to you.

Used in code, this would probably work fine exactly as it is, because your code isn't likely to incorrectly try to convert this to hex for you to see. That's why, when you get something unexpected, and you aren't using the mysql command line client, you should always double-check using the official CLI to see what's really happening. 

mysql> SELECT AES_DECRYPT( AES_ENCRYPT('password1','secret'),'secret');
+----------------------------------------------------------+
| AES_DECRYPT( AES_ENCRYPT('password1','secret'),'secret') |
+----------------------------------------------------------+
| password1                                                |
+----------------------------------------------------------+
1 row in set (0.08 sec)

You'll probably get the expected result in phpMyAdmin if you cast the output back to character data, but you shouldn't need to do this in your actual application.

SELECT CAST(AES_DECRYPT(AES_ENCRYPT('password1','secret'),'secret') AS CHAR);

Of course, as a rule you shouldn't encrypt passwords, you should hash them. You should only encrypt data that you need to be able to decrypt, and with passwords, that's generally not the case... and data you never store can't be compromised.

Upvotes: 4

Related Questions