[mapserver-users] Dynamin SQL with mapserver CGI?

[Steve Lime]

In addition, one can and should apply validation patterns to variables
used in SQL. That way MapServer
can test the input data before handing off to PostGIS or whatever.

These patterns are set in LAYER metadata. For example:

LAYER
  ...
  DATA"'the_geom FROM (SELECT the_geom FROM my_table WHERE ID = '%id%')
as foo USING UNIQUE OID"  
  METADATA
    id_validation_pattern '^[0-9]{3}$'
  END
END

This tells the CGI to test the request parameter against the regex
'^[1-9]{3}$'. The regex says that the value
of the id variable must be an number exactly 3 digits long with no
zeros. Any attempt to send a value other
than that will generate an error message. The CGI code does not force
the use of validation.

Validation like this will be further expanded in 5.4 with the addition
of a new validation object. So you'd see 
something like:

LAYER
  ...
  VALIDATION
    'id'  '^[0-9]{3}$
  END
END

Steve

>>> On 1/26/2009 at 8:47 AM, in message
<6246727221874A4FB8D3F9BBC37D9BD5056DA174 at s-sp22.pca.state.mn.us>,
"Fawcett,
David" <David.Fawcett at state.mn.us> wrote:
> Of course, part of security is also having your application hit your
database 
> as a user that only has the rights that it needs.  If your user only
has 
> select rights on only the data that you want to expose, that should
help 
> limit some of these issues.
> 
> 	-----Original Message-----
> 	From: mapserver-users-bounces at lists.osgeo.org 
> [mailto:mapserver-users-bounces at lists.osgeo.org] On Behalf Of 
> umn-ms at hydrotec.de 
> 	Sent: Monday, January 26, 2009 4:43 AM
> 	To: MapServer
> 	Subject: Re: [mapserver-users] Dynamin SQL with mapserver CGI?
> 	
> 	
> 
> 	> Not any great hazard, I believe, ... 
> 	
> 	Mmh. I'd be cautious. 
> 	
> 	Example: 
> 	* Mapfile: 
> 	   DATA "the_geom from buildings"
> 	
> 	* Set Filter via URL to this: 
> 	  1=1);DELETE FROM OTHERTABLE; DECLARE X BINARY CURSOR FOR
SELECT * from 
> buildings WHERE (1=1 
> 	
> 	I think Mapserver will create the following statements: (I've
added 
> newlines) 
> 	DECLARE mycursor BINARY CURSOR FOR SELECT the_geom from
buildings WHERE 
> (1=1); 
> 	DELETE FROM OTHERTABLE; 
> 	DECLARE X BINARY CURSOR FOR SELECT * from buildings WHERE (1=1)
and (%s && 
> setSRID( ...) ) 
> 	
> 	Mapserver calls PQExec with these statements. PQExec will
execute every 
> statement and will return 
> 	the results of the last one. 
> 	
> 	Bye 
> 	Benedikt Rothe 
> 	
> 	
> 	"Rahkonen Jukka" <Jukka.Rahkonen at mmmtike.fi> schrieb am
26.01.2009 09:34:31:
> 	
> 	> Hi, 
> 	>   
> 	> Not any great hazard, I believe, if it means that user can
normally 
> 	> get all the features, but only a subset when filter is set. 
It is 
> 	> different case if DATA clause is manipulated, and therefore
that 
> 	> must be connected to DATAPATTERN. 
> 	>   
> 	> -Jukka Rahkonen- 
> 	> 
> 	> Lähettäjä: mapserver-users-bounces at lists.osgeo.org [mailto:
> 	> mapserver-users-bounces at lists.osgeo.org] Puolesta
umn-ms at hydrotec.de 
> 	> Lähetetty: 26. tammikuuta 2009 10:03
> 	> Vastaanottaja: MapServer
> 	> Aihe: Re: [mapserver-users] Dynamin SQL with mapserver CGI?
> 	
> 	> 
> 	> Hi 
> 	> 
> 	> > You can use a replaceable parameter in the FILTER clause if
all you ... 
> 	> This introduces the hazard of SQL-Injection, doesn't it? 
> 	> 
> 	> Bye 
> 	> Benedikt Rothe 
> 	> 
> 	> mapserver-users-bounces at lists.osgeo.org schrieb am 24.01.2009
14:04:42:
> 	> 
> 	> > On Sat, Jan 24, 2009 at 3:18 AM, Saka Royban
<srph124 at yahoo.com> wrote:
> 	> > > Hi all
> 	> > > I'm looking for a way to change SQL dynamically via URL
parameters. it
> 	> > > sounds from doc that changing DATA element in map file is
impossible. Is
> 	> > > there any other way?
> 	> > 
> 	> > You can use a replaceable parameter in the FILTER clause if
all you
> 	> > want to do is alter the WHERE clause. So for example:
> 	> >    FILTER "%criteria%"
> 	> > and
> 	> >   criteria=id='value'
> 	> > would work with a database like Postgres.
> 	> > 
> 	> > When working with a database you put the whole SQL WHERE
clause in the
> 	> > FILTER, whereas with shapefiles or ORG data sources you use
the
> 	> > FILTERITEM and FILTER.
> 	> > 
> 	> > -- 
> 	> > Richard Greenwood
> 	> > richard.greenwood at gmail.com 
> 	> > www.greenwoodmap.com 
> 	> > _______________________________________________
> 	> > mapserver-users mailing list
> 	> > mapserver-users at lists.osgeo.org 
> 	> > http://lists.osgeo.org/mailman/listinfo/mapserver-users