Manual parameter substitution in sqlite3

[Peter Otten]

Skip Montanaro wrote:

> Most of the time (well, all the time if you're smart), you let the
> database adapter do parameter substitution for you to avoid SQL
> injection attacks (or stupid users). So:
> 
>     curs.execute("select * from mumble where key = ?", (key,))
> 
> If you want to select from several possible keys, it would be nice to
> be able to do this:
> 
>     curs.execute("select * from mumble where key in (?)", (keys,))
> 
> but that doesn't work. Instead, you need to do your own parameter
> substitution. The quick-and-insecure way to do this is:
> 
>     curs.execute("select * from mumble where key in (%s)" %
>                         ",".join([repr(k) for k in keys]))
> 
> I'm pretty sure that's breakable.
> 
> Some database adapters provide a function to do explicit substitution
> (e.g., mySQLdb.escape, psycopg2._param_escape), but the sqlite3
> adapter doesn't. Is there a function floating around out there which
> does the right thing, allowing you to safely construct these sorts of
> set inclusion clauses?
> 
> Thx,
> 
> Skip

If all else fails use quote()

http://www.sqlite.org/lang_corefunc.html#quote

>>> db = sqlite3.connect(":memory:")
>>> cs = db.cursor()
>>> next(cs.execute("select quote(?)", ("foo 'bar' \"baz\"",)))[0]
'\'foo \'\'bar\'\' "baz"\''
>>> print(_)
'foo ''bar'' "baz"'

With the documented limitation:

>>> next(cs.execute("select quote(?)", ("foo 'bar'\0 \"baz\"",)))[0]
"'foo ''bar'''"