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

2021-12-20 00:00:00 encryption mysql phpmyadmin

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

I am experimenting with creating a simple message system (PHP) page that uses a MySQL table to store the entries. The rough outline of the columns I'll use in the table are:

msg_id(主键，auto_increment)

msg_id (primary key, auto_increment)

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

user_id (foreign key pointing to the user who created the message)

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

time (a DATETIME entry to provide msg timestamps)

msg(包含 msg 的 VARCHAR)

msg (a VARCHAR containing the msg)

accessable(只是一个 int(1)，0 表示除了用户自己之外没有人可以读取 msg，1 表示其他人可以读取它)

accessable (just an int(1), 0 means no one except the user himself can read the msg, and 1 means others can read it)

我想知道的是，加密 msg 字段以便窥探者无法读取它的最佳方法是什么(比方说，通过打开 mysql CLI 或 phpMyAdmin 并只读取值连续存储)?

What I'm wondering is, what's the best way to encrypt the msg field so prying eyes can't read it (let's say, by opening the mysql CLI or phpMyAdmin and just read the value stored in a row)?

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

If "accessable" is set to 0, then only the user him/herself should be able to read it (by accessing some PHP page), but if set to 1, everyone else should be able to read it as well. I don't know how to tackle this, so any help is very appreciated!

推荐答案

在此处查看可能的加密功能列表:

Look here for list of possible encryption functions:

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

您可以为更新创建触发器并检查那里的字段accessable.类似的东西:

You can create trigger for update and check there field accessable. Something like that:

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;

您还可以使用此查询更新表中的所有现有记录:

You also can update all existing records in you table with this query:

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

所以你可以为你的 PHP 代码选择记录:

So you can select records for you PHP code:

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

UPD.这里也有类似的问题:

MySQL 加密列

相关文章