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.

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

2 Answers 2

Reset to default
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\ ]+$

Improve this answer
0

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

Improve this answer
3
  • Where do I ad the .*?
    – wondim
    Jul 16, 2018 at 12:30
  • I've just added a screenshot of a parametric view in GeoServer
    – matthieufournel
    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
    Oct 8, 2020 at 13:42

Your Answer

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

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