如何加密MySQL表中的特定列？ [英] How to encrypt a specific column in a MySQL table?

问题描述

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

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)

strong>（提供msg时间戳的DATETIME条目）

time (a DATETIME entry to provide msg timestamps)

msg （包含msg的VARCHAR）

msg (a VARCHAR containing the msg)

可访问（只是一个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)?

如果可访问设置为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!

推荐答案

：

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

您可以创建更新的触发器并检查字段可访问。这样的事情：

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。此处还有类似的问题：

UPD. Also here was similar question:

MySQL加密列

这篇关于如何加密MySQL表中的特定列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！