我可以对数据库TEXT列进行哈希/加密吗

Question

提前为一个可能很愚蠢的问题道歉，但我正在努力构建一个小"期刊"；网站，用户可以在这里以私人方式输入日常想法。我目前正在MySQL数据库中以MEDIUMTEXT数据类型存储这些信息。

我的问题是：有没有一种方法可以存储这些信息，这样可以访问数据库的个人(比如我自己(就无法读取该字段来保护用户隐私，就像我对密码进行散列一样？

提前感谢

Answer 1

更好的方法是在DBMS之前加密数据(使用网站上使用的任何编程语言(，将已加密的数据存储在MySQL/MariaDB中，然后读回加密的数据，并在DBMS之外的软件中再次解密。这样，你的网站软件将进行加密/解密，MySQL/MariaDB将只存储数据。

如果您需要在SQL中执行此操作，这里有一种简单(不太安全！(的方法，可以通过使用AES_ENCRYPT和AES_DECRYPT函数来执行此操作。首先我将创建一个简单的测试表

CREATE TABLE encrypt(
col MEDIUMBLOB
);

当我插入数据时，我将使用加密功能：

-- do this if you are unsure if the general log is enabled systemwide
SET sql_log_off = 'ON';
INSERT INTO encrypt(col) VALUES
(AES_ENCRYPT("blah","mysecretpassword"));

要读回数据，必须使用AES_DECRYPT:

SELECT CONVERT(AES_DECRYPT(col, "mysecretpassword") USING utf8) AS col
FROM encrypt;
+------+
| col  |
+------+
| blah |
+------+
-- now you can turn the log back on if you need it 
-- and it is not disabled globally in my.cnf
SET sql_log_off = 'OFF';

提供错误的密码将给出NULL值。请注意，虽然MEDIUMTEXT可能工作得很完美，但它并不是最好的数据类型——当您存储加密数据时，它都是二进制的，所以BLOB更好(它不关心排序规则、编码等(。

为什么不推荐？首先，您应该注意日志文件(如常规日志或日志慢速查询(。如果启用了它们，DBMS可能会以明文形式记录您的密钥，对于具有管理访问权限的人来说，恢复密钥将很容易。因此，如果您要使用这种方式，您必须禁用日志记录！在上面的示例中，我展示了如何使用sql_log_off变量，该变量将仅为当前会话禁用明文常规查询日志(它不会以这种方式在服务器范围内禁用它，因此会记录其他查询(。

但这并不是日志记录的全部内容——还有一个用于事务的二进制日志。如果启用，它将记录对数据的更改(特别是像所有INSERT、UPDATE和DELETE语句一样(。有一个mysqlbinlog实用程序，在DBMS机器上具有管理权限的人可以使用它来恢复数据……他们最终也可以从上面的INSERT语句中恢复您的密钥。如果您也想阻止binlog，则必须在执行INSERT语句之前执行以下操作：

SET sql_log_bin = 'OFF';

当然，在它之后再启用它。注意，它不能在事务内部发生——事务管理需要bin日志。此外，这会使您的数据库有点崩溃不安全。如果在bin日志被禁用时，系统在插入语句的中间崩溃，则可能会损坏表中的数据。所以这里的最后结论是——在应用程序中进行加密/解密，而不是在数据库中进行。这将为您省去很多日志问题的麻烦。

其次，上面的例子不是安全加密，因为(默认情况下(它使用的是不安全的ECB模式。简言之，所有数据都被分为块，每个块都用相同的密钥以相同的方式加密。这样，相等的明文块将产生相同的加密块——这可能会泄露模式。因此，最好使用一些带有初始化向量的块链接模式——这是一种更强的加密。不幸的是，如果你正在使用MariaDB，你应该到此为止，因为它还不支持ECB以外的任何其他模式(他们正在努力在未来添加其他模式(。如果您正在使用MySQL，请继续阅读以改进sql解决方案。。。

对于MySQL，您应该考虑切换默认加密模式，并开始使用加密/解密函数的第三个IV(初始化向量(参数，该参数必须为16字节(它应该是一个随机值，不是秘密，可以直接存储在DB中(。方法如下：

1. 首先将block_encryption_mode系统变量更改为不同于ECB的变量(请在此处检查(。例如，您可以使用CBC。

2. 然后像这样更改上面的查询：

   CREATE TABLE encrypt(
   col MEDIUMBLOB,
   iv BINARY(16)
   );
   -- Run this each time you encrypt/decrypt
   -- if you cannot guarantee that it is
   -- set properly in my.cnf
   SET block_encryption_mode="aes-256-cbc";
   SET sql_log_off = 'ON';
   INSERT INTO encrypt(iv, col)
   VALUES(RANDOM_BYTES(16), AES_ENCRYPT("blah", "mysecretpassword", iv));
   SELECT CONVERT(AES_DECRYPT(col, "mysecretpassword", iv) USING utf8) AS col
   FROM encrypt;
   SET sql_log_off = 'OFF';