mysql aes_decrypt,MySQL的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?

解决方案

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