Sqlite3. Substitution of names in query.
Diez B. Roggisch
deets at nospam.web.de
Fri Oct 30 13:27:48 CET 2009
> I use sqlite3 module for my sqlite database. I am trying to substitute
> table name in sql query.
>>>> import sqlite3
>>>> con = sqlite3.connect('mydb')
>>>> cur = con.execute("select * from table where name='Joe'")
> That's ok
>>>> cur = con.execute("select * from table where name=?", ('Joe',))
> That's ok too
>>>> cur = con.execute("select * from ? where name=?", ('table', 'Joe'))
> Traceback (most recent call last):
> File "<string>", line 1, in <fragment>
> sqlite3.OperationalError: near "?": syntax error
> So what's wrong now?
> Is it impossible to substitute table names, using DB API?
Yes, it is. How should the api discern that you meant the tabe-name
(which gets passed without quotes, or if so with double-quotes) instead
of a string-literal? There *could* be means to do so, but in the end all
this boils down to crossing a border that better is left alone - because
there are many problems of sql injection lurking if you are basing your
tablenames/columns on *input*.
> If so, what is a way to make table name substitutions? Are string
> operations like
> 'select * from %s where...' % tablename
> ok in this case?
By substituting it with simple string-interpolation. Or even better, by
not doing it at all - because usually, your datamodel is tied to your
program, so the need for this kind of dynamicity shouldn't arise in the
More information about the Python-list