TransWikia.com

GeoServer SQL View with parameter value having space not working

Geographic Information Systems Asked by wondim on December 11, 2020

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-zs]+$

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-zs]+$

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.

geoserver

2 Answers

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

Answered by matthieufournel on December 11, 2020

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:

^[wds ]+$

Answered by wfgeo on December 11, 2020

Add your own answers!

Ask a Question

Get help from others!

Recent Answers

Recent Questions

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP