<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Thanks Dan,<div><br></div><div>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.</div><div><br></div><div>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?</div><div><br></div><div>cheers</div><div><br></div><div>Ben</div><div><br></div><div><br></div><div><br></div><div><br></div><div><div><div>On 27/06/2009, at 10:39 PM, <a href="mailto:mapserver-users-request@lists.osgeo.org">mapserver-users-request@lists.osgeo.org</a> wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: medium; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; "><span class="Apple-style-span" style="font-family: 'times new roman', 'new york', times, fantasy; font-size: 16px; "><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span style="font-family: Helvetica; font-size: medium; color: rgba(0, 0, 0, 0.496094); "><b>From:<span class="Apple-converted-space"> </span></b></span><span style="font-family: Helvetica; font-size: medium; ">Dan Little <<a href="mailto:danlittle@yahoo.com">danlittle@yahoo.com</a>><br></span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span style="font-family: Helvetica; font-size: medium; color: rgba(0, 0, 0, 0.496094); "><b>Date:<span class="Apple-converted-space"> </span></b></span><span style="font-family: Helvetica; font-size: medium; ">27 June 2009 10:32:58 PM<br></span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span style="font-family: Helvetica; font-size: medium; color: rgba(0, 0, 0, 0.496094); "><b>To:<span class="Apple-converted-space"> </span></b></span><span style="font-family: Helvetica; font-size: medium; ">Gabriel Messner <<a href="mailto:gabmessner@gmail.com">gabmessner@gmail.com</a>>,<span class="Apple-converted-space"> </span><a href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a><br></span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span style="font-family: Helvetica; font-size: medium; color: rgba(0, 0, 0, 0.496094); "><b>Subject:<span class="Apple-converted-space"> </span></b></span><span style="font-family: Helvetica; font-size: medium; "><b>Re: [mapserver-users] Spatial queries to Mapserver</b><br></span></div><br><br><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font-family: arial, helvetica, sans-serif; font-size: 10pt; "><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Make sure your user has limited table access. This example would allow for SQL injection.</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font-family: arial, helvetica, sans-serif; font-size: 10pt; "><br><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font-family: 'times new roman', 'new york', times, serif; font-size: 12pt; "><font size="2" face="Tahoma"><hr size="1"><b><span style="font-weight: bold; ">From:</span></b><span class="Apple-converted-space"> </span>Gabriel Messner <<a href="mailto:gabmessner@gmail.com">gabmessner@gmail.com</a>><br><b><span style="font-weight: bold; ">To:</span></b><span class="Apple-converted-space"> </span><a href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a><br><b><span style="font-weight: bold; ">Sent:</span></b><span class="Apple-converted-space"> </span>Friday, June 26, 2009 6:51:43 AM<br><b><span style="font-weight: bold; ">Subject:</span></b><span class="Apple-converted-space"> </span>Re: [mapserver-users] Spatial queries to Mapserver<br></font><br>Thanks, IŽll digest it :-)<br><br><br><div class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">2009/6/26 Ben Madin<span class="Apple-converted-space"> </span><span dir="ltr"><<a rel="nofollow" ymailto="mailto:ben@remoteinformation.com.au" target="_blank" href="mailto:ben@remoteinformation.com.au">ben@remoteinformation.com.au</a>></span><br><blockquote class="gmail_quote" style="border-left-width: 1px; border-left-style: solid; border-left-color: rgb(204, 204, 204); margin-top: 0pt; margin-right: 0pt; margin-bottom: 0pt; margin-left: 0.8ex; padding-left: 1ex; "><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; word-wrap: break-word; ">Gabriel,<div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">You need to add a layer with your query in it - you can use variable substitution etc.</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">an example (may or may not be a good one!) :</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> LAYER</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> DEBUG 3</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> NAME "buffalo"</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> GROUP "Movement"</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> METADATA</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> layer_title "Buffalo"</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> layer_order "2"</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> END</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> CONNECTIONTYPE postgis</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> CONNECTION "host=localhost port=5432 dbname=prices"</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span style="white-space: pre; "> </span>PROCESSING "CLOSE_CONNECTION=DEFER"</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> DATA "route FROM (</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> SELECT<span class="Apple-converted-space"> </span><a rel="nofollow" target="_blank" href="http://s.id">s.id</a>, 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</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> FROM (</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> SELECT<span class="Apple-converted-space"> </span><a rel="nofollow" target="_blank" href="http://m.id">m.id</a>, c.commodity, sp.shortname, c.amount,<span class="Apple-converted-space"> </span><a rel="nofollow" target="_blank" href="http://r.name">r.name</a><span class="Apple-converted-space"> </span>AS purpose, </div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span>initcap(<a rel="nofollow" target="_blank" href="http://o.name">o.name</a>) as origprov, initcap(<a rel="nofollow" target="_blank" href="http://d.name">d.name</a>) as destprov,</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span>ST_Centroid(o.geom) as startpoint, ST_Centroid(d.geom) as endpoint, origindate</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> FROM movements m</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> LEFT OUTER JOIN locations o</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> ON m.originprovince = o.hiercode</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span><span class="Apple-converted-space"> </span> AND o.del = 0</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span style="white-space: pre; "> </span><span class="Apple-converted-space"> </span> AND o.level = 2</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> LEFT OUTER JOIN locations d</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> ON m.destinationprovince = d.hiercode</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span>AND d.del = 0</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span>AND d.level = 2</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> JOIN commodities c</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> ON<span class="Apple-converted-space"> </span><a rel="nofollow" target="_blank" href="http://m.id">m.id</a><span class="Apple-converted-space"> </span>= c.movementid</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span>AND c.del = 0</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span><span class="Apple-converted-space"> </span> AND c.commodity = 2</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span>LEFT OUTER JOIN species sp</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span>ON c.commodity =<span class="Apple-converted-space"> </span><a rel="nofollow" target="_blank" href="http://sp.id">sp.id</a></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span>AND sp.del = 0</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span>LEFT OUTER JOIN reasons r</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span>ON c.purpose::int =<span class="Apple-converted-space"> </span><a rel="nofollow" target="_blank" href="http://r.id">r.id</a></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span>AND r.del = 0</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> WHERE m.del = 0</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span style="white-space: pre; "> </span>AND originprovince is not null</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span style="white-space: pre; "> </span>AND originprovince not like '0'</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span style="white-space: pre; "> </span>AND destinationprovince is not null</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span style="white-space: pre; "> </span>AND destinationprovince not like '0'</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> ) AS s</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> WHERE s.origindate > '%start%'</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span><span class="Apple-converted-space"> </span> AND s.origindate < '%end%'</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> ORDER BY s.origindate</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> ) AS foo USING UNIQUE id USING srid=4326"</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> TYPE LINE</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> STATUS ON</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> HEADER 'templates/trade_header.html'</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> FOOTER 'templates/trade_footer.html'</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> CLASSITEM commodity</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> CLASS</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> NAME "Buffalo"</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> EXPRESSION "2"</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> STYLE</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span style="white-space: pre; "> </span><span class="Apple-converted-space"> </span> SYMBOL "arrow1"</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span style="white-space: pre; "> </span><span class="Apple-converted-space"> </span> OFFSET 0 0 </div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span><span class="Apple-converted-space"> </span> SIZE 12</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span><span class="Apple-converted-space"> </span> COLOR 0 0 255</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> <span style="white-space: pre; "> </span>END</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> TEMPLATE 'templates/trade_query.html'</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> END</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "> END</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><br></div><br class="Apple-interchange-newline"></div></div></blockquote></div></div></div></div></span></span></blockquote></div><br><div apple-content-edited="true"> <span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: auto; -khtml-text-decorations-in-effect: none; text-indent: 0px; -apple-text-size-adjust: auto; text-transform: none; orphans: 2; white-space: normal; widows: 2; word-spacing: 0px; "><div style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; "><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">-- </div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Ben Madin</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">REMOTE INFORMATION</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">t : +61 8 9192 5455</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">f : +61 8 9192 5535</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">m : 0448 887 220</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Broome WA 6725</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><a href="mailto:ben@remoteinformation.com.au">ben@remoteinformation.com.au</a></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span class="Apple-tab-span" style="white-space:pre"><span class="Apple-style-span" style="white-space: pre; "> </span></span>Out here, it pays to know...</div></div><br class="Apple-interchange-newline"></span> </div><br></div></body></html>