[mapserver-users] Variable substitution error with non-ASCII characters

[Rahkonen Jukka]

Hi,

I am preparing an on-line demonstration about how to make queries against PostgreSQL/PostGIS key-value store with MapServer and I fear it would be too hard for me and possible readers to use UTF-8 encoding with their browsers.  I think I will just tell that non-ASCII characters cannot be used at the moment. When it comes to risk of SQL injection I must just take it because I have not found another way to do what I want yet. I hope that having a separate PostGIS user with just select rights is limiting the risk. The data on my server is free so I do not need to protect it. But of course unkind people can easily construct very heavy queries and block my service.

What I am playing with is to have OpenSteetMap data in PostGIS so that all the OSM tags are stored into one single column which is of PostgrSQL hstore datatype. It is a key-value store and thus a kind of a NoSQL thing. More info about hstore is at http://www.postgresql.org/docs/9.0/static/hstore.html

I notised that MapServer allows to utilise this key-value store without a fixed schema pretty nice through WMS and WFS services, even OGC definitely did not define them to support NoSQL. However, making queries against hstore attribute (the key-value store) is not possible with standard SLD or OGC Filter encoding.  Making SQL queries from hstore is somewhat tricky but I have been reading this document and managed to do working queries http://rubydoc.info/gems/activerecord-postgres-hstore/0.1.0/frames

OpenStreetMap data from Finland contain right now totally 3333 different keys http://tagwatch.stoecker.eu/Finland/En/tags.html and with variable substitution I can query just everything with Mapserver. I can make selection and show a map with WMS and next I can do the same selection and download data with WFS. I have a feeling that Mapserver is one of the best tools that exist for making this kind of Query-Visualize-Download stuff with OpenStreetMap data or any other data having attributes as unstructured key-value pairs. Even the OSM project itseld does not have such a service at the moment. But Mapserver cannot deliver vector data in OSM.xml format so it is a different thing for some people. If question is just about using the data then GML should work as well.

Perhaps developers will get interested in making some more secure way for building hstore queries. One alternative could be to make Mapserver to recognise hstore column and make OGC PropertyIsLike queries to lead into suitable SQL. Next should be a working live example about selecting tags highway=bus_stop (or anything else by editing the &sql parameter) from OSM point data, showing them on a map and later downloading the same data with WFS. ANDs and ORs are naturally supported as well as BBOX and other OGC spatial filters.

Show a map
188.64.1.61/cgi-bin/ms_ows?REQUEST=GetMap&SERVICE=WMS&VERSION=1.1.1&WIDTH=563&HEIGHT=437&LAYERS=sea,borders,osm_pisteet&TRANSPARENT=TRUE&FORMAT=image/png&BBOX=-369151.98300283286,6597900.0,1511076.628895184,8057331.444759207&SRS=EPSG:3067&STYLES=&sql=(tags @>'highway=>bus_stop')


Get the data (ten first features with this query)
188.64.1.61/cgi-bin/ms_ows?service=wfs&version=1.0.0&request=getfeature&typename=osm_pisteet&sql=(tags @>'highway=>bus_stop')&maxFeatures=10

-Jukka Rahkonen-




Even Rouault wrote:

>
> Selon Rahkonen Jukka <Jukka.Rahkonen at mmmtike.fi>:
>
> Did you check the encoding of your 'ä' character ? I would
> think that if it is
> sent in UTF-8 it should work.
>
> I'd also warn you that unless you've defined a very strict
> validation pattern,
> such use of substitution is potentially dangerous because it
> leaves the door
> wide open to SQL injections.
>
> > Hi,
> >
> > I have been playing with variable substitution inside the
> DATA part of my
> > mapfile. I have defined variable %sql% which is tranferred
> inside a WHERE
> > part of the SQL selecting data for me. The next with
> &sql=highway='bus_stop'
> > works fine.
> >
> >
> http://188.64.1.61/cgi-bin/ms_ows?REQUEST=GetMap&SERVICE=WMS&V <http://188.64.1.61/cgi-bin/ms_ows?REQUEST=GetMap&SERVICE=WMS&V>
ERSION=1.1.1&WIDTH=563&HEIGHT=437> &LAYERS=osm_pisteet&TRANSPARENT=TRUE&FORMAT=image/png&BBOX=-36
> 9151.98300283286,6597900.0,1511076.628895184,8057331.444759207
> &SRS=EPSG:3067&STYLES=&sql=highway='bus_stop'
> >
> > However, if the variable contains non-ASCII characters the
> queries will fail.
> > For example &sql=highway='ä'
> > gives the following error.
> >
> >
> > <?xml version='1.0' encoding="ISO-8859-1" standalone="no" ?>
> > <!DOCTYPE ServiceExceptionReport SYSTEM
> > "http://schemas.opengis.net/wms/1.1.1/exception_1_1_1.dtd <http://schemas.opengis.net/wms/1.1.1/exception_1_1_1.dtd> ">
> > <ServiceExceptionReport version="1.1.1">
> > <ServiceException>
> > msDrawMap(): Image handling error. Failed to draw layer named
> > 'osm_pisteet'.
> > msPostGISLayerWhichShapes(): Query error. Error (ERROR:
> invalid byte
> > sequence for encoding "UTF8": 0xe42729
> > ) executing query: select
> >
> encode(ST_AsBinary(ST_Force_2D("way"),'NDR')
> ,'hex')
> > as geom,"osm_id" from (select (way),osm_id,tags
> from osm_point
> > where highway='ä') as foo where way &&
> > GeomFromText('POLYGON((-367482.152974504
> > 6599569.83002833,-367482.152974504 8055661.61473088,1509406.79886686
> > 8055661.61473088,1509406.79886686 6599569.83002833,-367482.152974504
> > 6599569.83002833))',3067)
> > </ServiceException>
> > </ServiceExceptionReport>
> >
> > Is there any known way to solve the invalid byte sequence
> error for example
> > by escaping the none-ASCII characters in some way?
> >
> > -Jukka Rahkonen-_______________________________________________
> > mapserver-users mailing list
> > mapserver-users at lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/mapserver-users <http://lists.osgeo.org/mailman/listinfo/mapserver-users>
> >
>
>
>