PHP Openssl解密AES Mysql加密 [英] PHP Openssl decrypt an AES Mysql Encryption

问题描述

所以我只是在mysql表上做一些基本的数据加密.我遵循此处找到的准则 https://dev.mysql.com/doc /refman/5.6/en/encryption-functions.html#function_aes-encrypt

So i'm just doing some basic data encryption on my mysql tables. I followed the guidelines found here https://dev.mysql.com/doc/refman/5.6/en/encryption-functions.html#function_aes-encrypt

但是我遇到了一个问题.虽然我知道我可以在mysql查询中使用aes_decrypt来解密数据.我还想让php本身具有这样做的能力.

But i'm running into an issue. While i know i can just use aes_decrypt in the mysql query to decrypt the data. I want to also have the ability for php to do so itself.

我已经把这部分工作了.如果MySQL这样做非常基本的AES_ENCRYPTION

I've gotten this part working. If MySQL does the very basic AES_ENCRYPTION like so

INSERT INTO tablename (dataset) VALUES (AES_ENCRYPT('testvalue','mysecretphrase'))

我能够像这样用php解密

I'm able to decrypt this with php like so

openssl_decrypt(base64_encode($dR['dataset']), 'aes-128-ecb', 'mysecretphrase')

当我使用上面网址中提到的MySQL建议的UNHEX(SHA2('mysecretphrase'，512))时，我的问题就出现了.

My problem shows up when i use the recommended UNHEX(SHA2('mysecretphrase',512)) that MySQL mentions in the url above.

我尝试使用的php sha *函数可以确认它们都生成与MySQLs sha2()相同的字符串

The php sha* functions i tried using and can confirm that they both generate the same string as MySQLs sha2()

openssl_digest('mysecretphrase', 'sha512')
// AND
hash('sha512', 'mysecretphrase')

最后要解决mysql使用的UNHEX()的问题，经过一些研究，我发现PHP hex2bin == unhex

And lastly to work around the UNHEX() that mysql uses, after some research I turns out that PHP hex2bin == unhex http://www.php.net/manual/en/function.hex2bin.php

但是，解密数据时我没有得到任何结果.这是它不断失败的地方.我感觉好像我丢失了什么，但这只是不解密数据而只返回空结果.

However, i'm just not getting any result when decrypting the data. This is where it keeps failing. I feel as though i'm either missing something but this just does not decrypt the data and only returns empty results.

openssl_decrypt(base64_encode($dR['dataset']), 'aes-128-ecb', hex2bin(openssl_digest('mysecretphrase', 'sha512')))

任何帮助，指针或提示将不胜感激.

Any help, pointers or hints would be greatly appreciated.

推荐答案

我在这里回答是因为它比明显发表评论要便宜...

I'm answering here because it's cheaper than commenting apparently...

上面的帖子非常确切地告诉您问题所在，但并未真正说明如何解决.

The post above tells you quite exactly what the problem is but doesn't really say how to address it.

openssl_encrypt()和openssl_decrypt()静默将密钥切成最大16个字节的长度(至少对于aes-128-ecb)

openssl_encrypt() and openssl_decrypt() silent cuts the key to max 16 bytes length (at least for aes-128-ecb)

并且无法更改它，因此在MySQL中使用AES_ENCRYPT时，您需要通过创建密钥的子字符串来缩短密钥.

And there is no way to change this, therefore you will need to shorten your key by creating a substring of it when using AES_ENCRYPT in MySQL.

INSERT INTO tablename (dataset) 
VALUES (AES_ENCRYPT('testvalue',SUBSTR( UNHEX(SHA2('mysecretphrase',512)), 1, 16))))

请注意，既然您使用了子字符串，以上答案中的l_16列与["data2"]相同吗? (可能需要在php中执行strtolower())

Notice how the l_16 column in the above answer is the same as ["data2"] now that you use the substring? (may need to do a strtolower() in php)

如果这给您带来了安全隐患，则需要找到一种没有此限制的替代加密算法

If this raises security concerns for you, you will need to find an alternative encryption algorithm that doesnt have this restriction

这篇关于PHP Openssl解密AES Mysql加密的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！