Problem with MySQL's AES_DECRYPT

问题:

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?

回答1:

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).

回答2:

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.

回答3:

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

回答4:

If you use AES_ENCRYPT to encrypt a character string, then AES_DECRYPT returns not a character string but an array of System.Byte. I use the following code to restore it to a string:

            Dim back As System.Byte()
            back = DirectCast(reader(x), System.Byte())
            Dim s As String = ""
            For Each b As Byte In back
                s &= Chr(b)
            Next

回答5:

agree with @user187291

I was having the same issue and found out an option checked in my phpmyadmin

Show binary contents as HEX

when I run the same query in mysql commandline it was displaying me the correct result