Sqlite3. Substitution of names in query.

[Diez B. Roggisch]

Lacrima schrieb:
> Hello!
> 
> 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 
first place.

Die