Josh Josh - 3 months ago 21
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

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

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)
c.execute(query)

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