Quantcast

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

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

William Temperley
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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Steve Atkins

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


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Rodrigo E. De León Plicet
In reply to this post by William Temperley
On Thu, Apr 3, 2008 at 11:50 AM, William Temperley
<[hidden email]> 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}');

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Adam Rich
In reply to this post by William Temperley

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



--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Richard Broersma
In reply to this post by William Temperley
On Thu, Apr 3, 2008 at 9:50 AM, William Temperley
<[hidden email]> 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.

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

brian-18
In reply to this post by William Temperley
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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

William Temperley
In reply to this post by William Temperley
Thanks for the replies,

"Rodrigo E. De León Plicet" <[hidden email]> 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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Tino Wildenhain
In reply to this post by Steve Atkins
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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Loading...