如何基于Tkinter用户输入变量查询SQLite3数据库并获取关联密码？

Let's walk through the issues in your code and fix them to properly check if a username exists and retrieve its corresponding password.

First, the key problems in your current code: #

• SQL Syntax & Security Issue: You're directly inserting the username variable into your SQL query without proper formatting, which causes a syntax error. Worse, this exposes you to SQL injection attacks—always use parameterized queries instead of raw variable insertion.
• Duplicate Entry Call: You're fetching both username and password from entry_user.get()—you should have two separate Tkinter Entry widgets (one for username, one for password).
• Not Fetching Query Results: The c.execute() call returns a cursor object, not the actual data. You need to use methods like fetchone() or fetchall() to pull the results from the query.
• Backwards Condition: Your logic checks if database_username != ' ' to say the username doesn't exist, but that's reversed—you should check if no results were returned instead.

Corrected Full Code #

Here's a complete, working version that addresses all these issues:

import sqlite3
import tkinter as tk

def check_credentials():
    # Get user input from Tkinter entries (strip extra whitespace)
    username = entry_user.get().strip()
    user_input_password = entry_pass.get().strip()

    # Use a context manager to auto-handle database connection cleanup
    with sqlite3.connect('passwordDb.db') as conn:
        c = conn.cursor()
        # Parameterized query: ? is a safe placeholder for variables
        c.execute("SELECT employee_password FROM passwordDb WHERE employee_username = ?", (username,))
        # Fetch first matching row (returns None if no match exists)
        result = c.fetchone()

    if result is None:
        print("You entered a username that is not in the database")
    else:
        # Extract stored password from the result tuple
        stored_password = result[0]
        # Optional: Verify input password against stored password
        if user_input_password == stored_password:
            print("Credentials are valid!")
            running = True
        else:
            print("Incorrect password")

# Set up the Tkinter login window
root = tk.Tk()
root.title("Employee Login")

# Username field
tk.Label(root, text="Username:").grid(row=0, column=0, padx=5, pady=5)
entry_user = tk.Entry(root)
entry_user.grid(row=0, column=1, padx=5, pady=5)

# Password field (show * for privacy)
tk.Label(root, text="Password:").grid(row=1, column=0, padx=5, pady=5)
entry_pass = tk.Entry(root, show="*")
entry_pass.grid(row=1, column=1, padx=5, pady=5)

# Login button to trigger credential check
tk.Button(root, text="Login", command=check_credentials).grid(row=2, column=0, columnspan=2, pady=10)

root.mainloop()

Key Explanations: #

• Parameterized Queries: The ? in the SQL statement is safely replaced by the username variable—SQLite handles escaping, eliminating syntax errors and injection risks.
• Context Manager (with statement): Automatically closes the database connection when done, so you don't have to remember to call conn.close().
• fetchone(): Returns the first matching row as a tuple (e.g., ("user123pass",)). If no rows match, it returns None, which we use to detect non-existent usernames.
• Tkinter UI Setup: Added proper widgets for username and password, plus a button to trigger the check—your original code was missing the core Tkinter window setup.

Additional Best Practices: #

• Never store plain-text passwords: For production use, hash passwords with a library like bcrypt before storing them, then hash the user's input to compare it (never compare plain text).
• Add input validation: Check that username/password fields aren't empty before querying the database to avoid unnecessary database calls.

内容的提问来源于stack exchange，提问作者nhm64 rtwh