将表名插入查询会给出 sqlite3.OperationalError: near "?": syntax error [英] Inserting a table name into a query gives sqlite3.OperationalError: near "?": syntax error

问题描述

我想动态选择要在 SQL 查询中使用的表，但我一直收到错误，但是我正在尝试对其进行格式化.还尝试了 %s 而不是 ?.

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 %s instead of ?.

有什么建议吗?

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)

推荐答案

不能使用 SQL 参数作为 SQL 对象中的占位符；使用 SQL 参数的原因之一是对值进行转义，以便数据库永远不会将内容误认为数据库对象.

You cannot use SQL parameters to be placeholders in SQL objects; one of the reasons for using a SQL parameters is to escape the value such that the database can never mistake the contents for a database object.

您必须单独插入数据库对象；通过将任何 " 双引号参数加倍来转义您的标识符并使​​用

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,))

和

cur.execute('INSERT INTO "{}" VALUES(?, ?)'.format(group.replace('"', '""')), (food, 1))

和

cur.execute('UPDATE "{}" SET Times=? WHERE Name=?'.format(group.replace('"', '""')),
            (times_before + 1, food))

".." 双引号用于正确区分标识符，即使该标识符也是有效关键字；名称中任何现有的 " 字符都必须加倍；这也有助于消除 SQL 注入尝试.

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.

您真的应该考虑使用像 SQLAlchemy 这样的项目来生成您的 SQL；它可以负责验证对象名称并严格保护您免受 SQL 注入风险.它可以预先加载您的表定义 这样它就会知道哪些名称是合法的:

You should really consider using a project like SQLAlchemy to 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 front so it'll know what names are legal:

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.

这篇关于将表名插入查询会给出 sqlite3.OperationalError: near "?": syntax error的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！