Josh Josh - 1 year ago 137
Python Question

How to use variable for SQLite table name

I have a program where the user can select what table they want to modify in SQLite. I store the selection in a variable called

, then try and select everything from that table

c.execute('SELECT * FROM ?', (table,))

The program gets stuck at the question mark. It says:

"Sqlite3.OperationalError: near "?": syntax error"

What am I doing wrong?

Answer Source

You can't use parameter substitution for the table name. You need to add the table name to the query string yourself. Something like this:

query = 'SELECT * FROM {}'.format(table)

One thing to be mindful of is the source of the value for the table name. If that comes from an untrusted source, e.g. a user, then you need to validate the table name to avoid potential SQL injection attacks. One way might be to construct a parameterised query that looks up the table name from the DB catalogue:

import sqlite3

def exists_table(db, name):
    query = "SELECT 1 FROM sqlite_master WHERE type='table' and name = ?"
    return db.execute(query, (name,)).fetchone() is not None