Problem with MySQL's AES_DECRYPT.

Question

I'm looking for a way to encrypt data on its way into a MySQL database, and decrypt it on the way out. Additionally, I would like to be able to perform normal SQL queries on those fields, such as searching and comparison, which prevents me from using a pure PHP solution.

This leads me to AES_ENCRYPT() and AES_DECRYPT(), which can be duplicated in PHP using MCRYPT.

I'm having a hard time with AES_DECRYPT and have tried all suggestions I can find through searches online.

Here's my table:

CREATE TABLE IF NOT EXISTS `test_table` (
  `id` int(6) NOT NULL,
  `secure_info` text NOT NULL,
  `encrypted_blob` blob NOT NULL,
  `encrypted` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I perform these queries:

INSERT INTO test_table (id, secure_info) VALUES (1,'Testing');
UPDATE test_table SET encrypted = AES_ENCRYPT(secure_info,'key') WHERE id=1 LIMIT 1;
UPDATE test_table SET encrypted_blob = AES_ENCRYPT(secure_info,'key') WHERE id=1 LIMIT 1;

SELECT *, AES_DECRYPT(encrypted,'key') as decrypted, AES_DECRYPT(encrypted_blob,'key') as decrypted_blob FROM test_table WHERE id=1;

I cannot get the original value. 'decrypted' returns NULL, and 'decrypted_blob' returns 54657374696e67

Any ideas, or perhaps a better solution?

Answer 1

AES_ENCRYPT returns a binary string so don't use a text column type.

Tip: Start primary key numbering at 1, not 0

Tip 2: Try not to name your fields after MySQL keywords. This can lead to confusion and will usually require escaping with backticks (text is an exception).

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

Answer 2

Blob decryption works just fine, "54657374696e67" is "Testing", only hex-encoded. You're probably executing this with a tool that displays blobs to hex. Text decryption doesn't work (and shouldn't).

Answer 3

TEXT fields in MySQL are subject to character set conversion. If you're connecting with iso-8859 and the table's stored in CP1252, say, then MySQL will auto-convert the text between the two character sets. This will trash the encrypted data, as some bytes of the original 8859 data will get converted to 1252's equivalents, which have different values.

BLOB fields, on the other hand, are passed through verbatim with no conversion, so there's no errors decrypting.