python - Inserting a table name into a query gives sqlite3.OperationalError: near "?": syntax error

Question

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 ?.

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)

question from:https://stackoverflow.com/questions/25387537/inserting-a-table-name-into-a-query-gives-sqlite3-operationalerror-near-sy

Answer 1

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

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.

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.

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.