Manual parameter substitution in sqlite3

[Chris Angelico]

On Wed, Mar 1, 2017 at 4:28 AM, Skip Montanaro <skip.montanaro at 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