MySQL Data encryption for database field storage

The data in the original table is not encrypted, creating an encrypted table, looping the data inside the original table, and inserting it into the encrypted table after encryption. Finally, a trigger is created that inserts data into the original table and automatically triggers the insertion of the same data into the encrypted table.

• Encrypt data using MySQL's Aes_encrypt
• Decrypt data using MySQL's Aes_decrypt
• Because the encrypted data is ugly, it uses to_base64 transcoding data and from_base64 to decode the data.
• So the actual data stored is encrypted and transcoded.
• Viewing data is decoding the data before decrypting the data
• The script is as follows

# _*_ Coding:utf-8 _*___author__ = ' xiaoke ' __date__ = ' 2018/6/12 18:25 ' "" "Add trigger Create TRIGGER ' Auth_enc_trigger ' after in SERT on authfor all rowinsert into ' auth_enc ' (ID, real_name, id_number) VALUES (New.id,to_base64 (Aes_encrypt (new.real_n Ame, "SLWH-DFH")), To_base64 (Aes_encrypt (New.id_number, "SLWH-DFH")); Query encrypted table Select Id,aes_decrypt (From_base64 ( real_name), ' slwh-dfh '), Aes_decrypt (From_base64 (id_number), ' slwh-dfh ') from Auth_enc; ""                     Import mysqldbdb = MySQLdb.connect (host= ' localhost ', port=3306, user= ' root ', passwd= ' mypassword ', db= ' my_db ', charset= ' UTF8 ') cursor = Db.cursor () # Creates a Cursor object Cursor.execute ("select * from Auth;") lines = Cursor.fetchall () print ("Fetch data%s bar altogether")% (len (lines)) for data in Lines:id, real_name, id_number = Data sql = ' INSERT into Auth_enc (ID, real_name, id_number) VALUE (%d,to_base64 (Aes_encrypt ("%s", "SLWH-DFH")), To_base64 (Aes_ Encrypt ("%s", "SLWH-DFH")); '% (ID, real_name, id_number) print (SQL) Try:cursor.execute (sQL) Db.commit () except Exception, E:db.rollback () print (e) cursor.close () 

MySQL encrypts data stored in database fields