5

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?

Cyrus Mohammadian
  • 4,982
  • 6
  • 33
  • 62
Josh
  • 95
  • 1
  • 2
  • 8

1 Answers1

10

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
mhawke
  • 84,695
  • 9
  • 117
  • 138
  • That did it. Thank you – Josh Aug 28 '16 at 22:57
  • I think I did that. The program first gets a list of names of the tables and displays them. Then the user has to input one of those names. If the input doesn't match a name on the list the program goes back to the list of table names and asks for input again. – Josh Aug 28 '16 at 23:02
  • @Josh: that should be safe. I've also added to my answer a way to verify the user entered table name against SQLite's master table. – mhawke Aug 28 '16 at 23:56
  • hey, does sb know how I can verify the input if it comes from an user and the table doesn't exists? Because I have a function to create a table – raphiel Jun 04 '21 at 11:32