基于MySQL与Python的数据加解密：存储加密数据的解密实现问询

Hey there! Let's work through decrypting your stored MySQL data so your users can view it properly. You're already on the right track with MySQL's AES functions—we just need to tweak your query and code to handle decryption correctly.

Key Pre-Requisites to Remember #

First, a couple critical points before diving into code:

• Matching Encryption Key: The secret key you used with AES_ENCRYPT when saving data must be exactly the same key you use for AES_DECRYPT. Keep this key secure—never hardcode it directly in your code (use environment variables or a secure config system instead).
• Avoid SQL Injection: Your current query uses string concatenation for the username, which is a security risk. We'll fix that with parameterized queries too.

Modified viewNumber Function #

Here's your updated code with working decryption logic:

import tkinter as tk
from tkinter import ttk
import os  # For secure key retrieval (optional but highly recommended)

def viewNumber():
    for widget in window.winfo_children():
        widget.destroy()
    window.geometry("760x550")
    window.configure(background="#CDCDCD")
    
    mycursor = mydb.cursor()
    mycursor.execute("CREATE TABLE IF NOT EXISTS numbers (id VARCHAR(1000), owner VARCHAR(1000), number VARCHAR(1000));")
    mydb.commit()
    
    text = "View your list"
    textO = tk.Label(window, text=text, background="#CDCDCD", fg='black', font=('Helvetica',13))
    textO.grid(row=0, column=0, padx=0, pady=15)
    
    # Securely fetch your encryption key (replace with your actual key management method)
    # Using an environment variable keeps sensitive data out of your codebase
    encryption_key = os.getenv("DB_ENCRYPTION_KEY") or "your_secure_secret_key_here"
    
    # Updated query: use AES_DECRYPT and parameterization to avoid injection
    sql = """
        SELECT 
            AES_DECRYPT(owner, %s) AS decrypted_owner,
            AES_DECRYPT(number, %s) AS decrypted_number
        FROM numbers 
        WHERE id = MD5(%s);
    """
    # Pass parameters as a tuple to safely inject values
    mycursor.execute(sql, (encryption_key, encryption_key, username))
    rows = mycursor.fetchall()
    
    tv = ttk.Treeview(window, selectmode='browse', height=15)
    tv.grid(row=1, column=0, padx=18, pady=20)
    tv["columns"]=("1", "2")
    tv['show']='headings'
    tv.column("1", width=320, anchor='c')
    tv.column("2", width=320, anchor='c')
    tv.heading("1", text="Owner")
    tv.heading("2", text="Number")
    
    # Convert binary decrypted values to readable strings
    for row in rows:
        # AES_DECRYPT returns bytes; decode to UTF-8 strings (adjust encoding if needed)
        decrypted_owner = row[0].decode('utf-8') if row[0] else "N/A"
        decrypted_number = row[1].decode('utf-8') if row[1] else "N/A"
        tv.insert('', 'end', values=(decrypted_owner, decrypted_number))
    
    backBut = tk.Button(text="Back to the menu", command=numbers, background="#FDB552", activebackground="#FDB552", width=25)
    backBut.grid(row=16, column=0, pady=30)
    
    text = "© 2022 Clowny. All rights reserved."
    textO = tk.Label(window, text=text, background="#CDCDCD")
    textO.grid(row=17, column=0, padx=5, pady=15, sticky="w")

What Changed? #

1. Decryption in SQL: We added AES_DECRYPT to the SELECT clause, targeting your encrypted owner and number columns with your secret key. We aliased the results for clarity.
2. Parameterized Query: Instead of concatenating username directly into the SQL string, we use %s placeholders and pass values as a tuple. This eliminates SQL injection risks.
3. Binary to String Conversion: AES_DECRYPT returns raw binary data, so we decode it to UTF-8 strings before adding to the Treeview. We also handle cases where decrypted values might be None (e.g., empty columns).
4. Secure Key Handling: We included a note about using environment variables to store your encryption key—this keeps sensitive data out of your code repository.

Quick Extra Tips #

• Ensure your insertion logic matches this decryption code. For example, your insert query should use AES_ENCRYPT with the same key:

  INSERT INTO numbers (id, owner, number) 
  VALUES (MD5(%s), AES_ENCRYPT(%s, %s), AES_ENCRYPT(%s, %s));
  

• As a side note: MD5 is no longer considered secure for hashing credentials. For better security, look into libraries like bcrypt or argon2-cffi for storing user passwords instead.

内容的提问来源于stack exchange，提问作者Alessandro Bottini