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, [email protected]
wrote:
From: Dan Little <[email protected]>
Date: 27 June 2009 10:32:58 PM
To: Gabriel Messner <[email protected]>, [email protected]
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 <[email protected]>
To: [email protected]
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 <[email protected]>
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
[email protected]
Out here, it pays to
know...
_______________________________________________
mapserver-users mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
