[大数据]数据库敏感数据加密技术

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

1. MySQL加密解密方式

1.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`)
);

1.2 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类型字段中，但是不推荐
   三种方式可以自行测试处理。

1.3 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)

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

2.Oracle加密解密方式

2.1md5加密

CREATE OR REPLACE FUNCTION MD5(
passwd IN VARCHAR2)
RETURN VARCHAR2
IS
retval varchar2(32);
BEGIN
retval := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => passwd)) ;
RETURN retval;
END;

select md5(1) from dual

2.2DES加密

create or replace function 
encrypt_des(p_text varchar2, p_key varchar2) return varchar2 is
v_text varchar2(4000);
v_enc varchar2(4000);
raw_input RAW(128) ;
key_input RAW(128) ;
decrypted_raw RAW(2048);
begin
v_text := rpad( p_text, (trunc(length(p_text)/8)+1)*8, chr(0));
raw_input := UTL_RAW.CAST_TO_RAW(v_text);
key_input := UTL_RAW.CAST_TO_RAW(p_key);
dbms_obfuscation_toolkit.DESEncrypt(input => raw_input,key => key_input,encrypted_data =>decrypted_raw);
v_enc := rawtohex(decrypted_raw);
dbms_output.put_line(v_enc);
return v_enc;
end;

2.3DES解密

create or replace function decrypt_des(p_text varchar2,p_key varchar2) return varchar2 is
v_text varchar2(2000);
begin
dbms_obfuscation_toolkit.DESDECRYPT(input_string => UTL_RAW.CAST_TO_varchar2(p_text),key_string =>p_key, decrypted_string=> v_text);
v_text := rtrim(v_text,chr(0));
dbms_output.put_line(v_text);
return v_text;
end;

3.SQL server加密解密方式

ENCRYPTBYASYMKEY() --非对称密钥
ENCRYPTBYCERT() --证书加密
ENCRYPTBYKEY() --对称密钥
ENCRYPTBYPASSPHRASE() --通行短语（PassPhrase）加密

在SQL Server 2005和SQL Server 2008之前。如果希望加密敏感数据，如财务信息、工资或身份证号，必须借助外部应用程序或算法。SQL Server 2005引入内建数据加密的能力，使用证书、密钥和系统函数的组合来完成。
与数字证书类似。SQL Server 证书包括公钥和私钥这一对密钥，它们都用来加密和解密数据。SQL Server也拥有创建非对称密钥和对称密钥对象的能力。非对称密钥（asymmetric key）与证书相似，公钥用来加密数据库，私钥用来解密数据。非对称密钥和证书都提供了强大的加密强度。但在完成复杂的加密|解密过程中具有更多的性能开销。更适合对大量数据进行加密，且具有较低性能开销的解决方案是对称密钥（symmetric key），它是对相同数据进行加密和解密的一个密钥。
SQL Server允许将这些加密能力放到加密层次结构中。当安装了SQL Server后，在数据库master中创建名为服务主密钥的服务器级别证书，并将其默绑定到SQL Server服务账号登录名。服务主密钥用来加密所有其他数据库证书和创建在SQL Server实例中的密钥。另外，你也可以在用户数据库中创建数据库主密钥（Database Master Key）,它可以用来加密数据库证书和密钥。
在SQL Server 2008中，微软引入了透明数据加密(TDE),它对整个数据库进行加密，而不需要修改任何访问它的应用程序。数据、日志文件和相关的数据库备份都是加密的。假如数据库被偷，如果没有数据库加密密钥（DEK）是不能访问数据的。

3.1通过通行短语(PassPhrase)加密

对于不涉及证书及密钥的应急的数据加密，可以直接基于用户提供的密码来加密和解密数据。通行短语(PassPhrase)是允许存在空格的密码。这个PassPhrase不会存储在数据库中，因而也就意味着不会被使用存储的系统数据“破解”。同时，可以使用空格创建一个长的、易于记忆的句子来加密和解密敏感数据。
加密函数

CREATE FUNCTION dbo.EncryptByPassPhrasePwd(@password nvarchar(max))
RETURNS varbinary(max)
AS  
BEGIN 
    declare @pwd varbinary(max)
 SELECT @pwd = EncryptByPassPhrase(
 '1234567',            
 @password)
    return @pwd
END

解密函数

CREATE  FUNCTION dbo.DecryptByPassPhrasePwd(@password varbinary(max))
RETURNS nvarchar(max)
AS  
BEGIN 
    declare @pwd nvarchar(max)
 SELECT @pwd =CAST( DecryptByPassPhrase('1234567',@password)  as nvarchar(max))
    return @pwd
END

注意：123456 是用于生成对称密钥的通行短语
select dbo.EncryptByPassPhrasePwd(‘test11’) as result
select dbo.DecryptByPassPhrasePwd(0x010000004779C35F96DACC0EC6A8C518E186D203B1A336EE5B8A51B4271B54F56F516ECE) as result

SQL server其他加密方式查看文档：
https://www.cnblogs.com/lyhabc/articles/3187453.html

4. 应用端Python 加密解密方式

4.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)

4.2 使用Python的decodestring方法解密

解密的方式采用base64.decodestring方法进行，该方法比较简单，可以自行测试.
注： 此方法的加、解密没有加密串进行加成，安全性相对较低，因此可以继续采用另一种方式进行。

4.3 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即可。

4.4两种加密算法结合适用

双层加密测试：（兼容python3.7版本）

# -*- coding: utf-8 -*-
import base64
from Crypto.Cipher import AES
 
 
#用aes加密，再用base64  encode
def aes_encrypt(data): 
    key='8888888888888888'  #加密时使用的key，只能是长度16,24和32的字符串
    BS = AES.block_size
    pad = lambda s: s + (BS - len(s) % BS) * chr(BS - len(s) % BS)
    cipher = AES.new(key)
    encrypted = cipher.encrypt(pad(data))  #aes加密
    result = base64.b64encode(encrypted)  #base64 encode
    return result 
 
#把加密的数据，用base64  decode，再用aes解密
def aes_decode(data):
    key='8888888888888888'
    # unpad = lambda s : s[0:-ord(s[-1])]
    unpad = lambda s : s[:-ord(s[len(s)-1:])]
    cipher = AES.new(key)
    result2 = base64.b64decode(data)
    decrypted = unpad(cipher.decrypt(result2))
    return  decrypted  
 
data = 'jibobo'  #需要加密的字符串
print(data)
res1 = aes_encrypt(data) #加密
res1 = str(res1,'utf-8')
print(res1)     #加密后的字符串
res2 = aes_decode(res1)  #解密
print(str(res2,'utf-8')) #解密后的字符串

结果展示：

双层加密测试：（兼容python3.8版本）

# -*- coding: utf-8 -*-
import base64
from Crypto.Cipher import AES
 
 
#用aes加密，再用base64  encode
def aes_encrypt(data): 
    key=b'8888888888888888'  #加密时使用的key，只能是长度16,24和32的字符串
    BS = AES.block_size
    pad = lambda s: s + (BS - len(s) % BS) * chr(BS - len(s) % BS)
    cipher = AES.new(key, AES.MODE_ECB)
    encrypted = cipher.encrypt(str.encode(pad(data)))  #aes加密
    result = base64.b64encode(encrypted)  #base64 encode
    return result 
 
#把加密的数据，用base64  decode，再用aes解密
def aes_decode(data):
    key=b'8888888888888888'
    unpad = lambda s : s[:-ord(s[len(s)-1:])]
    cipher = AES.new(key, AES.MODE_ECB)
    result2 = base64.b64decode(data)
    decrypted = unpad(cipher.decrypt(result2))
    return  decrypted  
 
if __name__ == '__main__':
 
    data = 'jibobo'  #需要加密的字符串
    print(data)
    res1 = aes_encrypt(data) #加密
    res1 = str(res1,'utf-8')
    print(res1)
    res2 = aes_decode(res1)  #解密
    print(str(res2,'utf-8')) #解密后的字符串

结果展示：

其他写法方式：

#coding=utf-8
#AES AES/CBC/PKCS5|Zero
 
import base64
from Crypto.Cipher import AES
 
# pip install pycryptodome
 
'''
采用AES对称加密算法
'''
# str不是16的倍数那就补足为16的倍数. ZeroPadding
 
'''
    在PKCS5Padding中，明确定义Block的大小是8位
    而在PKCS7Padding定义中，对于块的大小是不确定的，可以在1-255之间
    PKCS #7 填充字符串由一个字节序列组成，每个字节填充该字节序列的长度。
    假定块长度为 8，数据长度为 9，
    数据： FF FF FF FF FF FF FF FF FF
    PKCS7 填充： FF FF FF FF FF FF FF FF FF 01 01 01 01 01 01 01   ?应该是填充01
    
    python3:填充bytes(这个说法不对,AES的参数是字符串,不是byte)
    length = 16 - (len(data) % 16)
    data += bytes([length])*length
 
    python2:填充字符串
    length = 16 - (len(data) % 16)
    data += chr(length)*length
    
    pad = lambda s: s + (BS - len(s) % BS) * chr(BS - len(s) % BS)
    unpad = lambda s : s[0:-ord(s[-1])]
'''
 
class AES_CBC:
 
    def add_to_16(self, value):
        while len(value) % 16 != 0:
            value += '\0'
        return str.encode(value)  # 返回bytes
 
    #加密方法
    def encrypt_oracle(self, key, text):
        # iv=self.add_to_16(key)   #多了个iv
        # 偏移量 16个0
        iv = "0000000000000000"
        # 初始化加密器
        aes = AES.new(self.add_to_16(key), AES.MODE_CBC, self.add_to_16(iv))
        bs = AES.block_size
        pad2 = lambda s: s + (bs - len(s) % bs) * chr(bs - len(s) % bs)#PKS7 
    
        encrypt_aes = aes.encrypt(str.encode(pad2(text)))
    
        # 用base64转成字符串形式
        # 执行加密并转码返回bytes
        encrypted_text = str(base64.encodebytes(encrypt_aes), encoding='utf-8')  
        print(encrypted_text)
        #和js的 结果相同 http://tool.chacuo.net/cryptaes
        return encrypted_text
    
    #解密方法
    def decrypt_oralce(self, key, text):
        # 初始化加密器
        # 偏移量 16个0
        iv = "0000000000000000"
        aes = AES.new(self.add_to_16(key), AES.MODE_CBC, self.add_to_16(iv))
        #优先逆向解密base64成bytes
        base64_decrypted = base64.decodebytes(text.encode(encoding='utf-8'))
        #
        decrypted_text = str(aes.decrypt(base64_decrypted), encoding='utf-8') # 执行解密密并转码返回str
        unpad = lambda s : s[0:-ord(s[-1])]
        #PADDING = '\0'
        #print decrypted_text.rstrip(PADDING)  #zeropadding只见诶去掉结尾\0
        # print(unpad(decrypted_text))
        return unpad(decrypted_text)
 
 
if __name__ == '__main__':
    aes = AES_CBC()
    #加密
    key = "8888888888888888"
    enc_text = aes.encrypt_oracle(key, "jibobo")
 
    #解密
    dec_text = aes.decrypt_oralce(key, enc_text)
    print(key)
    print(enc_text)
    print(dec_text)