Python+SQLite用户认证：如何从数据库表提取账号密码到变量

原代码的核心问题 #

1. 认证环节未执行查询：你写的SELECT语句只是个字符串，既没连接数据库，也没执行查询，自然拿不到验证结果
2. SQL注入风险：用字符串拼接生成SQL语句，恶意用户可通过输入特殊字符篡改SQL逻辑
3. 密码明文存储：直接存明文密码是严重安全漏洞，一旦数据库泄露，所有用户密码都会暴露
4. 创建账号逻辑错误：customercreate函数里判断choice == '1'才让用户输入信息，但主逻辑里只有choice != '1'才调用这个函数，会导致变量未定义就执行INSERT，直接报错

修复后的完整代码 #

import sqlite3 as sql
import hashlib

def hash_password(password):
    # 用SHA256加盐哈希密码，提升安全性
    salt = "your_custom_salt_here"  # 替换成自己的专属盐值，不要用默认内容
    return hashlib.sha256((password + salt).encode()).hexdigest()

def customer_create():
    conn = sql.connect('customers1.db')
    c = conn.cursor()

    # 创建表，调整用户名唯一约束、密码字段长度以适配哈希值
    c.execute("""CREATE TABLE IF NOT EXISTS customers1
        (sipID text PRIMARY KEY,
        firstname text,
        lastname text,
        phone varchar(20),
        town text(30),
        customeruser text UNIQUE,
        customerpass text(64) )""")

    # 直接获取用户输入，无需判断choice（调用此函数即为创建账号）
    sipID = input("Please enter your sipID number: ")
    firstname = input("Please enter your first name: ")
    lastname = input("Please enter your last name: ")
    phone = input("Please enter your phone number: ")
    town = input("Please enter your town/city of residence: ")
    customeruser = input("Please create a username for your account: ")
    customerpass = input("Please create a password for your account: ")

    # 哈希密码后再存储
    hashed_pass = hash_password(customerpass)

    try:
        c.execute("INSERT INTO customers1 VALUES(?,?,?,?,?,?,?)",
                  (sipID, firstname, lastname, phone, town, customeruser, hashed_pass))
        conn.commit()
        print("\033[32mInformation Entered Successfully\033[0m")
    except sql.IntegrityError:
        # 处理sipID或用户名重复的情况
        print("\033[31mError: sipID or username already exists!\033[0m")
    finally:
        conn.close()  # 用完数据库连接及时关闭

def customer_login():
    username = input("Please enter your username: ")
    password = input("Please enter your password: ")
    hashed_pass = hash_password(password)

    conn = sql.connect('customers1.db')
    c = conn.cursor()

    # 用参数化查询避免SQL注入
    c.execute("SELECT customeruser FROM customers1 WHERE customeruser = ? AND customerpass = ?",
              (username, hashed_pass))
    result = c.fetchone()  # 有匹配返回元组，无匹配返回None

    conn.close()

    if result:
        print("\033[32mLogin successful!\033[0m")
        # 此处可添加登录后的业务逻辑
    else:
        print("\033[31mInvalid username or password!\033[0m")

# 主逻辑
choice = input("Press 1 to sign in as a customer, press 2 to create a customer account: ")

if choice == '2':
    customer_create()
elif choice == '1':
    customer_login()
else:
    print("Invalid choice!")

关键优化点说明 #

• 密码哈希存储：用SHA256加盐哈希，即使数据库泄露，攻击者也无法直接获取明文密码
• 参数化查询：用?作为占位符传递参数，彻底杜绝SQL注入风险
• 函数职责拆分：将创建账号和登录拆分为独立函数，逻辑更清晰易维护
• 错误处理：捕获主键/用户名重复的异常，避免程序崩溃
• 连接管理：每次数据库操作后关闭连接，避免资源泄漏

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