how to replace and string in a "SELECT ... IN ()"

[Paul Boddie]

On 26 Sep, 12:15, Wojtek Walczak <gmin... at bzt.bzt> wrote:
> On Fri, 26 Sep 2008 02:32:50 -0700 (PDT), bcurtu wrote:
> > I have a BIIIIIG problem with the next query:
>
> >         cursor.execute("""
> >                     SELECT titem.object_id, titem.tag_id
> >                     FROM tagging_taggeditem titem
> >                     WHERE titem.object_id IN (%s)
> >                 """,( eid_list))
>
>                      ^
> It should rather be '%'.

You're telling the inquirer to do string substitution which can be
dangerous if eid_list is built, say, from a collection of strings
taken from an untrusted source.

Sadly, SQL parameter substitution, which is done using the syntax
employed by the inquirer above (along with the unfortunate "%s"
placeholder syntax), does not really deal with sequences of values
very well. What needs to be done here, if everything should happen
relatively safely, is that the query string should be made to contain
the appropriate number of placeholders between the brackets, with
commas separating them as demanded by the syntax of SQL. Then, the
values should be correctly taken from eid_list by the execute method,
although for portability between different database modules, whose
authors seem to have differing views on what kind of object can be
given containing the parameters, I'd recommend converting eid_list to
a tuple.

Bruno and Tino thrash out some kind of working solution, I think.

Paul