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

Question

我正在尝试创建一个简单的消息系统 (PHP) 页面，该页面使用 MySQL 表来存储条目。我将在表中使用的列的粗略轮廓是：

msg_id（主键，自动增量）

user_id（指向创建消息的用户的外键）

< strong>time（提供消息时间戳的 DATETIME 条目）

msg（包含消息的 VARCHAR）

可访问（只是一个 int(1)，0 表示没有除了用户自己可以读取消息外，1 表示其他人可以读取它）

我想知道的是，加密 msg 字段的最佳方法是什么，这样窥探的眼睛就无法读取它（比方说，通过打开 mysql CLI 或 phpMyAdmin 并读取连续存储的值）？

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

Answer 1

在此处查找可能的加密函数列表：

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

您可以创建更新触发器并检查该字段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 加密列

Answer 2

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

Answer 3

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

假设您的值是“Lorem ipsum dolor sat amet”，并且您想要搜索“lorem”。一种方法是您可以将原始数据分解成块（小写），然后将它们放入第二个表中。整个（原始）值位于 row_id 123 的原始表列中，但名为“chunks”的新表可能具有：

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

将其视为子字符串索引，其中每个子字符串都是 2 个字符长。

现在，当用户想要执行搜索时，您同样可以将其分块，然后进行查找。如果他们输入“lo”，那么您会看到哪些外部行 ID 匹配。但是，如果他们输入“lore”，那么您将搜索具有“lo”、“or”，以及“re”。

到目前为止，还不太实用。但是，如果原始值“Lorem ipsum dolor sat amet”已加密或散列，那么您还可以对 2 个字符的子字符串进行分块，对其进行加密/散列，然后对这些块进行查找或完整的字符串。无需解密或取消散列。

逻辑如下：

1. 对搜索字符串进行
2. 分块 对每个 2 字符块进行加密/散列
3. 执行查找并找到所有加密/散列块匹配项。

然后可以从原始表中获取任何匹配项。这将保护静态数据，因为如果块表受到损害，它们将无法对一堆加密/散列的 2 字符值执行任何操作。您无法获取 2 个加密/散列的子字符串并重新组合它们或从中获取任何有意义的内容。

如果我是发明者并必须命名它，因为它与制作彩虹桌相似但不完全相同，我会将其称为“水果卵石桌”。因为大块的缘故。