转义动态sqlite查询？ [英] Escaping dynamic sqlite query?

问题描述

我目前正在建立SQL查询，取决于用户的输入。可以在这里看到这样做的示例：

  def generate_conditions（table_name，nameValues）：
 sql = u 
 for nameValues中的字段：
 sql + = uAND {0}。{1} ='{2}'。format（table_name，field，nameValues [field]）
 return sql 
 
 search_query = uSELECT * FROM Enheter e LEFT OUTER JOIN Handelser h ON e.Id == h.Enhet WHERE 1 = 1
 
 if in args：
 search_query + = generate_conditions（e，args [Enhet]）
 c.execute（search_query）
  

由于SQL每次都无法在execute调用中插入值，因此我需要手动转义字符串。但是，当我搜索每个点执行...

我也不是那么满意我的生成查询，所以如果有人有任何想法的另一种方式

b >

切换至使用 SQLAlchemy ;

因为你不能使用表和列的参数名称，您仍然必须使用字符串格式将这些包括在查询中。另一方面，您的值应始终使用SQL参数，如果只有数据库可以准备语句。

不建议只是插入从用户输入直接采取的表和列名，它是太容易以任何方式注入任意SQL语句。根据您接受的此类名称列表验证表名和列名。

因此，为了建立您的示例，我会走这个方向：

  tables = {
'e'：（'unit1'，'unit2'，...），＃tablename：tuple的列名
} 
 
 def generate_conditions（table_name，nameValues）：
如果table_name不在表中：
 raise ValueError（'No such table％r'％table_name ）
 sql = u
 params = [] 
在nameValues中的字段：
如果字段不在表中[table_name]：
 raise ValueError列％r'％字段）
 sql + = uAND {0}。{1} =？。format（table_name，field）
 params.append（nameValues [field]）
 return sql，params 
 
 search_query = uSELECT * FROM Enheter e LEFT OUTER JOIN Handelser h ON e.Id == h.Enhet WHERE 1 = 1
 
 search_params = [] 
如果args中的Enhet：
 sql，params = generate_conditions（e，args [Enhet]）
 search_query + = sql 
 search_params.extend （params）
 c.execute（search_query，search_params）
  

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)
   

这篇关于转义动态sqlite查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！