How to implement encryption and decryption of sensitive data in MySQL database

With the advent of the big data era, data has become one of the most important assets of an enterprise, and data encryption is also an important means of protecting data assets. This article mainly demonstrates some simple ways of data encryption by combining learning MySQL functions and Python encryption methods.

1. Preparation

In order to facilitate subsequent comparison, data sets of various types are stored in different tables.

Create a table of original plaintext data

/* Create the original data table */

 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`)
);
/* Add new raw data */
INSERT INTO `f_user` VALUES (1,'Cao Cao','10000000000','Cc@123'),(2,'Guan Yu','21000000000','Guanyu@21'),(3,'Liu Bei','20000000000','LB#200000');

Creating MySQL Encrypted Tables

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

Creating a Python Encrypted Table

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 encryption function method

2.1 MySQL Encryption

Insert the data in the plaintext table into f_user_m, and encrypt and store the pwd password field. Remember the encrypted string because this value will be used when decrypting.

/* Encrypted password field */
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

The stored results are as follows

Note:

If the encrypted data is directly stored in a varchar type field, the following error will occur:

ERROR 1366 (HY000): Incorrect string value: '\xF0K+!\x15?...' for column 'pwd' at row 1

There are three ways to handle this:

1) The encrypted data must be stored in a binary field such as varbinary/binary/blob in the utf8 character set, so the type of the password field in the above text is set to varbinary type

2) You can use the HEX() function to convert the encrypted data into hexadecimal format and store it. When retrieving the data, use UNHEX() to process it before decrypting it.

3) Use latin1 character set to store directly in varchar type field, but it is not recommended

There are three ways to test and handle it yourself.

2.2 MYSQL Decryption

For encrypted data, you can use the MySQL decryption function AES_DECRYPT to decrypt and view the plain text

mysql> select name,tel,AES_DECRYPT(pwd,'MySQL')pwd from f_user_m; 
+--------+-------------+-----------+
| name | tel | pwd |
+--------+-------------+-----------+
| Cao Cao | 10000000000 | Cc@123 |
| Guanyu| 21000000000 | Guanyu@21 |
| Liu Bei | 20000000000 | LB#200000 |
+--------+-------------+-----------+
3 rows in set (0.00 sec)

The data viewed at this time is consistent with the plaintext table.

3. Python base64 encryption method

3.1 Encryption using Python’s encodestring method

Write a python script to encrypt the data and insert it into the table

#!/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) # Encryption 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()

The encrypted data is as follows:

/* The encrypted data is as follows*/
mysql> select * from f_user_p;
+----+--------+-------------+---------------+
| id | name | tel | pwd |
+----+--------+-------------+---------------+
| 1 | Cao Cao | 10000000000 | Q2NAMTIz
  |
| 2 | Guan Yu | 21000000000 | R3Vhbnl1QDIx
 |
| 3 | Liu Bei | 20000000000 | TEIjMjAwMDAw
 |
+----+--------+-------------+---------------+
3 rows in set (0.00 sec)

3.2 Decryption using Python's decodestring method

The decryption method uses base64.decodestring method, which is relatively simple and can be tested by yourself.

Note: This method does not use an encryption string for encryption and decryption, so the security is relatively low. Therefore, you can continue to use another method.

4. Python AES algorithm encryption

The AES algorithm requires the Crypto.Cipher module. This method is similar to the MySQL method. You can customize the encryption string, and the corresponding encryption string may be used during decryption, which is relatively secure.

You need to install Crypto before use

pip install Crypto

The test procedure is as follows:

#!/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) # Encryption 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()

View the data as follows:

The decryption method can be to change addstring in the above example to desString.

The above three methods are used to encrypt and decrypt data. I personally recommend the third method, which is custom encryption from the application layer. In addition, this method is only encryption. In actual applications, encryption, obfuscation and other desensitizing methods may be needed to ensure data security. In addition, in many cases there is no decryption method, that is, it is irreversible. If you are interested, please communicate with us. Thank you!

Summarize

This is the end of this article about how to implement encryption and decryption of sensitive data in MySQL database. For more relevant MySQL data encryption and decryption content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!