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.

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

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

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

  • Where do I ad the .*? – wondim Jul 16 '18 at 12:30
  • I've just added a screenshot of a parametric view in GeoServer – matthieufournel Jul 18 '18 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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