mysql数据库表加密字段

import mysql.connector
from Crypto.Cipher import AES
from Crypto.Util.Padding import pad, unpad
import base64

# 连接数据库
db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

cursor = db.cursor()

# 加密函数
def encrypt_field(field_value, key):
    cipher = AES.new(key, AES.MODE_CBC)
    ct_bytes = cipher.encrypt(pad(field_value.encode(), AES.block_size))
    iv = base64.b64encode(cipher.iv).decode('utf-8')
    ct = base64.b64encode(ct_bytes).decode('utf-8')
    return iv + ':' + ct

# 解密函数
def decrypt_field(encrypted_value, key):
    iv, ct = encrypted_value.split(':')
    iv = base64.b64decode(iv)
    ct = base64.b64decode(ct)
    cipher = AES.new(key, AES.MODE_CBC, iv)
    pt = unpad(cipher.decrypt(ct), AES.block_size).decode()
    return pt

# 示例：加密和解密字段
key = b'ThisIsASecretKey123'  # 16字节密钥
field_value = "SensitiveData"

# 加密
encrypted_value = encrypt_field(field_value, key)
print(f"Encrypted Value: {encrypted_value}")

# 解密
decrypted_value = decrypt_field(encrypted_value, key)
print(f"Decrypted Value: {decrypted_value}")

# 将加密后的值插入数据库
cursor.execute("INSERT INTO yourtable (encrypted_field) VALUES (%s)", (encrypted_value,))
db.commit()

# 查询并解密
cursor.execute("SELECT encrypted_field FROM yourtable WHERE id = %s", (1,))
result = cursor.fetchone()
if result:
    decrypted_value = decrypt_field(result[0], key)
    print(f"Decrypted Value from DB: {decrypted_value}")

cursor.close()
db.close()