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

我正在寻找一种可以在MySQL（5.7.x）和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.

问题：我们通过procs有一些流程。和MySQL中的事件，其中之一需要加密用户的ID（发送电子邮件链接）。然后，我们需要抓取它并在PHP中解密以进行处理。

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

是否存在可以跨平台工作的兼容算法？

*Resolved

*解决

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 openssl_encrypt() is compatible out of the box (no needing to pad):

我发现我最大的问题是MySQL实际上仅限于16char / 128bit密钥。我对SHA256的所有尝试都失败了。另外，我发现openssl_encrypt（）开箱即用兼容（无需填充）：

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

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

Results in:

结果是：

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

MySQL Response:

MySQL响应：

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

2 个解决方案

#1

This is something that I found doing some research :-

这是我发现做一些研究的东西： -

PHP code to encrypt:

用于加密的PHP代码：

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

用于搜索字符串的SQL查询是：

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.

#2

You can do with PHP mcrypt_encrypt and mcrypr_decrypt using a simple VARCHAR column in your database. From the docs:

您可以使用数据库中的简单VARCHAR列使用PHP mcrypt_encrypt和mcrypr_decrypt。来自文档：

$key = pack('H*', "bcb04b7e103a0cd8b54763051cef08bc55abe029fdebae5e1d417e2ffb2a00a3");

$iv_size = mcrypt_get_iv_size(MCRYPT_RIJNDAEL_128, MCRYPT_MODE_CBC);
$iv = mcrypt_create_iv($iv_size, MCRYPT_RAND);

$plain_text = 'some text blabla';

$encrypt_text = mcrypt_encrypt(MCRYPT_RIJNDAEL_128, $key, $plain_text, MCRYPT_MODE_CBC, $iv);

echo $encrypt_text;
//should output some hash

$decrypt_text = mcrypt_decrypt(MCRYPT_RIJNDAEL_128, $key,
                                $encrypt_text, MCRYPT_MODE_CBC, $iv_dec);

echo $decryp_text;
//should output 'some text blabla'

#1