如何将列名干净地传递到游标Python/SQLite中? [英] How do you cleanly pass column names into cursor, Python/SQLite?

问题描述

我是游标的新手，我正在尝试通过使用针对sqlite3的净化方法构建动态python sql插入语句来进行练习:

  import sqlite3conn = sqlite3.connect("db.sqlite")游标= conn.cursor()列表= ['column1'，'column2'，'column3'，'value1'，'value2'，'value3']cursor.execute("插入到table_name(?，?，?)值(?，?，?)"，列表) 

当我尝试使用它时，在带有值的行上收到语法错误"sqlite3.OperationalError:在?"附近.尽管有这样一个事实，当我对列进行硬编码(并从列表中删除列名称)时，我没有问题.我可以用％s进行构造，但我知道最好使用清理过的方法.

如何干净地插入这些?还是我缺少明显的东西?

解决方案

(?,?，?)语法仅适用于包含值的元组，恕我直言……这就是sqlite3.OperationalError的原因

我相信(！)应该建立与以下类似的东西:

  cursor.execute("INSERT INTO {tn}({f1}，{f2})VALUES(?，?)".format(tn ='testable'，f1 ='foo'，f1 ='bar')，('test'，'test2'，)) 

但是，如果允许用户自己提供表名或字段名，那么这不能解决注入问题.

我不知道有任何内置方法可以帮助解决此问题.但是您可以使用这样的函数:

  def clean(some_string):返回''.join(如果char.isalnum()中的char为some_string中的char) 

清除用户提供的表名或字段名.这应该足够了，因为表名/字段名通常仅由字母数字字符组成.

如果

，也许检查可能很聪明

  some_string == clean(some_string) 

如果为False，请放一个不错的例外，以确保安全.

在使用sql&的过程中python我觉得，虽然您不需要让用户自己命名他的表和字段名.因此，对我而言，这几乎是不必要的.

如果有人能进一步阐述并提供他的见解，我将不胜感激.

I'm new to cursors and I'm trying to practice by building a dynamic python sql insert statement using a sanitized method for sqlite3:

import sqlite3
conn = sqlite3.connect("db.sqlite")
cursor = conn.cursor()
list = ['column1', 'column2', 'column3', 'value1', 'value2', 'value3']
cursor.execute("""insert into table_name (?,?,?) 
values (?,?,?)""", list)

When I attempt to use this, I get a syntax error "sqlite3.OperationalError: near "?"" on the line with the values. This is despite the fact that when I hard code the columns (and remove the column names from the list), I have no problem. I could construct with %s but I know that the sanitized method is preferred.

How do I insert these cleanly? Or am I missing something obvious?

解决方案

The (?, ?, ?) syntax works only for the tuple containing the values, imho... That would be the reason for sqlite3.OperationalError

I believe(!) that you are ought to build it similar to that:

cursor.execute("INSERT INTO {tn} ({f1}, {f2}) VALUES (?, ?)".format(tn='testable', f1='foo', f1='bar'), ('test', 'test2',))

But this does not solve the injection problem, if the user is allowed to provide tablename or fieldnames himself, however.

I do not know any inbuilt method to help against that. But you could use a function like that:

def clean(some_string):
    return ''.join(char for char in some_string if char.isalnum())

to sanitize the usergiven tablename or fieldnames. This should suffice, because table-/fieldnames usually consists only of alphanumeric chars.

Perhaps it may be smart to check, if

some_string == clean(some_string)

And if False, drop a nice exception to be on the safe side.

During my work with sql & python I felt, that you wont need to let the user name his tables and fieldnames himself, though. So it is/was rarely necessary for me.

If anyone could elaborate some more and give his insights, I would greatly appreciate it.

这篇关于如何将列名干净地传递到游标Python/SQLite中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！