mysql - 为什么解密函数将所有值都更改为Null？

Question

我正在尝试加密所有公司数据。但是当我想解密它们时，我只会看到空值。我在加密后检查了我的数据，它看起来是加密的。

这是我的代码

update TCB set Company=aes_encrypt(Company,'1234') WHERE Company= CONVERT(Company USING ASCII);
//I added where clause in case if there is any Encrypted data

select *,cast(aes_decrypt(Company,'1234') as char(100))  from TCB;

提前致谢

Answer 1

AES_ENCRYPT() 返回二进制字节，而不是文本。当您将加密数据保存到同一列时，我假设它是带有字符集的 varchar 或 text 列，它试图将二进制字节解释为列字符集中的编码。许多字节可能不是该字符集的有效编码，因此您最终得到无法解密的垃圾数据。

mysql> create table tcb ( company text );

mysql> insert into tcb set company = 'company';

mysql> update tcb set company = aes_encrypt(company, '1234');
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                          |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\x8F\x94O\x1D\xB9\x07...' for column 'company' at row 1 |
+---------+------+----------------------------------------------------------------------------------+

您应该将加密数据保存到 VARBINARY 或 BLOB 列。这些数据类型没有字符集，它们只存储二进制字节。

mysql> create table tcb (company text, company_enc blob);

mysql> insert into tcb set company = 'company';

mysql> update tcb set 
    company_enc = aes_encrypt(company, '1234'),
    company = NULL; /* no more plain text copy */

mysql> select aes_decrypt(company_enc, '1234') from tcb;
+----------------------------------+
| aes_decrypt(company_enc, '1234') |
+----------------------------------+
| company                          |
+----------------------------------+

另一种解决方案是在将二进制字节转换为可打印的十六进制数字字符串后存储加密数据。

mysql> update tcb set company = 'company';

mysql> update tcb set company = hex(aes_encrypt(company, '1234'));

mysql> select company from tcb;
+----------------------------------+
| company                          |
+----------------------------------+
| 8F944F1DB907685B94F8F21554E57396 |
+----------------------------------+

mysql> select aes_decrypt(unhex(company), '1234') from tcb;
+-------------------------------------+
| aes_decrypt(unhex(company), '1234') |
+-------------------------------------+
| company                             |
+-------------------------------------+