2

I am trying to make query with GeoServer SQL View. However, GeoServer fails to make a successful query when I use values with spaces.

Below is my setting.

SELECT name, orig_name, desig, 
   desig_eng, desig_type, iucn_cat, int_crit, marine, rep_m_area, 
   gis_m_area, rep_area, gis_area, no_take, no_tk_area, status, 
   status_yr, gov_type, own_type, mang_auth, mang_plan, verif, metadataid, 
   sub_loc, parent_iso, iso3, shape_leng, shape_area, geom
FROM public.tz_protected_areas
WHERE desig LIKE %desig%

Name: desig Default value: 'Forest Reserve' Regex: ^[A-z\s]+$

Part of my URL with query:

application/openlayers&VIEWPARAMS=desig:'Forest%20Reserve'

But I get the following error:

Invalid value for parameter desig
java.lang.IllegalArgumentException: Value 'Forest plantation' does not match ^[A-z\s]+$

Edit: I have added the full error message related to the issue below:

2017-06-23 19:18:33,859 INFO [geoserver.filters] - 0:0:0:0:0:0:0:1 "GET /geoserver/myws/wms?VIEWPARAMS=desig%3A%27Forest%20Plantation%27&STYLES=&LAYERS=myws%3Atz_protected_areas&FORMAT=image%2Fpng&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetMap&SRS=EPSG%3A4326&BBOX=27.06366873101,-14.292436494419,43.104053958472,1.6644050599834&WIDTH=768&HEIGHT=764" took 16ms
2017-06-23 19:18:33,862 INFO [geoserver.filters] - 0:0:0:0:0:0:0:1 "GET /geoserver/myws/wms?VIEWPARAMS=desig%3A%27Forest%20Plantation%27&STYLES=&LAYERS=myws%3Atz_protected_areas&FORMAT=image%2Fpng&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetMap&SRS=EPSG%3A4326&BBOX=27.06366873101,-14.292436494419,43.104053958472,1.6644050599834&WIDTH=768&HEIGHT=764" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36" "http://localhost:8080/geoserver/myws/wms?service=WMS&version=1.1.0&request=GetMap&layers=myws:tz_protected_areas&styles=&bbox=29.7370662689209,-11.62756,40.4306564205623,-1.00047143443565&width=768&height=763&srs=EPSG:4326&format=application/openlayers&VIEWPARAMS=desig:%27Forest%20Plantation%27" 
2017-06-23 19:18:33,863 INFO [geoserver.wms] - 
Request: getServiceInfo
2017-06-23 19:18:33,871 ERROR [geotools.jdbc] - Invalid value for parameter desig
java.lang.IllegalArgumentException: Value 'Forest Plantation' does not match ^[A-z\\]+$
    at org.geotools.jdbc.RegexpValidator.validate(RegexpValidator.java:44)
    at org.geotools.jdbc.VirtualTable.expandParameters(VirtualTable.java:201)
    at org.geotools.jdbc.JDBCDataStore.encodeAliasedTableName(JDBCDataStore.java:4374)
    at org.geotools.jdbc.JDBCDataStore.encodeTableName(JDBCDataStore.java:4363)
    at org.geotools.jdbc.JDBCDataStore.selectSQL(JDBCDataStore.java:3103)
    at org.geotools.jdbc.JDBCFeatureSource.getReaderInternal(JDBCFeatureSource.java:608)
    at org.geotools.data.store.ContentFeatureSource.getReader(ContentFeatureSource.java:563)
    at org.geotools.data.store.ContentFeatureCollection.features(ContentFeatureCollection.java:164)
    at org.geotools.data.store.ContentFeatureCollection.features(ContentFeatureCollection.java:58)
    at org.geotools.data.crs.ForceCoordinateSystemFeatureResults.openIterator(ForceCoordinateSystemFeatureResults.java:95)
    at org.geotools.feature.collection.AbstractFeatureCollection.features(AbstractFeatureCollection.java:69)
    at org.geotools.feature.collection.AbstractFeatureCollection.features(AbstractFeatureCollection.java:53)
    at org.geotools.renderer.lite.StreamingRenderer.drawPlain(StreamingRenderer.java:2340)
    at org.geotools.renderer.lite.StreamingRenderer.processStylers(StreamingRenderer.java:2066)
    at org.geotools.renderer.lite.StreamingRenderer.paint(StreamingRenderer.java:824)
    at org.geoserver.wms.map.RenderedImageMapOutputFormat.produceMap(RenderedImageMapOutputFormat.java:515)
    at org.geoserver.wms.map.RenderedImageMapOutputFormat.produceMap(RenderedImageMapOutputFormat.java:258)
    at org.geoserver.wms.map.RenderedImageMapOutputFormat.produceMap(RenderedImageMapOutputFormat.java:130)
    at org.geoserver.wms.GetMap.executeInternal(GetMap.java:510)
    at org.geoserver.wms.GetMap.run(GetMap.java:254)
    at org.geoserver.wms.GetMap.run(GetMap.java:125)
    at org.geoserver.wms.DefaultWebMapService.getMap(DefaultWebMapService.java:369)
    at sun.reflect.GeneratedMethodAccessor282.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:319)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.geoserver.kml.WebMapServiceKmlInterceptor.invoke(WebMapServiceKmlInterceptor.java:34)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.geoserver.gwc.wms.CacheSeedingWebMapService.invoke(CacheSeedingWebMapService.java:62)
    at org.geoserver.gwc.wms.CacheSeedingWebMapService.invoke(CacheSeedingWebMapService.java:36)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.geoserver.gwc.wms.CachingWebMapService.invoke(CachingWebMapService.java:74)
    at org.geoserver.gwc.wms.CachingWebMapService.invoke(CachingWebMapService.java:55)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.geoserver.ows.util.RequestObjectLogger.invoke(RequestObjectLogger.java:55)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at com.sun.proxy.$Proxy71.getMap(Unknown Source)
    at sun.reflect.GeneratedMethodAccessor242.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.geoserver.ows.Dispatcher.execute(Dispatcher.java:792)
    at org.geoserver.ows.Dispatcher.handleRequestInternal(Dispatcher.java:274)
    at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
    at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.geoserver.filters.ThreadLocalsCleanupFilter.doFilter(ThreadLocalsCleanupFilter.java:28)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:75)
    at org.geoserver.wms.animate.AnimatorFilter.doFilter(AnimatorFilter.java:71)
    at org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:71)
    at org.geoserver.filters.SpringDelegatingFilter.doFilter(SpringDelegatingFilter.java:46)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.geoserver.platform.AdvancedDispatchFilter.doFilter(AdvancedDispatchFilter.java:50)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:311)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:69)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:116)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:73)
    at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:69)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:73)
    at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
    at org.geoserver.security.filter.GeoServerAnonymousAuthenticationFilter.doFilter(GeoServerAnonymousAuthenticationFilter.java:54)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:69)
    at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:73)
    at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
    at org.geoserver.security.filter.GeoServerBasicAuthenticationFilter.doFilter(GeoServerBasicAuthenticationFilter.java:83)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:69)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
    at org.geoserver.security.filter.GeoServerSecurityContextPersistenceFilter$1.doFilter(GeoServerSecurityContextPersistenceFilter.java:53)
    at org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:73)
    at org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:173)
    at org.geoserver.security.GeoServerSecurityFilterChainProxy.doFilter(GeoServerSecurityFilterChainProxy.java:135)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.geoserver.filters.LoggingFilter.doFilter(LoggingFilter.java:82)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.geoserver.filters.GZIPFilter.doFilter(GZIPFilter.java:42)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.geoserver.filters.SessionDebugFilter.doFilter(SessionDebugFilter.java:48)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.geoserver.filters.FlushSafeFilter.doFilter(FlushSafeFilter.java:44)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:109)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:503)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1070)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Unknown Source)

I am using GeoServer 2.6 and PostgreSQL 9.5 with PostGIS 2.2. Edit: I also tried GeoServer 2.11 and the issue is the same.

10
  • I'm not sure \s is supported, try using a space or possibly 2 \
    – Ian Turton
    Commented Jun 23, 2017 at 15:55
  • I still get the error when I placed \` in place of \s`.
    – wondim
    Commented Jun 23, 2017 at 16:19
  • 1
    There is no need to escape the space
    – Ian Turton
    Commented Jun 23, 2017 at 16:55
  • This editor escaped one of the slashes. :) I mean, when I used \\ it still doesn't work.
    – wondim
    Commented Jun 23, 2017 at 17:02
  • Just a space should work though
    – Ian Turton
    Commented Jun 23, 2017 at 17:03

2 Answers 2

1

Rather than allow the regex validator to accept anything and everything, add the offending character to the default regex expression which comes with the parameter in Geoserver.

In OP's case, since the offending character is a space, this should work:

^[\w\d\s\ ]+$

0

Try .* for the regexp expression. It's not secure, but it should work.
enter image description here

3
  • Where do I ad the .*?
    – wondim
    Commented Jul 16, 2018 at 12:30
  • I've just added a screenshot of a parametric view in GeoServer Commented Jul 18, 2018 at 18:25
  • 1
    Please do not do this, it is extremely insecure and leaves you wide open for SQL injection: w3schools.com/sql/sql_injection.asp
    – wfgeo
    Commented Oct 8, 2020 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.