Python SQLite near "?": syntax error

Question

Another one of those questions. I'm trying to do:

self.table = 'table'
a = 'column'
b = 'value'

c.execute('INSERT INTO ? (?) VALUES (?)', (self.table, a, b))

But I'm getting

<class 'sqlite3.OperationalError'>:near "?": syntax error

At the same time,

c.execute('INSERT INTO {0} ({1}) VALUES ({2})'.format(self.table, a, b))

Works flawlessly, except for the security concerns.

I realize I'm not the first one to have this issue, but I have yet to find a solution that works for me.

As far as I know, ? is DB-API’s parameter substitution don't work for table name, see that post - http://stackoverflow.com/questions/3247183/variable-table-name-in-sqlite — Roman Pekar, Aug 10 '13 at 06:41

falsetru · Accepted Answer · 2013-08-10T07:12:33.863

8

Table names, column names cannot be parameterized. Try following instead.

self.table = 'table'
b = 'value'

c.execute('INSERT INTO {} ({}) VALUES (?)'.format(self.table, a), (b,))

edited Aug 10 '13 at 07:12

answered Aug 10 '13 at 06:41

falsetru

3

I do this all the time. You must be careful that self.table is a value that you have absolute control over, because a SQL injection attack is absolutely possible if you don't have tight controls on it. :) – Mark Roberts Aug 10 '13 at 06:46
This doesn't completely work, either. Apparently column names can't be parameterized, either. `c.execute('INSERT INTO {0} ({1}) VALUES (?)'.format(self.table, a), (b,))` works. Luckily I have full control over the column name as well. – cbrst Aug 10 '13 at 07:09
@crshd Unfortunately pretty much all DB drivers for any language and any DB do not support parameterization of table names or column names. As long as you are very careful about table names and column names not containing any sort of user input in any way, it's safe to do what you're doing. Just be cautious. – Anorov Aug 10 '13 at 15:51

1 Answers1