Manual parameter substitution in sqlite3
Skip Montanaro
skip.montanaro at gmail.com
Tue Feb 28 12:28:14 EST 2017
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
More information about the Python-list
mailing list