SQL SELECT from table name supplied by a variable

Question

Whenever I try the code below, I get near "?": syntax error I tried multiple things including prepping it into a variable

Is this possible in python? Or am I thinking in the wrong direction?

import sqlite3
sqlite_file = 'DATABASE.db'
conn = sqlite3.connect(sqlite_file)
c = conn.cursor()
word = ()
question = int(input("What would you like to see?"))
if question == 1:
    word = ("Batchnumbers")
if question == 2:
    word = ("Worker IDs")
c.execute('SELECT * FROM ?', (word))
    data = c.fetchall()
    for x in range(len(data)):
        print(data[x])

which database package are you using because you can't just insert the table name into the query like that, like you could other params — eagle, Apr 16 '18 at 15:37
afaik there is no option to safely execute a SQL query with the table name being the param in `sqlite3`, using string interpolation is also unsafe, check here as they give examples --> https://stackoverflow.com/questions/3247183/variable-table-name-in-sqlite — eagle, Apr 16 '18 at 15:45

Gord Thompson · Answer 1 · 2018-04-16T15:53:46.140

4

Query parameters can only be used to supply column values, not table or column names. If you need to supply a table name you will have to use dynamic SQL, e.g.,

c.execute('SELECT * FROM "{}"'.format(word))

Note that this approach is vulnerable to SQL injection issues, so you really should consider mitigating those, e.g., ensuring that word does not contain double-quote characters that would cause errors (or worse).

edited Apr 16 '18 at 15:53

answered Apr 16 '18 at 15:50

Gord Thompson

116,920
32
215
418

Thanks, working like a charm! totally forgot about that!(while I was using it in other queries... _damn_) – DevOps Apr 16 '18 at 15:52

score 2 · Accepted Answer · answered Apr 23 '18 at 21:04

2

Indeed use this line of code word =

c.execute('SELECT * FROM "{}"'.format(word))

answered Apr 23 '18 at 21:04

LanaReversed

38
5

SQL SELECT from table name supplied by a variable

2 Answers2