MySQL集成校园管理系统登录验证cur.fetchall()匹配异常问题

问题背景 #

我正在为校园项目开发一套管理系统，需集成MySQL数据库。定义了verify_login()函数，用于根据用户角色（管理员/访客）展示对应界面。MySQL登录表包含role和password两列，分别存储'admin'和'guest'对应的数据。

现有代码 #

import mysql.connector as connector

conn = connector.connect(host='localhost', username='root', password='root', database='ip_project')
cur = conn.cursor()

def verify_login(win: Tk, role, pwd):
    cred = [(role.strip().lower(), pwd)]
    
    cur.execute(f'SELECT * FROM LOGIN WHERE ROLE = "{role.lower()}"')
    
    if cred == cur.fetchall() and role == 'admin'.casefold():
        win.destroy()
        
        admin()
    elif cred == cur.fetchall() and role == 'guest'.casefold():
        win.destroy()
        
        guest()
    elif cred != cur.fetchall():
        mb.showerror('Login failed!', 'The entered username and password are incorrect! \nPlease recheck them and try again')

问题现象 #

• 当cred为[('guest', 'guest')]时，总是弹出错误提示框；
• 当cred为[('admin', 'admin')]时，仅约60%概率能正常进入管理员界面；
• 访客登录失败后再尝试管理员登录也无法成功。

解决方案 #

1. 核心问题：游标结果被重复读取耗尽 #

cur.fetchall()会一次性取出游标中的所有结果，之后游标就会处于空状态。你连续三次调用cur.fetchall()：

• 第一次if判断时已取空结果；
• 后续elif判断时，cur.fetchall()返回空列表[]，自然和cred不相等，导致逻辑错误。

修复： 先将查询结果存入变量，再用该变量完成所有判断：

def verify_login(win: Tk, role, pwd):
    role_lower = role.strip().lower()
    cred = [(role_lower, pwd)]
    
    cur.execute(f'SELECT * FROM LOGIN WHERE ROLE = "{role_lower}"')
    # 先存储查询结果，避免重复读取游标
    db_result = cur.fetchall()
    
    if cred == db_result:
        win.destroy()
        if role_lower == 'admin':
            admin()
        elif role_lower == 'guest':
            guest()
    else:
        mb.showerror('Login failed!', 'The entered username and password are incorrect! \nPlease recheck them and try again')

2. 修复SQL注入漏洞 #

原代码用字符串拼接生成SQL语句，存在SQL注入风险，改用参数化查询：

# 替换原execute语句
cur.execute('SELECT * FROM LOGIN WHERE ROLE = %s', (role_lower,))

3. 优化数据库连接管理 #

全局连接和游标容易因长期占用导致异常，建议在函数内创建并及时关闭：

def verify_login(win: Tk, role, pwd):
    role_lower = role.strip().lower()
    cred = [(role_lower, pwd)]
    
    # 函数内创建连接和游标，使用后关闭
    conn = connector.connect(host='localhost', username='root', password='root', database='ip_project')
    cur = conn.cursor()
    
    try:
        cur.execute('SELECT * FROM LOGIN WHERE ROLE = %s', (role_lower,))
        db_result = cur.fetchall()
        
        if cred == db_result:
            win.destroy()
            if role_lower == 'admin':
                admin()
            elif role_lower == 'guest':
                guest()
        else:
            mb.showerror('Login failed!', 'The entered username and password are incorrect! \nPlease recheck them and try again')
    finally:
        # 确保资源被释放
        cur.close()
        conn.close()

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