blocks|key|4583691|text|MySQL有广泛的内置加密功能，你可以在here中找到详细的概述。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4583692|你可能想看看AES_DECRYPT()和AES_ENCRYPT()。|4583693|INSERT+INTO+table+(mycolumn)+VALUES(AES_ENCRYPT('Hello!',+'encryption_key'));
SELECT+AES_DECRYPT(mycolumn,+'encryption_key')+FROM+table;|code-block|syntax|javascript|4583694|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html^0|K|4|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

MySQL has a wide range of built-in encryption functions, you can find a detailled overview <a href="http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html" rel="nofollow">here</a>.

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

<pre><code>INSERT INTO table (mycolumn) VALUES(AES_ENCRYPT('Hello!', 'encryption_key'));
SELECT AES_DECRYPT(mycolumn, 'encryption_key') FROM table;
</code></pre>

blocks|key|2472722|text|alter+table+tableName++modify+columnNameToBeEncrypted+varbinary(200);
update+tableName+
set+columnNameToBeEncrypted+=+aes_encrypt(tableName.columnNameToBeEncrypted,+'secretyKey');|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2472723|首先将要加密的列更新为varbinary类型。这将允许保存加密值。|unstyled|offset|length|style|CODE|2472724|然后更新表以将加密值存储在适当的位置，如上所示。这将在表中保存加密值。|2472725|要查看此值，请使用以下查询：|2472726|select+*,+CAST(aes_decrypt(columnNameToBeEncrypted,+'secretyKey')+AS+CHAR(40))+from+tableName;|2472727|上面的SQL查询已经在MySQL上进行了测试。|2472728|entityMap^0|0|B|9|0|0|0|0|3|3|B|5|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|V|8|@$G|W|H|X|I|J]]|9|@]|A|$]]|$1|K|3|L|5|F|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$B|C]]|$1|Q|3|R|5|F|7|11|8|@$G|12|H|13|I|J]|$G|14|H|15|I|J]]|9|@]|A|$]]|$1|S|3|-4|5|F|7|16|8|@]|9|@]|A|$]]]|T|$]]

<pre><code>alter table tableName modify columnNameToBeEncrypted varbinary(200);
update tableName 
set columnNameToBeEncrypted = aes_encrypt(tableName.columnNameToBeEncrypted, 'secretyKey');
</code></pre>

First update the column to be encrypted to be of <code>varbinary</code> type. 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.

To view this value, use the following query: 

<pre><code>select *, CAST(aes_decrypt(columnNameToBeEncrypted, 'secretyKey') AS CHAR(40)) from tableName;
</code></pre>

Above <code>SQL</code> queries have been tested on <code>MySQL</code>.

blocks|key|2472679|text|将数据库(仅数据)备份为csv文件。使用以下查询更新具有key的现有字段：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2472680|LOAD+DATA+INFILE+'C:/Sample.csv'+INTO+TABLE+myTable(myname,@mypass)+SET+mypass=AES_ENCRYPT(@mypass,'key:test');|code-block|syntax|javascript|2472681|希望它能解决你的问题。|2472682|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

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

Hope it will solve your problem.

blocks|key|4583672|text|对password字段进行更新，以更改要散列的密码。您可以使用SHA1来实现这一点。你想要散列密码，而不是加密。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4583673|在检查提供的密码是否正确时，您需要对用户提供的值进行散列处理，并将其与数据库字段中的值进行比较。|4583674|在对密码进行散列时，您需要使用某种形式的盐。理想情况下，对于每个用户，这应该是不同的。一个包含好答案的类似问题是：Secure+hash+and+salt+for+PHP+passwords|offset|length|4583675|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/401656/secure-hash-and-salt-for-php-passwords^0|0|0|1L|12|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@$F|S|G|T|1|U]]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]]]

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.

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: <a href="https://stackoverflow.com/questions/401656/secure-hash-and-salt-for-php-passwords">Secure hash and salt for PHP passwords</a>

blocks|key|2472663|text|您可以使用md5或password，但要小心使用rainbow+tables|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2472664|mysql>+select+md5('a');
%2B----------------------------------%2B
%7C+md5('a')+++++++++++++++++++++++++%7C
%2B----------------------------------%2B
%7C+0cc175b9c0f1b6a831c399e269772661+%7C
%2B----------------------------------%2B
1+row+in+set+(0.00+sec)

mysql>+select+password('a');
%2B-------------------------------------------%2B
%7C+password('a')+++++++++++++++++++++++++++++%7C
%2B-------------------------------------------%2B
%7C+*667F407DE7C6AD07358FA38DAED7828A72014B4E+%7C
%2B-------------------------------------------%2B
1+row+in+set+(0.00+sec)|code-block|syntax|javascript|2472665|有些人只是以一种方式加密密码并添加一些哑文，即|2472666|假设您想要加密"Hello“，并且您将使用MD5，您将这样做：|2472667|mysql>+select+password('dumbtext+Hello');
%2B-------------------------------------------%2B
%7C+password('dumbtext+Hello')++++++++++++++++%7C
%2B-------------------------------------------%2B
%7C+*1F2CE4EA3F6F689369453F090A660A9D0314AD90+%7C
%2B-------------------------------------------%2B
1+row+in+set+(0.00+sec)|2472668|然后，如果要验证会话，只需在用户在表单的输入字段中使用的密码之间进行匹配：|2472669|if+(md5("dumptext".用户(Password_input))数据库中的==字段，然后选择会话！。如果不是拒绝|2472670|这是Mysql的密码encrypt+information，有不同的选项，由你决定|2472671|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/Rainbow_table|1|http://dev.mysql.com/doc//refman/5.5/en/encryption-functions.html^0|O|E|0|0|0|0|0|0|0|0|A|J|1|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@$A|15|B|16|1|17]]|C|$]]|$1|D|3|E|5|F|7|18|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|19|8|@]|9|@]|C|$]]|$1|K|3|L|5|6|7|1A|8|@]|9|@]|C|$]]|$1|M|3|N|5|F|7|1B|8|@]|9|@]|C|$G|H]]|$1|O|3|P|5|6|7|1C|8|@]|9|@]|C|$]]|$1|Q|3|R|5|6|7|1D|8|@]|9|@]|C|$]]|$1|S|3|T|5|6|7|1E|8|@]|9|@$A|1F|B|1G|1|1H]]|C|$]]|$1|U|3|-4|5|6|7|1I|8|@]|9|@]|C|$]]]|V|$W|$5|X|Y|Z|C|$10|11]]|12|$5|X|Y|Z|C|$10|13]]]]

you can use md5 or password, but be careful of <a href="http://en.wikipedia.org/wiki/Rainbow_table" rel="nofollow">rainbow tables</a>

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

mysql&gt; select password('a');
+-------------------------------------------+
| password('a') |
+-------------------------------------------+
| *667F407DE7C6AD07358FA38DAED7828A72014B4E |
+-------------------------------------------+
1 row in set (0.00 sec)
</code></pre>

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

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

<pre><code>mysql&gt; select password('dumbtext Hello');
+-------------------------------------------+
| password('dumbtext Hello') |
+-------------------------------------------+
| *1F2CE4EA3F6F689369453F090A660A9D0314AD90 |
+-------------------------------------------+
1 row in set (0.00 sec)
</code></pre>

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

here is the Mysql password <a href="http://dev.mysql.com/doc//refman/5.5/en/encryption-functions.html" rel="nofollow">encrypt information</a>, there are different options, that's up to you

blocks|key|6000563|text|有两种加密数据的原理：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|6000564|6000565|The+1-way哲学使用算法对数据进行加密或散列，因此无法获得原始字符串。这可以使用MD5或SHA1来完成。|unordered-list-item|offset|length|style|BOLD|6000566|6000567|插入用户(用户，密码)值('userLogin'，md5('myPassword')+)；|6000568|更新用户设置user='userLogin';的密码=+sha1('myOtherPassword')|6000569|验证这一点的方法是对用户提供的密码应用方法(sha1或md5)，并将得到的散列与存储在数据库中的散列进行比较。|6000570|select+id+from+users+where+user='userLogin'+and+password+=+sha1('passwordFromUser')|code-block|syntax|javascript|6000571|6000572|The双向哲学使用可以编码或解码的算法对数据进行加密。这可以使用AES_ENCRYPT()和AES_DECRYPT()来完成。|6000573|6000574|插入用户(用户，密码)值('userLogin'，'myEncryptionKey'));+(‘myDecodeablePass’，AES_ENCRYPT|6000575|验证的方法是对存储的字符串使用解密方法，并将结果与用户提供的密码进行比较|6000576|select+AES_DECRYPT(password,+'myEncryptionKey')+as+myDecodedPassword+from+users+
where+user='userLogin';|6000577|请注意，通过这种方式，中间人攻击可能会破坏加密密钥，因此可能会危及所有密码，除非它是每个用户的密钥。|6000578|注意：为了避免彩虹表(或预先计算的哈希表)，建议在要加密的字符串中插入一个随机的非字典单词...因为许多用户倾向于使用简单密码，如“password”或“123456”|6000579|entityMap^0|0|0|0|B|0|0|0|0|0|0|0|0|7|0|0|0|0|0|0|0|3|0^^$0|@$1|2|3|4|5|6|7|1B|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|1D|8|@$F|1E|G|1F|H|I]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|1H|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1I|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1J|8|@]|9|@]|A|$]]|$1|Q|3|R|5|S|7|1K|8|@]|9|@]|A|$T|U]]|$1|V|3|-4|5|6|7|1L|8|@]|9|@]|A|$]]|$1|W|3|X|5|E|7|1M|8|@$F|1N|G|1O|H|I]]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1P|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|11|3|12|5|6|7|1R|8|@]|9|@]|A|$]]|$1|13|3|14|5|S|7|1S|8|@]|9|@]|A|$T|U]]|$1|15|3|16|5|6|7|1T|8|@]|9|@]|A|$]]|$1|17|3|18|5|6|7|1U|8|@$F|1V|G|1W|H|I]]|9|@]|A|$]]|$1|19|3|-4|5|6|7|1X|8|@]|9|@]|A|$]]]|1A|$]]

There are 2 philosophies of encrypting data: 

<ul>
<li>The 1-way philosophy
encrypts 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';</li>
</ul>

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.

<pre><code>select id from users where user='userLogin' and password = sha1('passwordFromUser')
</code></pre>

<ul>
<li>The Bidirectional philosophy
encrypts 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'));</li>
</ul>

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

<pre><code>select AES_DECRYPT(password, 'myEncryptionKey') as myDecodedPassword from users 
where user='userLogin';
</code></pre>

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'

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

How to encrypt the existing column in mysql table?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有表与密码字段，这是一个纯文本。现在，我想对字段进行加密。怎么办？

问如何加密mysql表中已有的列？
EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何加密mysql表中已有的列？EN

回答 6

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何加密mysql表中已有的列？
EN