Re: [GENERAL] Secure where in(a,b,c) clause.
Thanks for the replies,
Rodrigo E. De León Plicet [EMAIL PROTECTED] 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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Secure where in(a,b,c) clause.
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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Secure where in(a,b,c) clause.
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 (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Secure where in(a,b,c) clause.
On Thu, Apr 3, 2008 at 11:50 AM, William Temperley
[EMAIL PROTECTED] 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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Secure where in(a,b,c) clause.
On Thu, Apr 3, 2008 at 9:50 AM, William Temperley [EMAIL PROTECTED] 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 (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Secure where in(a,b,c) clause.
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 (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
