search

mapcentia / geocloud2

The GC2 framework helps you build a spatial data infrastructure quickly and easily. Powered using open source components for a scalable solution focused on freedom rather than fees.
https://www.osgeo.org/projects/gc2-vidi/
GNU Affero General Public License v3.0
148 stars 67 forks source link

setting custom sql in admin can render sql query invalid, failing silently #146

Open giovanniborella opened 2 months ago

giovanniborella commented 2 months ago

setting sql in this box can cause unwanted sideeffects:

billede

especially when adding statements after the table reference. when using filtering in vidi, the where-clause is appended to the sql in the box making mapserver return nothing due to a faulty statement, in this case it would look like ...SELECT * FROM public.lukkede_sager order by sagsnummer desc WHERE sagsnummer = '10232472'... which has bad ordering.

i wholeheartedly agree that ordering in that manner is bad practice, however nothing prevents the user from doing it. i suggest adding a check to the textbox, making sure the statement ended with the name of the table, so that the user cannot add anything after. Even setting a where clause would break the call from mapserver.

in the cases we have spotted, mapserver returns an empty image - due to the faulty sql - but no error messages, an exception would be really nice here, as it it not really something you can catch or test in the admin console (cannot set filter).

mapcentia commented 1 month ago

I thought this would result in this in MapServer:

select * from (SELECT * FROM public.lukkede_sager order by sagsnummer desc) as foo WHERE sagsnummer = '10232472'

If not, it's a bug