IT博客汇 | 使用mysql加密函数aes_encrypt()和aes

使用mysql加密函数aes_encrypt()和aes_decrypt()

mckee发表于 2015-11-24 20:23:36

如果你需要对mysql某些字段进行加解密的话,使用mysql的加解密函数可能比程序中处理更方便.
以aes_encrypt()和aes_decrypt()为例
特别需要注意的时mysql5.5及以下的版本仅支持aes-128-ecb模式，如果需要其它模式需要mysql5.6及以上版本才支持，可通过mysql全局变量如下方式指定：

mysql> SET block_encryption_mode = 'aes-256-cbc';
mysql> SET @key_str = SHA2('My secret passphrase',512);
mysql> SET @init_vector = RANDOM_BYTES(16);
mysql> SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);
mysql> SELECT AES_DECRYPT(@crypt_str,@key_str,@init_vector);
+-----------------------------------------------+
| AES_DECRYPT(@crypt_str,@key_str,@init_vector) |
+-----------------------------------------------+
| text                                          |
+-----------------------------------------------+

参考文档如下：

关于加密的二进制数据在mysql中字段存什么类型(存blob还是varbinay类型)？
引用文档一段话：
Many encryption and compression functions return strings for which the result might contain arbitrary byte values. If you want to store these results, use a column with a VARBINARY or BLOB binary string data type. This will avoid potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT).
尽量使用blob类型，原因如下：

There is no trailing-space removal for BLOB columns when values are stored or retrieved.
For indexes on BLOB columns, you must specify an index prefix length.
BLOB columns can not have DEFAULT values.

二进制数据如何使用sql插入？
不可直接拼接sql插入，否则会被当成字符串处理，不可你可以将二进制数据转换程十六进制或base64插入，相应的，取出来的时候你也需要转换。但是通过mysql prepared statement方式可以插入stream data，如php pdo可以类似如下实现：

$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("insert into images (id, contenttype, imagedata) values (?, ?, ?)");
$id = get_new_id(); // some function to allocate a new ID

// assume that we are running as part of a file upload form
// You can find more information in the PHP documentation

$fp = fopen($_FILES['file']['tmp_name'], 'rb');

$stmt->bindParam(1, $id);
$stmt->bindParam(2, $_FILES['file']['type']);
$stmt->bindParam(3, $fp, PDO::PARAM_LOB);

$db->beginTransaction();
$stmt->execute();
$db->commit();

关于是在php中还是mysql加密数据，可参考下面：