基于MVC架构的Flask-MySQL搜索引擎模糊查询失效问题

我用Flask搭建了一个基于MVC架构的MySQL搜索引擎，但无法检索到与存储内容非完全匹配的数据。

现有MODEL函数 #

@classmethod
def search(cls, data):
    instance_results = [] 
    query = "SELECT * FROM users WHERE first_name LIKE '" + data + "%' ORDER BY id DESC"
    results = connectToMySQL('lab_bd').query_db(query, data)
    for result in results:
        instancia = cls(result)
        instance_results.append(instancia)
    return instance_results

现有CONTROLLER函数 #

@app.route('/show/patients', methods = ['GET', 'POST'])
def show_patients():
    if request.method=='POST' and 'txtname' in request.form:
        name = request.form["txtname"]
        data = name
        print ("DATA NAMER RUTA", data)
        users = User.search(data)
        print("SEARCH USUARIOS ", users)
    else:
        users = User.get_all()
        print("USUARIOS ", users)
    return render_template("searcher.html", users = users)

此前仅支持完全匹配的可运行写法 #

路由中参数定义：data = {"first_name": name}
对应SQL语句：query = "SELECT * FROM users WHERE first_name LIKE %(first_name)s ORDER BY id DESC"

数据库连接代码 #

import pymysql.cursors

class MySQLConnection:
    def __init__(self, db):
        connection = pymysql.connect(host = 'localhost',
                                    user = 'root',
                                    password = '', 
                                    db = db,
                                    charset = 'utf8mb4',
                                    cursorclass = pymysql.cursors.DictCursor,
                                    autocommit = True)
        self.connection = connection
    def query_db(self, query, data=None):
        with self.connection.cursor() as cursor:
            try:
                query = cursor.mogrify(query, data)
                print("Running Query:", query)
     
                executable = cursor.execute(query, data)
                if query.lower().find("insert") >= 0:
                    self.connection.commit()
                    return cursor.lastrowid
                elif query.lower().find("select") >= 0:
                    result = cursor.fetchall()
                    return result
                else:
                    self.connection.commit()
            except Exception as e:
                print("Something went wrong", e)
                return False
            finally:
                self.connection.close() 
def connectToMySQL(db):
    return MySQLConnection(db)

问题根源在于SQL语句拼接错误，且未正确通过参数化查询实现模糊匹配，以下是修复步骤：

1. 修正MODEL中的查询逻辑
   禁止直接拼接字符串到SQL语句（存在SQL注入风险），应将模糊匹配的通配符%与参数值结合，通过参数化查询传递：

   @classmethod
   def search(cls, data):
       instance_results = [] 
       # 使用参数化占位符，通配符逻辑放到参数值中
       query = "SELECT * FROM users WHERE first_name LIKE %(search_term)s ORDER BY id DESC"
       # 构造参数字典，实现前缀模糊匹配；若需要全匹配可改为f"%{data}%"
       params = {"search_term": f"{data}%"}
       results = connectToMySQL('lab_bd').query_db(query, params)
       for result in results:
           instancia = cls(result)
           instance_results.append(instancia)
       return instance_results
   

2. CONTROLLER部分无需修改
   保持现有获取表单输入的逻辑即可，参数处理已在MODEL层完成。

3. 原写法仅支持完全匹配的原因
   之前的LIKE %(first_name)s未给参数值添加%，此时LIKE等同于精确匹配的=，只有给参数值加上通配符，才能实现前缀、后缀或全范围的模糊匹配。

4. SQL注入防护说明
   当前使用的cursor.mogrify配合参数化查询是正确的防护方式，直接拼接字符串会引入安全风险，需坚持使用参数化查询传递变量。

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