GeoServer - Serve SQL ORDER BY-clause in ViewParams

Question

I want to pass an SQL-ending as a view param to GeoServer using WMTS. E.g. something like:

...&ViewParams=FILTER%3AORDER%20BY%20road_class%3B

for my SQL view layer where the FILTER-variable has the following regex-validation: ^[\w\d\s\ ]+$.

The query looks something like this:

SELECT * FROM table %FILTER%

I'm getting this error from the GeoServer:

WARN [org.geoserver.ows] - Unable to parse filter: ORDER BY road_class
org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 1; Content is not allowed in prolog.

Is this at all possible, or is there some SQL-inject filter? Or is this some kind of validation error where GeoServer is unable to handle spaces?

Answer 1

No, it's because "filter" is a reserved key, it already has a meaning in the URL. Please use keys that cannot appear in the URL.

Important aside: that general filter expansion is very dangerous, make sure you setup a validation regular expression that can prevent SQL injection attacks (difficult, in general).

Second important aside: the query you give to GeoServer is not run directly but used as a sub-query in a larger query that has the spatial filter, property selection, and the like. The order you're trying to get is unlikely to work. For that use case it's best to use the sortBy parameter instead.