Python 将表名插入查询会给出 sqlite3.OperationalError: near "?": syntax error
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25387537/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting a table name into a query gives sqlite3.OperationalError: near "?": syntax error
提问by Crytrus
I want to dynamically choose what table to use in a SQL query, but I just keep getting error however I am trying to format this. Also tried %sinstead of ?.
我想动态选择要在 SQL 查询中使用的表，但我只是不断收到错误，但是我正在尝试对其进行格式化。也试过%s代替?.
Any suggestions?
有什么建议？
group_food = (group, food)
group_food_new = (group, food, 1)
with con:
cur = con.cursor()
tmp = cur.execute("SELECT COUNT(Name) FROM (?) WHERE Name=?", group_food)
if tmp == 0:
cur.execute("INSERT INTO ? VALUES(?, ?)", group_food_new)
else:
times_before = cur.execute("SELECT Times FROM ? WHERE Name=?", group_food)
group_food_update = (group, (times_before +1), food)
cur.execute("UPDATE ? SET Times=? WHERE Name=?", group_food_update)
采纳答案by Martijn Pieters
You cannot use SQL parameters to be placeholders in SQL objects; one of the reasonsfor using a SQL parameters is to escape the value such that the database can never mistake the contents for a database object.
不能使用 SQL 参数作为 SQL 对象中的占位符；使用 SQL 参数的原因之一是对值进行转义，以便数据库永远不会将内容误认为数据库对象。
You'll have to interpolate the database objects separately; escape your identifiers by doubling any "double quote parameters and use
您必须单独插入数据库对象；通过将任何"双引号参数加倍来转义您的标识符并使用
cur.execute('SELECT COUNT(Name) FROM "{}" WHERE Name=?'.format(group.replace('"', '""')), (food,))
and
和
cur.execute('INSERT INTO "{}" VALUES(?, ?)'.format(group.replace('"', '""')), (food, 1))
and
和
cur.execute('UPDATE "{}" SET Times=? WHERE Name=?'.format(group.replace('"', '""')),
(times_before + 1, food))
The ".."double quotes are there to properly demark an identifier, even if that identifier is also a valid keyword; any existing "characters in the name must be doubled; this also helps de-fuse SQL injection attempts.
在".."双引号是有正确丹麦的标识，即使该标识也是一个有效的关键字; "名称中的任何现有字符都必须加倍；这也有助于消除 SQL 注入尝试。
However, if your object names are user-sourced, you'll have to do your own (stringent) validation on the object names to prevent SQL injection attacks here. Always validate them against existing objects in that case.
但是，如果您的对象名称来自用户，则您必须对对象名称进行自己的（严格）验证，以防止此处的 SQL 注入攻击。在这种情况下，始终对照现有对象验证它们。
You should really consider using a project like SQLAlchemyto generate your SQL instead; it can take care of validating object names and rigorously protect you from SQL injection risks. It can load your table definitions up frontso it'll know what names are legal:
你真的应该考虑使用像SQLAlchemy这样的项目来生成你的 SQL；它可以负责验证对象名称并严格保护您免受 SQL 注入风险。它可以预先加载您的表定义，以便知道哪些名称是合法的：
from sqlalchemy import create_engine, func, select, MetaData
engine = create_engine('sqlite:////path/to/database')
meta = MetaData()
meta.reflect(bind=engine)
conn = engine.connect()
group_table = meta.tables[group] # can only find existing tables
count_statement = select([func.count(group_table.c.Name)], group_table.c.Name == food)
count, = conn.execute(count_statement).fetchone()
if count:
# etc.
