标题：[实践OK]mysql加密存储敏感数据
出处：向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除
时间：Wed, 22 Feb 2023 14:19:48 +0000
作者：jackxiang
地址：http://jackxiang.com/post/11713/

内容：
实践如下：

CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */;
Query OK, 1 row affected (0.01 sec)
use test;
Database changed
create table test( id int (12),idcard varchar (128));
Query OK, 0 rows affected (0.03 sec)
INSERT INTO test (idcard) VALUES (HEX(AES_ENCRYPT('452528199909091234','my_secret_key'))) ;
Query OK, 1 row affected (0.00 sec)
SELECT id,AES_DECRYPT(UNHEX(idcard),'my_secret_key') AS idcard FROM test;
+------+--------------------+
| id   | idcard             |
+------+--------------------+
| NULL | 452528199909091234 |
+------+--------------------+
1 row in set (0.00 sec)
select * from test\G;
*************************** 1. row ***************************
    id: NULL
idcard: AC89B7A39D9A5A43BF9B0B455E2097B37E7BB2A8379B6D820FB3FC97EEB197B2
1 row in set (0.00 sec)
SELECT id,LEFT(AES_DECRYPT(UNHEX(idcard),'my_secret_key'),6) AS idcard FROM test;
+------+--------+
| id   | idcard |
+------+--------+
| NULL | 452528 |
+------+--------+
1 row in set (0.00 sec)
SELECT id,CONCAT('******',LEFT(AES_DECRYPT(UNHEX(idcard),'my_secret_key'),6)) AS idcard FROM test;
+------+--------------+
| id   | idcard       |
+------+--------------+
| NULL | ******452528 |
+------+--------------+
1 row in set (0.01 sec)
SELECT id,AES_DECRYPT(UNHEX(idcard),'my_secret_key') AS idcard FROM test  WHERE idcard=HEX(AES_ENCRYPT('452528199909091234','my_secret_key'));
+------+--------------------+
| id   | idcard             |
+------+--------------------+
| NULL | 452528199909091234 |
+------+--------------------+
1 row in set (0.00 sec)
SELECT id,AES_DECRYPT(UNHEX(idcard),'my_secret_key') AS idcard FROM test  WHERE AES_DECRYPT(UNHEX(idcard),'my_secret_key')='452528199909091234';
+------+--------------------+
| id   | idcard             |
+------+--------------------+
| NULL | 452528199909091234 |
+------+--------------------+
1 row in set (0.00 sec)


项目中涉及到用户敏感信息，比如手机号、身份证号码等，应该以密文形式存储在数据库中，本文例子演示如何利用mysql的加解密函数对数据进行加密存储、解密读取、以及过滤查询。

  下面以在表"test"中将身份证信息写入"idcard"字段、以及从"idcard"字段读取数据、以及以"idcard"为过滤条件进行过滤查询为例子；假设加密秘钥为"my_secret_key"

一）加密存储数据：'452528199909091234'，秘钥为:'my_secret_key'
INSERT INTO test (idcard) VALUES (HEX(AES_ENCRYPT('452528199909091234','my_secret_key'))) 

二）读取并解密数据返回

1.读取并解密数据，数据全部返回。
SELECT id,AES_DECRYPT(UNHEX(idcard),'my_secret_key') AS idcard FROM test

2.读取并解密数据，只返回最左边6个字符
SELECT id,LEFT(AES_DECRYPT(UNHEX(idcard),'my_secret_key'),6) AS idcard FROM test

3.读取并解密数据，只返回最左边6个字符，补充前缀'******'，读出来的结果为
SELECT id,CONCAT('******',LEFT(AES_DECRYPT(UNHEX(idcard),'my_secret_key'),6)) AS idcard FROM test

三）过滤查询

1.按照密文比较方式(要比较的数据先加密，加密后的数据和数据库里存的密文比较)
SELECT id,AES_DECRYPT(UNHEX(idcard),'my_secret_key') AS idcard FROM test  WHERE idcard=HEX(AES_ENCRYPT('452528199909091234','my_secret_key'))

2.按照明文比较方式(将数据库存的密文解密出明文，然后和要比较的数据进行比较)
SELECT id,AES_DECRYPT(UNHEX(idcard),'my_secret_key') AS idcard FROM test  WHERE AES_DECRYPT(UNHEX(idcard),'my_secret_key')='452528199909091234'
来自：https://blog.csdn.net/u010178611/article/details/126508465


Generated by Jackxiang's Bo-blog 2.1.1 Release