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 (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
