MySQL 如何加密mysql表中的现有列？

How to encrypt the existing column in mysql table?

提问by Dhileepan

I have table with password field which is a plain text. Now, I want to encrypt the field. how to do ?

我有一个带有密码字段的表格，它是一个纯文本。现在，我想加密该字段。怎么做 ？

回答by Fahim Parkar

MySQL has a wide range of built-in encryption functions, you can find a detailled overview here.

MySQL 具有广泛的内置加密功能，您可以在此处找到详细概述。

You might want to have a look at e.g. AES_DECRYPT() and AES_ENCRYPT().

您可能想看看例如 AES_DECRYPT() 和 AES_ENCRYPT()。

INSERT INTO table (mycolumn) VALUES(AES_ENCRYPT('Hello!', 'encryption_key'));
SELECT AES_DECRYPT(mycolumn, 'encryption_key') FROM table;

回答by Rahman

Take backup of your database (data only) as csv file. Use the following query to update existing fields with key:

将您的数据库（仅数据）备份为 csv 文件。使用以下查询以键更新现有字段：

LOAD DATA INFILE 'C:/Sample.csv' INTO TABLE myTable(myname,@mypass) SET mypass=AES_ENCRYPT(@mypass,'key:test');

Hope it will solve your problem.

希望它能解决你的问题。

回答by user3157008

alter table tableName  modify columnNameToBeEncrypted varbinary(200);
update tableName 
set columnNameToBeEncrypted = aes_encrypt(tableName.columnNameToBeEncrypted, 'secretyKey');

First update the column to be encrypted to be of varbinarytype. This will enable saving of encrypted values.
Then update the table to store the encrypted value in place as shown above. This will save the encrypted value in the table.

首先将要加密的列更新为varbinary类型。这将启用加密值的保存。
然后更新表以存储加密值，如上所示。这会将加密值保存在表中。

To view this value, use the following query:

要查看此值，请使用以下查询：

select *, CAST(aes_decrypt(columnNameToBeEncrypted, 'secretyKey') AS CHAR(40)) from tableName;

Above SQLqueries have been tested on MySQL.

以上SQL查询已在MySQL.

回答by Joanne Lee

Why don't you use a column-level encryption solution?

为什么不使用列级加密解决方案？

As I found out recently, there are many drawbacks in MySQL internal encryption functions. I got this information from a blog that provides mysql encryption freeware. http://www.mydiamo.com/index.php/mdblog/item/24-why-applying-mysql-internal-encryption-functions-only-is-not-enough

正如我最近发现的那样，MySQL 内部加密功能有很多缺点。我从提供 mysql 加密免费软件的博客中获得此信息。 http://www.mydiamo.com/index.php/mdblog/item/24-why-applying-mysql-internal-encryption-functions-only-is-not-enough

If you are trying to encrypt the password field only, maybe a column-level encryption solution is the best way to provide you security. (Column-level encryption is a method of database encryption in which the information in every cell (or data field) in a particular column has the same password for access, reading, and writing purposes.-from WhatIs.com-)

如果您只想加密密码字段，也许列级加密解决方案是为您提供安全性的最佳方式。（列级加密是一种数据库加密方法，其中特定列中每个单元格（或数据字段）中的信息具有相同的密码，用于访问、读取和写入目的。-来自 WhatIs.com-）

MyDiamo, which is the freeware I talked above, seems to be convenient to use. Maybe you can try it out.

MyDiamo，这是我上面谈到的免费软件，使用起来似乎很方便。也许你可以试试看。

回答by pamekar

What made me stumble on your post was because I had the same problem that you had, but somehow I figured it out. I had initially created a table and inserted data into it, without encrypting it: i later discovered that there was a way of encrypting data in mysql, but it is with only with using a query and the md5(), sha(), sha1(), sha2() and maybe more.

让我偶然发现你的帖子是因为我和你有同样的问题，但不知何故我想通了。我最初创建了一个表并将数据插入其中，但没有对其进行加密：后来我发现在 mysql 中有一种加密数据的方法，但它只能使用查询和 md5()、sha()、sha1 (), sha2() 甚至更多。

This is what I did.

这就是我所做的。

You have to create another table that is identical to your current table (but with a different table name of course) .

您必须创建另一个与当前表相同的表（但当然具有不同的表名）。

INSERT INTO users_new(userID, password) SELECT userID, sha1(password) FROM users;

Now note that these are two different tables (users and users_new) these tables have identical column names and characteristics. The command copies the value of the userID and an encrypted (using the sha1() function) password into the table users.

现在请注意，这是两个不同的表（users 和 users_new），这些表具有相同的列名和特征。该命令将 userID 的值和加密的（使用 sha1() 函数）密码复制到表 users 中。

Remember to make the column password to be of type VARCHAR(40) because the decrypted character length is 40bytes(characters) long.

请记住将列密码设为 VARCHAR(40) 类型，因为解密后的字符长度为 40bytes(characters)。

Hope this was helpful. Post you comment or leave a reply.

希望这是有帮助的。发表您的评论或留下回复。

more Grace to you.

给你更多恩典。

回答by Gusstavv Gil

There are 2 philosophies of encrypting data:

有两种加密数据的理念：

• The 1-way philosophyencrypts or hashes the data with an algorithm so that the original string cannot be obtained back. This can be done using MD5 or SHA1.

  insert into users(user,password) values ('userLogin', md5('myPassword') );

  update users set password = sha1('myOtherPassword') where user='userLogin';

• 1-way 哲学使用一种算法对数据进行加密或散列，从而无法取回原始字符串。这可以使用 MD5 或 SHA1 来完成。

  插入用户（用户，密码）值（'userLogin'，md5（'myPassword'））；

  更新用户设置密码 = sha1('myOtherPassword') where user='userLogin';

The way of validating this, is by applying the method (sha1 or md5) on the password the user gives and the resulting hash is compared with the one stored in the DB.

验证这一点的方法是对用户提供的密码应用方法（sha1 或 md5），并将结果散列与存储在数据库中的散列进行比较。

select id from users where user='userLogin' and password = sha1('passwordFromUser')

• The Bidirectional philosophyencrypts the data with an algorithm which be can encoded or decoded. This can be done with AES_ENCRYPT() and AES_DECRYPT().

  insert into users (user,password) values ('userLogin', AES_ENCRYPT('myDecodeablePass', 'myEncryptionKey'));

• 双向哲学使用可编码或解码的算法对数据进行加密。这可以通过 AES_ENCRYPT() 和 AES_DECRYPT() 来完成。

  插入用户 (user,password) 值 ('userLogin', AES_ENCRYPT('myDecodeablePass', 'myEncryptionKey'));

The way of validating this, is by using the decryption method on the stored string and comparing the result with the password the user provided

验证这一点的方法是对存储的字符串使用解密方法并将结果与​​用户提供的密码进行比较

select AES_DECRYPT(password, 'myEncryptionKey') as myDecodedPassword from users 
where user='userLogin';

Please note in this way a man-in-the-middle attack could compromise the encryption key and therefore risking all the passwords, unless it's a per-user key.

请注意，以这种方式，中间人攻击可能会破坏加密密钥，从而冒着所有密码的风险，除非它是每个用户的密钥。

Note:To avoid rainbow tables (or precomputed hash tables) it is recommended inserting a random-non-dictionary word to the string to be encrypted... since a lot of users tend to use easy passwords like 'password' or '123456'

注意：为了避免彩虹表（或预先计算的哈希表），建议在要加密的字符串中插入一个随机的非字典词...因为很多用户倾向于使用简单的密码，如“密码”或“123456”

回答by jcho360

you can use md5 or password, but be careful of rainbow tables

您可以使用 md5 或密码，但要小心彩虹表

mysql> select md5('a');
+----------------------------------+
| md5('a')                         |
+----------------------------------+
| 0cc175b9c0f1b6a831c399e269772661 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select password('a');
+-------------------------------------------+
| password('a')                             |
+-------------------------------------------+
| *667F407DE7C6AD07358FA38DAED7828A72014B4E |
+-------------------------------------------+
1 row in set (0.00 sec)

Some ppl just encrypt the password one way and add some dumb text, I.E

有些人只是以一种方式加密密码并添加一些愚蠢的文本，IE

lets say that you want to encrypt "Hello" and you are going to use MD5, you will do:

假设您想加密“Hello”并且您将使用 MD5，您将执行以下操作：

mysql> select password('dumbtext Hello');
+-------------------------------------------+
| password('dumbtext Hello')                |
+-------------------------------------------+
| *1F2CE4EA3F6F689369453F090A660A9D0314AD90 |
+-------------------------------------------+
1 row in set (0.00 sec)

then if you want to validate session you just make the match between the password that the user use in the input field of your form:

然后，如果您想验证会话，您只需匹配用户在表单输入字段中使用的密码：

if (md5("dumptext". user(password_input))== field in database, then Session ON!. if not Reject

if (md5("dumptext".user(password_input))== 数据库中的字段，然后会话开启！如果不是拒绝

here is the Mysql password encrypt information, there are different options, that's up to you

这里是Mysql密码加密信息，有不同的选项，由你决定

回答by infojolt

Do an update on the password field to change the password to be hashed. You could use SHA1 for this. You want to hash the password, not encrypt.

更新密码字段以更改要散列的密码。您可以为此使用 SHA1。您想要散列密码，而不是加密。

When checking the supplied password is correct, you will need to hash the user supplied value and compare it to what is in the database field.

检查提供的密码是否正确时，您需要对用户提供的值进行散列并将其与数据库字段中的值进行比较。

When hashing the password, you want to use a salt of some kind. Ideally this should be different for each user. A similar question contain good answers is: Secure hash and salt for PHP passwords

在对密码进行哈希处理时，您需要使用某种盐。理想情况下，这对于每个用户应该是不同的。一个包含很好答案的类似问题是：Secure hash and salt for PHP passwords