php - MySQL & PHP 交叉加密/解密算法？

Question

我正在寻找一种适用于 MySQL (5.7.x) 和 PHP (5.6.x) 的加密/解密算法(方法)。

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

是否有可以跨平台使用的兼容算法？

*已解决

我发现我最大的问题是 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),
));

结果:

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

MySQL 响应:

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

结果:

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

Answer 1

这是我在做一些研究时发现的:-

要加密的 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();

用于搜索 string 的 SQL 查询是:

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

输出:-

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