逃避动态sqlite查询?[英] Escaping dynamic sqlite query?

本文是小编为大家收集整理的关于逃避动态sqlite查询?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我目前正在根据用户的输入来构建SQL查询.一个示例在这里如何看待:

def generate_conditions(table_name,nameValues):
    sql = u""
    for field in nameValues:
        sql += u" AND {0}.{1}='{2}'".format(table_name,field,nameValues[field])
    return sql

search_query = u"SELECT * FROM Enheter e LEFT OUTER JOIN Handelser h ON e.Id == h.Enhet WHERE 1=1"

if "Enhet" in args:
    search_query += generate_conditions("e",args["Enhet"])
c.execute(search_query)

由于SQL每次无法在执行调用中插入值时都会更改,这意味着我应该手动逃脱字符串.但是,当我搜索每个人指向执行...

我也对自己的查询方式不满意,因此,如果有人对另一种想法也很棒!

推荐答案

您有两个选择:

  1. 切换到使用 sqlalchemy ;它将使生成动态SQL多于Pythonic 和确保正确引用.

  2. 由于您不能将参数用于表和列名称,因此您仍然必须使用字符串格式将其包含在查询中.另一方面,您的 value 应始终使用SQL参数,如果数据库可以准备语句.

    不建议仅仅从用户输入中直接获取表格和列名,它是 far 太容易以这种方式注入任意的SQL语句.根据您接受的此类名称的列表来验证表和列名.

    所以,要在您的示例上构建,我会朝这个方向前进:

    tables = {
        'e': ('unit1', 'unit2', ...),   # tablename: tuple of column names
    }
    
    def generate_conditions(table_name, nameValues):
        if table_name not in tables:
            raise ValueError('No such table %r' % table_name)
        sql = u""
        params = []
        for field in nameValues:
            if field not in tables[table_name]:
                raise ValueError('No such column %r' % field)
            sql += u" AND {0}.{1}=?".format(table_name, field)
            params.append(nameValues[field])
        return sql, params
    
    search_query = u"SELECT * FROM Enheter e LEFT OUTER JOIN Handelser h ON e.Id == h.Enhet WHERE 1=1"
    
    search_params = []
    if "Enhet" in args:
        sql, params = generate_conditions("e",args["Enhet"])
        search_query += sql
        search_params.extend(params)
    c.execute(search_query, search_params)
    

本文地址:https://www.itbaoku.cn/post/597307.html

问题描述

I'm currently building SQL queries depending on input from the user. An example how this is done can be seen here:

def generate_conditions(table_name,nameValues):
    sql = u""
    for field in nameValues:
        sql += u" AND {0}.{1}='{2}'".format(table_name,field,nameValues[field])
    return sql

search_query = u"SELECT * FROM Enheter e LEFT OUTER JOIN Handelser h ON e.Id == h.Enhet WHERE 1=1"

if "Enhet" in args:
    search_query += generate_conditions("e",args["Enhet"])
c.execute(search_query)

Since the SQL changes every time I cannot insert the values in the execute call which means that I should escape the strings manually. However, when I search everyone points to execute...

I'm also not that satisfied with how I generate the query, so if someone has any idea for another way that would be great also!

推荐答案

You have two options:

  1. Switch to using SQLAlchemy; it'll make generating dynamic SQL a lot more pythonic and ensures proper quoting.

  2. Since you cannot use parameters for table and column names, you'll still have to use string formatting to include these in the query. Your values on the other hand, should always be using SQL parameters, if only so the database can prepare the statement.

    It's not advisable to just interpolate table and column names taken straight from user input, it's far too easy to inject arbitrary SQL statements that way. Verify the table and column names against a list of such names you accept instead.

    So, to build on your example, I'd go in this direction:

    tables = {
        'e': ('unit1', 'unit2', ...),   # tablename: tuple of column names
    }
    
    def generate_conditions(table_name, nameValues):
        if table_name not in tables:
            raise ValueError('No such table %r' % table_name)
        sql = u""
        params = []
        for field in nameValues:
            if field not in tables[table_name]:
                raise ValueError('No such column %r' % field)
            sql += u" AND {0}.{1}=?".format(table_name, field)
            params.append(nameValues[field])
        return sql, params
    
    search_query = u"SELECT * FROM Enheter e LEFT OUTER JOIN Handelser h ON e.Id == h.Enhet WHERE 1=1"
    
    search_params = []
    if "Enhet" in args:
        sql, params = generate_conditions("e",args["Enhet"])
        search_query += sql
        search_params.extend(params)
    c.execute(search_query, search_params)