MySQL敏感数据加密及解密

优惠码大使 2020-03-12

python mysql 函数 加密 字符集 varchar 存储

MySQL敏感数据加密及解密
大数据时代的到来,数据成为企业最重要的资产之一,数据加密的也是保护数据资产的重要手段。本文主要在结合学习通过MySQL函数及Python加密方法来演示数据加密的一些简单方式。

  1. 准备工作
    为了便于后面对比,将各种方式的数据集存放在不同的表中。

创建原始明文数据表

/ 创建原始数据表 /

CREATE TABLE f_user (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
tel varchar(20) DEFAULT NULL,
pwd varchar(50) DEFAULT NULL,
PRIMARY KEY (id)
);

/ 新增原始数据 /
INSERT INTO f_user VALUES (1,'曹操','10000000000','Cc@123'),(2,'关羽','21000000000','Guanyu@21'),(3,'刘备','20000000000','LB#200000');

创建MySQL加密表

CREATE TABLE f_user_m (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(200) DEFAULT NULL,
tel varchar(100) DEFAULT NULL,
pwd varbinary(255) DEFAULT NULL,
PRIMARY KEY (id)
);

创建python加密表

CREATE TABLE f_user_p (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(200) DEFAULT NULL,
tel varchar(100) DEFAULT NULL,
pwd varchar(500) DEFAULT NULL,
PRIMARY KEY (id)
);

2.   MySQL加密函数的方式
2.1  MySQL加密
将明文表中的数据插入到f_user_m中,同时对pwd密码字段进行加密存储,注意要记住加密的字符串,因为解密的时候要用到这个值。

/ 加密密码字段 /
mysql> insert into f_user_m (name,tel,pwd)
select name,tel,AES_ENCRYPT(pwd,'MySQL') from f_user;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
存储的结果如下

注:

加密后的数据直接存储varchar类型的字段中会出现如下报错:

ERROR 1366 (HY000): Incorrect string value: 'xF0K+!x15?...' for column 'pwd' at row 1
可以用如下三种方式处理:

1) 加密后的数据在utf8字符集下必须存放在varbinary/binary/blob等二进制字段属性的字段中,故上文中密码字段的类型设置为varbinary类型

2) 可以对加密后的数据使用HEX()函数来16进制化存入,取出时先用UNHEX()处理再解密

3) 采用latin1字符集直接存储在varchar类型字段中,但是不推荐

三种方式可以自行测试处理。

2.2  MYSQL解密
对于加密后的数据可以使用MySQL的解密函数AES_DECRYPT进行解密查看明文

mysql> select name,tel,AES_DECRYPT(pwd,'MySQL')pwd from f_user_m;
name tel pwd
曹操 10000000000 Cc@123
关羽 21000000000 Guanyu@21
刘备 20000000000 LB#200000

3 rows in set (0.00 sec)

此时查看的数据和明文表里的一致了。

  1. Python base64加密方法
    3.1  使用Python的encodestring方法加密

编写python脚本,将数据加密后插入表中

!/usr/bin/python

coding=utf-8

import pymysql as mdb
import base64

sor_conn = mdb.connect(host='127.0.0.1',port=3306,user='root',passwd='Root@Py123')
sor_cur = sor_conn.cursor()

v_sql = "select name,tel,pwd from bak_db.f_user"
result_tb = sor_cur.execute(v_sql)
t = sor_cur.fetchall()
for col in t:

    v_name = col[0]
    v_tel = col[1]
    v_pwd = col[2]

    v_pwd = base64.encodestring(v_pwd)  # 加密
    v_sql_insert = "insert into  bak_db.f_user_p(name,tel,pwd) values('%s','%s','%s');" %(v_name,v_tel,v_pwd)
    sor_cur.execute(v_sql_insert)
    sor_conn.commit()

sor_conn.close()

查询加密后的数据如下:

/ 加密后的数据如下 /

mysql> select * from f_user_p;
id name tel pwd
1 曹操 10000000000 Q2NAMTIz
2 关羽 21000000000 R3Vhbnl1QDIx
3 刘备 20000000000 TEIjMjAwMDAw
+----+--------+-------------+---------------+

3 rows in set (0.00 sec)

3.2  使用Python的decodestring方法解密
解密的方式采用base64.decodestring方法进行,该方法比较简单,可以自行测试.

注: 此方法的加、解密没有加密串进行加成,安全性相对较低,因此可以继续采用另一种方式进行。

  1. Python AES算法加密
    AES算法需用到Crypto.Cipher模块,此方法类似于MySQL的方式,可以自定义加密串,解密时也许用到对应的加密串,安全性相对较高。

使用前需先安装Crypto
pip install Crypto

测试程序如下:

!/usr/bin/python

coding=utf-8

from Crypto.Cipher import AES
import pymysql as mdb
from binascii import b2a_hex, a2b_hex
import sys

sor_conn = mdb.connect(host='127.0.0.1',port=3306,user='root',passwd='Root@Py123')
sor_cur = sor_conn.cursor()

class PyDbString():

    def __init__(self):
            self.key = 'pythonkey2020320'
            self.mode = AES.MODE_CBC

    def addString(self, text):
            cryptor = AES.new(self.key, self.mode, self.key)
            length = 32
            count = len(text)
            add = length - (count % length)
            text = text + ('\0' * add)
            self.ciphertext = cryptor.encrypt(text)
            return b2a_hex(self.ciphertext)

    def desString(self, text):
            cryptor = AES.new(self.key, self.mode, self.key)
            plain_text = cryptor.decrypt(a2b_hex(text))
            return plain_text.rstrip('\0')

v_strpass = PyDbString()

v_sql = "select name,tel,pwd from bak_db.f_user"
result_tb = sor_cur.execute(v_sql)
t = sor_cur.fetchall()
for col in t:

    v_name = col[0]
    v_tel = col[1]
    v_pwd = col[2]

    print(v_pwd)
    v_pwd = v_strpass.addString(v_pwd)  # 加密
    v_sql_insert = "insert into  bak_db.f_user_p(name,tel,pwd) values('%s','%s','%s');" %(v_name,v_tel,v_pwd)
    sor_cur.execute(v_sql_insert)
    sor_conn.commit()

sor_conn.close()

查看数据如下:

解密的方法可以将上例中的addstring 改为desString即可。

上面通过三种方式进行数据的加密、解密,个人推荐第三种方式,从应用层自定义加密。另外,此方法只是加密,实际应用中可能要用加密、混淆等脱敏方法来保障数据安全,另外,很多情况下没有解密方式,即不可逆,有兴趣的可以多多沟通,感谢!

原文地址https://www.cnblogs.com/gjc592/p/12463892.html

登录 后评论
下一篇
云栖号资讯小编
1168人浏览
2020-06-01
相关推荐
Fabric区块链敏感数据加密方案
635人浏览
2020-02-02 18:52:03
5.9. 数据加密
2560人浏览
2017-12-18 17:47:29
数据库加密
883人浏览
2019-07-19 17:52:15
0
1
0
376