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 - stackoverflow.com/questions/3247183/… — roman
– roman, Commented Aug 10, 2013 at 6:41

falsetru · Accepted Answer · 2013-08-10 07:12:33Z

7

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, 2013 at 7:12

answered Aug 10, 2013 at 6:41

falsetru

371k69 gold badges769 silver badges659 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mark Roberts Over a year ago

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. :)

cbrst Over a year ago

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.

Anorov Over a year ago

@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.

Collectives™ on Stack Overflow

Python SQLite near "?": syntax error

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related