mysql aes java解密,使用PHP Mcrypt加密并使用MySQL aes_decrypt解密？

Is it possible to Encrypt data with PHP mcrypt and decrypt it in the database with MySQL AES_DECRYPT? At the moment, I'm using RIJNDAEL_128 for mcrypt on PHP. I've also made sure that the encrypted fields in the database have data type blob. Yet, AES_DECRYPT with the correct key still returns NULL. Any suggestions on how to get this to work?

解决方案

I found some good help here

Note this works for encrypted text up to 65519 characters in the plain text. (maybe a bit more if no UTF-8 encoding)

PHP code to encrypt:

// MySQL uses 16 bytes key for 128 encryption/decryption

$key = "ABCDEF0123456789";

$plaintext = "This string was AES-128 / EBC / ZeroBytePadding encrypted.";

// Optionally UTF-8 encode

$plaintext_utf8 = utf8_encode($plaintext);

// Find out what's your padding

$pad_len = 16 - (strlen($plaintext_utf8) % 16);

// Padd your text

$plaintext_utf8 = str_pad($plaintext_utf8, (16 * (floor(strlen($plaintext_utf8) / 16) + 1)), chr($pad_len));

// Encryption

mt_srand();

$td = mcrypt_module_open(MCRYPT_RIJNDAEL_128, '', MCRYPT_MODE_ECB, '');

mcrypt_generic_init($td, $key, false);

// Generates a warning about empty IV but it's Ok

$ciphertext = mcrypt_generic($td, $plaintext_utf8);

mcrypt_generic_deinit($td);

$ciphertext = mysql_real_escape_string($ciphertext);

// Store in MySQL

$mysqli = new mysqli("localhost", "test", "test", "test");

$mysqli->set_charset("utf8");

$mysqli->query("insert into test(content) value ('$ciphertext')");

$mysqli->close();

SQL query to search for string was:

SELECT CAST(AES_DECRYPT(content,'ABCDEF0123456789') AS CHAR) AS content

FROM test

WHERE CAST(AES_DECRYPT(content,'ABCDEF0123456789') AS CHAR) like '%string was%';

Output is:

This string was AES-128 / EBC / ZeroBytePadding encrypted.

Note: MySQL table was created by:

create table test (

id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

content blob ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;