[mapserver-users] Spatial queries to Mapserver

[Ben Madin]

Thanks Dan,

Could you elaborate on this - there are a username and password on the  
database layer for select only for web user, but I scrubbed them from  
my post - which was remiss of me but I was in a hurry.

However, is there a way in MapServer to "clean" the parameters that I  
have used - I could imagine a startdate like 12';DELETE from  
movements; is what you mean by sql injection?

cheers

Ben




On 27/06/2009, at 10:39 PM, mapserver-users-request at lists.osgeo.org  
wrote:

> From: Dan Little <danlittle at yahoo.com>
> Date: 27 June 2009 10:32:58 PM
> To: Gabriel Messner <gabmessner at gmail.com>, mapserver-users at lists.osgeo.org
> Subject: Re: [mapserver-users] Spatial queries to Mapserver
>
>
> Make sure your user has limited table access.  This example would  
> allow for SQL injection.
>
> From: Gabriel Messner <gabmessner at gmail.com>
> To: mapserver-users at lists.osgeo.org
> Sent: Friday, June 26, 2009 6:51:43 AM
> Subject: Re: [mapserver-users] Spatial queries to Mapserver
>
> Thanks, I´ll digest it :-)
>
>
> 2009/6/26 Ben Madin <ben at remoteinformation.com.au>
> Gabriel,
>
> You need to add a layer with your query in it - you can use variable  
> substitution etc.
>
> an example (may or may not be a good one!) :
>
>     LAYER
>         DEBUG 3
>         NAME "buffalo"
>         GROUP "Movement"
>         METADATA
>             layer_title "Buffalo"
>             layer_order "2"
>         END
>         CONNECTIONTYPE postgis
>         CONNECTION "host=localhost port=5432 dbname=prices"
> 		PROCESSING "CLOSE_CONNECTION=DEFER"
>         DATA "route FROM (
>                 SELECT s.id, s.commodity, s.shortname, s.amount,  
> s.purpose, s.origprov, s.destprov, ST_MakeLine(s.startpoint,  
> s.endpoint) as route, to_char(s.origindate,'DD/MM/YYYY') as date
>                 FROM (
>                     SELECT m.id, c.commodity, sp.shortname,  
> c.amount, r.name AS purpose,
>                     		initcap(o.name) as origprov, initcap(d.name)  
> as destprov,
>                     		ST_Centroid(o.geom) as startpoint,  
> ST_Centroid(d.geom) as endpoint, origindate
>                     FROM movements m
>                     LEFT OUTER JOIN locations o
>                     ON m.originprovince = o.hiercode
>     	                AND o.del = 0
> 	                    AND o.level = 2
>                     LEFT OUTER JOIN locations d
>                     ON m.destinationprovince = d.hiercode
>                     	AND d.del = 0
>                     	AND d.level = 2
>                     JOIN commodities c
>                     ON m.id = c.movementid
>                     	AND c.del = 0
>       	              AND c.commodity = 2
>         			LEFT OUTER JOIN species sp
>         			ON c.commodity = sp.id
>         				AND sp.del = 0
>         			LEFT OUTER JOIN reasons r
>         			ON c.purpose::int = r.id
>         				AND r.del = 0
>                     WHERE m.del = 0
> 						AND originprovince is not null
> 						AND originprovince not like '0'
> 						AND destinationprovince is not null
> 						AND destinationprovince not like '0'
>                 ) AS s
>                 WHERE s.origindate > '%start%'
>         	        AND s.origindate < '%end%'
>                 ORDER BY s.origindate
>         ) AS foo USING UNIQUE id USING srid=4326"
>         TYPE LINE
>         STATUS ON
>         HEADER 'templates/trade_header.html'
>         FOOTER 'templates/trade_footer.html'
>         CLASSITEM commodity
>         CLASS
>             NAME "Buffalo"
>             EXPRESSION "2"
>             STYLE
> 	            SYMBOL "arrow1"
> 	            OFFSET 0 0
>     	        SIZE 12
>         	    COLOR 0 0 255
>         	END
>             TEMPLATE 'templates/trade_query.html'
>         END
>     END
>
>

-- 

Ben Madin
REMOTE INFORMATION

t : +61 8 9192 5455
f : +61 8 9192 5535
m : 0448 887 220
Broome   WA   6725

ben at remoteinformation.com.au



							Out here, it pays to know...


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20090628/d307e669/attachment.htm>