Shulou(Shulou.com)06/01 Report--

With the arrival of big data era, data has become one of the most important assets of enterprises, and data encryption is also an important means to protect data assets. This article mainly demonstrates some simple ways of data encryption through MySQL function and Python encryption method.

1. Preparatory work

For later comparison, various datasets are stored in different tables.

Create the original plaintext data table

/ * create the original data table * / CREATE TABLE `fuser` (`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 raw data * / INSERT INTO `f _ user` VALUES (1pi 'Cao Cao', '10000000000'), (2meme 'Guan Yu', '210000000000'), (3Med 'Liu Bei', '200000000000')

Create a MySQL encryption table

CREATE TABLE `froomuserm` (`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`))

Create a python encryption table

CREATE TABLE `froomuserp` (`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 2.1 MySQL encryption

Insert the data from the plaintext table into the f_user_m while encrypting the pwd password field, remembering the encrypted string because this value is used for decryption.

/ * encryption password field * / mysql > insert into f_user_m (name,tel,pwd) select name,tel,AES_ENCRYPT (pwd,'MySQL') from favored username Records query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0

The stored results are as follows

Note:

The encrypted data is directly stored in the field of varchar type, and the following error occurs:

ERROR 1366 (HY000): Incorrect string value:'\ xF0Kwon!\ x15kg.' For column 'pwd' at row 1

It can be handled in three ways:

1) the encrypted data must be stored in the fields of binary field attributes such as varbinary/binary/blob under the utf8 character set, so the type of password field above is set to varbinary.

2) you can use the HEX () function to hexadecimal the encrypted data, and use UNHEX () to process the encrypted data before decrypting it.

3) the latin1 character set is directly stored in the varchar type field, but it is not recommended

There are three ways to test and deal with it yourself.

2.2 MYSQL decryption

For encrypted data, you can use MySQL's decryption function AES_DECRYPT to decrypt and view plaintext.

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

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

3. Python base64 encryption method 3.1 encrypt using Python's encodestring method

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

#! / usr/bin/python# coding=utf-8import pymysql as mdbimport base64sor_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 int: 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 "sor_cur.execute (v_sql_insert) sor_conn.commit () sor_conn.close ()

The encrypted data is queried 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 Standby | 20000000000 | TEIjMjAwMDAw | +-+ 3 rows in set (0.00 sec) 3.2 decrypt using Python's decodestring method

The decryption is carried out by base64.decodestring method, which is relatively simple and can be tested by itself.

Note: no encryption string is added to the encryption and decryption of this method, so the security is relatively low, so it can continue to be carried out in another way.

4. Python AES algorithm encryption

AES algorithm needs to use Crypto.Cipher module, this method is similar to MySQL, you can customize the encryption string, decryption may use the corresponding encryption string, the security is relatively high.

Crypto needs to be installed before use

Pip install Crypto

The test procedure is as follows:

#! / usr/bin/python# coding=utf-8from Crypto.Cipher import AESimport pymysql as mdbfrom binascii import b2a_hex, a2b_heximport syssor_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 int: 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') "sor_cur.execute (v_sql_insert) sor_conn.commit () sor_conn.close ()

View the data as follows:

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

Above, there are three ways to encrypt and decrypt data, and the third way is recommended by individuals, which is custom encryption from the application layer. In addition, this method is only encryption, practical applications may need to use encryption, confusion and other desensitization methods to ensure data security, in addition, in many cases there is no decryption, that is, irreversible, interested can communicate more, thank you!

Summary

This is the end of this article on the implementation of MySQL database encryption and decryption of sensitive data, more relevant mysql data encryption and decryption content, please search the previous articles or continue to browse the following related articles hope that you will support more in the future!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.