guice666 guice666 - 1 year ago 71
MySQL Question

MySQL & PHP cross encryption / decryption algorithm?

I'm looking for an encryption / decryption algorithm (method) that would work across MySQL (5.7.x) and PHP (5.6.x).

Problem: we have some processes via procs. and events within MySQL, one of them requires encrypting the user's id (send in email links). We then, in turn, need to grab that and decrypt within PHP for processing.

Is there a compatible algorithm that we can use that works cross platforms?


I found my biggest problem was MySQL is actually limited to 16char / 128bit key. All my attempts with SHA256 failed because of that. In addition, I've discovered

is compatible out of the box (no needing to pad):

$salt = substr(hash('sha256', 'My secret passphrase'), 0, 16);
$text = 12345;

'salt' => $salt,
'aes-128-ecb' => openssl_encrypt($text, 'aes-128-ecb', $salt),

Results in:

[salt] => 760624f7a7f6e8e4
[aes-128-ecb] => blXklI80Q+KmPH909qUyWw==

MySQL Response:

set @salt = SUBSTRING(SHA2('My secret passphrase',256), 1, 16);
select @salt as salt
, TO_BASE64(AES_ENCRYPT(12345, @salt));

Results in:

salt TO_BASE64(AES_ENCRYPT(12345, @salt))
760624f7a7f6e8e4 blXklI80Q+KmPH909qUyWw==

Answer Source

This is something that I found doing some research :-

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
$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);
$ciphertext = mysql_real_escape_string($ciphertext);

// Store in MySQL
$mysqli = new mysqli("localhost", "test", "test", "test");
$mysqli->query("insert into test(content) value ('$ciphertext')");

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 :-

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