sqlite3 python parameter passing issue

Question

I have this code:

conn = sqlite3.connect("testDB")
curs = conn.cursor()
curs.execute("CREATE TABLE IF NOT EXISTS testTable( col1 VARCHAR, col2 VARCHAR)")
c1 = "value1"
c2 = "value2"
curs.execute("INSERT INTO testTable VALUES (?,?)", (c1, c2))#this works fine
conn.commit()
def inDB(curs, table, col, value):
 curs.execute("SELECT * FROM ? WHERE ?=?",(table, col, value)) #problemis here
 return bool(curs.fetchone())
print inDB(curs, "testTable", "col1", c1)

It gives me this error:

Traceback (most recent call last):
 File "test.py", line 16, in <module>
 print inDB(curs, "testTable", "col1", c1)
 File "test.py", line 13, in inDB
 curs.execute("SELECT * FROM ? WHERE ?=?",(table, col, value))
sqlite3.OperationalError: near "?": syntax error

Why this does not work and how can I fix this?

Answer 1

I not sure, but I don't think you can use bind variables on table or column names...

Try

curs.execute("INSERT INTO ? VALUES (?,?)", (testTable, c1, c2))

I bet it won't work. In this case, you need to avoid using ? on table or column names. I'd rather use concatenation operator to join the string like in Java it is "+". For example (Java style):

("INSERT INTO " + testTable + " VALUES (?,?)");

• torayeff: If you use string concatenation isn't it vulnerable for SQLi if I use code for web application?
• Slimer: well, might be...but I don't know better idea when you want to dynamically change the table name...If you fear SQL injection, you can create multiple functions with fixed table name something like: void addToTable1(value1, value2) { ("INSERT INTO table1 VALUES (?,?)", (value1, value2)); } void addToTable2(value1, value2) { ("INSERT INTO table2 VALUES (?,?)", (value1, value2)); }
• Slimer: Anyway, how can be table name injected...Are you passing table name as argument from user input? The table name supposed to be internal thing. Only application itself should know the table names and only application itself should set the parameter (in your case) testTable from some known list.
• User: If you want to protect the table "parameter" that you are concatenating from sql injection I believe the standard way is to only allow values from a fixed list of allowable table names.

Answer 2

This is all I could extract from the docs:

http://www.sqlite.org/cintro.html

3.0 Binding Parameters and Reusing Prepared Statements

In SQLite, wherever it is valid to include a string literal, one can use a parameter in one of the following forms:

http://www.sqlite.org/c3ref/bind_blob.html

In the SQL statement text input to sqlite3_prepare_v2() and its variants, literals may be replaced by a parameter that matches one of following templates:

http://www.sqlite.org/lang_insert.html

SQLite INSERT

You probably can't use a parameterised table name because [database-name.]table-name is not an ordinary literal value (that's why you can't write a statement like INSERT INTO 'foo'||'bar'||12 VALUES ...).

Or maybe it is something entirely different. :)