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

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.

Thanks

Will

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

On Thu, Apr 3, 2008 at 11:50 AM, William Temperley
<willtemperley@gmail.com> wrote:
>  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.

Use a prepared query and ANY, e.g.:

select st_collect(the_geom) from tiles
where tilename = any('{foo,bar,baz}');

On Thu, Apr 3, 2008 at 9:50 AM, William Temperley
<willtemperley@gmail.com> 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 an idea, but I can't comment if it is a good idea since I
haven't tried it.

Maybe you can create a temp table for each user, insert the values you
want into the table, and lastly perform a join on your foo table with
the user's temp table.  This hopefully would leave anything open for
injection.

When you are done just drop the temp table.

--
Regards,
Richard Broersma Jr.

> 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?
>
> As the length of the comma delimited list is highly
> variable I don't
> think I can use a prepared query to increase
> security.
>

Prepared query, no.. but you can still use parameter
binding.  Determine how many parameters you need, and
create a query like this:

where in ($1, $2, $3, $4, $5)

and then bind each of those parameters.  This works
well enough for small numbesr of parameters.  Somebody
else will have to answer if there's a better way for
larger quantities.

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.
>

Aside from using a prepared statement, your application code can simply
ensure that each named tile follows whatever naming conventions you have
in place. A very basic regex should do.

b

Thanks for the replies,

"Rodrigo E. De León Plicet" <rdeleonp@gmail.com> wrote:
>Use a prepared query and ANY, e.g.:
>select st_collect(the_geom) from tiles
>where tilename = any('{foo,bar,baz}');

Thanks, that's what I was looking for!
$sql = "select uid, accredited as acc, x(the_geom), y(the_geom) from clubs
where st_within(the_geom, (select st_collect(the_geom) from tiles
where tilename = any($1)))";

$result = pg_query_params($sql, array('{'.$tilearr.'}'));

Though a regex would do as well I guess.

Cheers

Will

Steve Atkins wrote:
...
> 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.

You mean something like:

items=(1,2,5,6,9)

cursor.execute("SELECT ... FROM foo where bar in (%s)" %
','.join('?'*len(items)),items)

? :-)

Oh.. I forgot he said PHP...

SCNR
Tino