Re: Secure "where in(a,b,c)" clause.

On Apr 3, 2008, at 9:50 AM, William Temperley wrote:
> Hi All
>
> I hope this isn't a FAQ, but does anyone have any suggestions as to
> how to make a query that selects using:
> "where in(<comma delimited list>)"
> secure from an sql injection point of view?
>
> I have grid of tiles I'm using to reference geographical points.
> These tiles are identical to the tiling system google maps uses. My
> google maps application works out the tiles it wants to display as a
> list of tile names, and sends this list to a php script.
>
> This works very well, however I'm currently directly concatenating a
> sql query:
>
> select st_collect(the_geom) from tiles where tilename in
> (<comma delimited list>))
>
> Which leaves my application vulnerable to sql injection.
>
> As the length of the comma delimited list is highly variable I don't
> think I can use a prepared query to increase security.

I count the number of values that I want to put in the IN () clause,
then create a query string with the right number of bind variables
in the in clause, then bind the values.

So for {1, 3, 5} I'd use "select * from foo where bar in (?, ?, ?)"
and for
{1,5,7,9,11} I'd use "select * from foo where bar in (?, ?, ?, ?, ?)"

Then, in perl-speak, I prepare that string into a query, loop through
all my values and bind them one by one, then execute the query.

Cheers,
Steve