On Wed, Mar 1, 2017 at 4:28 AM, Skip Montanaro <skip.montan...@gmail.com> wrote:
> 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?
Testing with PostgreSQL (which *does* transform lists) suggests that
"in" doesn't work; I used "key = any(%s)". I'd try that with sqlite3
first, just in case it makes a difference. Probably it won't, but
worth a try.
Second recommendation: Switch to PostgreSQL, because then this happens
automatically :)
Third recommendation: Instead of making yourself completely
vulnerable, just go one level in:
curs.execute("select * from mumble where key in (" +
",".join(["?"]*len(keys)) + ")", keys)
If this is combined with another parameter, it'd be messier, but you
could do something like:
curs.execute("select * from mumble where key in (" +
",".join(["?"]*len(keys)) + ") and category = ?",
tuple(keys) + (cat,))
Either way, you're still letting the sqlite connector do the
processing of the elements, but handling the collection yourself.
I may or may not have needed to do this once before.... with MySQL....
but I'm not going to admit to it. Because I also had to use PHP to
talk to the same database. And I don't admit to knowing PHP.
ChrisA
--
https://mail.python.org/mailman/listinfo/python-list
