如何加密MySQL表中的特定列？

Question

我正在尝试创建一个使用MySQL表存储条目的简单消息系统(PHP)页面。我将在表中使用的列的大致轮廓如下：

msg_id (主键，auto_increment)

user_id (指向创建消息的用户的外键)

time (提供msg时间戳的日期时间条目)

msg (包含msg的VARCHAR )

可访问(只表示int(1)，0表示除了用户自己之外，没有人能够读取msg，而1表示其他人可以读取它)

我想知道的是，加密msg字段的最佳方法是什么，这样窥探的眼睛就不能读取它(比如说，打开mysql或phpMyAdmin，只读取存储在一行中的值)？

如果"accessable“设置为0，那么只有用户他/她自己才能读取它(通过访问某个PHP页面)，但是如果设置为1，其他人也应该能够读取它。我不知道如何解决这个问题，所以任何帮助都是非常感谢的！

Answer 1

在这里查找可能的加密功能列表：

http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html

您可以为update创建触发器，并检查字段accessable。就像这样：

CREATE TRIGGER crypt_trg BEFORE UPDATE ON table FOR EACH ROW
BEGIN
  IF new.accessable = 0 THEN
    SET new.msg := ENCRYPT(new.msg, 'key');
  ELSE
    SET new.msg := DECRYPT(new.msg, 'key');
  END IF;
END;

还可以使用以下查询更新表中的所有现有记录：

UPDATE table SET msg = IF(accessable = 0, ENCRYPT(msg, 'key'), DECRYPT(msg, 'key'));

因此，您可以为PHP代码选择记录：

SELECT msg_id, user_id, time, IF(accessable = 0, DECRYPT(msg, 'key'), msg) msg
FROM table

UPD.也有类似的问题：

MySQL encrypted columns

Answer 2

您还可以在查询之前对数据进行加密以插入数据，这样MySQL甚至不知道数据是否已加密，并在应用程序中检索时对其进行解密。为此，您应该将其存储在varbinary或blob列中。

Answer 3

所以我有个想法可以实现这一点，但这都是概念性的。

假设您的值为"Lorem dolor a“，并且您想搜索"lorem”。一种方法是，你可以拿起原稿，把它分割成块(用小写)，然后把它们放在第二张桌子上。整个(原始)值位于带有row_id 123的原始表列中，但是一个名为“块”的新表可能包含：

row_id | chunk | foreign_row_id
1      | lo    | 123
2      | or    | 123
3      | re    | 123
4      | em    | 123
5      | m     | 123
6      |  i    | 123
7      | ip    | 123

把它想象成一个子字符串索引，其中每个子字符串都有两个字符长。

现在，当用户想要执行搜索时，您同样会对其进行分组，然后进行查找。如果他们输入了"lo"，那么您将看到哪些外行If匹配。但是，如果它们输入"lore"，则会搜索所有具有与"lo“、"or”和"re“匹配块的外国行ID。

到目前为止还不太实际。但是，如果原始值"Lorem dolor sit“被加密或散列，那么您也可以对2-char子字符串进行分块、加密/散列，然后对块进行查找或对整串进行查找。不需要解密或散列。

其逻辑是：

搜索string

• Encrypt/hash每个2-char块

• 进行查找，并查找所有加密/散列块匹配。

然后，可以从原始表中获取任何匹配。这将保护静止的数据，因为如果数据块表被破坏，它们就不能使用一堆加密/散列的2-char值来执行任何操作。您不能使用两个加密/散列子字符串并重新组合它们，也不能从它们获得任何有意义的内容。

如果我是发明家，并给这个命名，因为它是相似的，但不是完全相同的彩虹桌，我会称之为“水果鹅卵石桌”。因为大块。