Some characters in search term cause server errors when using PostgreSQL DB with fulltext search feature

[rwi]

Steps to reproduce:

• setup Communote with a PostgreSQL DB and ensure fulltext feature is enabled (default)
• insert term abc / def into search field on the right and start the search
  • the slash can be replaced with other characters like one of the following to produce the same result |&)(.,-_

Outcome:

• Note list, tag cloud and author filter show error message Loading content failed. Please refresh this page.
• exception like the following is logged

2016-11-01 20:37:03,167 global ERROR http-nio-8080-exec-8 de.communardo.kenmei.fe.uncaught_exception - Requesting http://localhost:8080/microblog/global/widgets/user/AuthorFilterWidget.widget?widget=AuthorFilterWidget&widgetGroup=user&type=DHTML&random=0.33607226270241264&widgetId=AuthorFilter_notesOverview_all&postTextSearchString=abc%20%2F%20def&maxCount=24&loadMoreMode=paging&offset=0&pagingInterval=5&ignoreUserIdsFilter=true resulted in java.lang.RuntimeException: Error performing 'com.communote.server.persistence.query.QueryHelperDao.executeQuery(Query query, QueryParameters queryParameters)' --> org.hibernate.exception.SQLGrammarException: could not execute query using scroll
java.lang.RuntimeException: Error performing 'com.communote.server.persistence.query.QueryHelperDao.executeQuery(Query query, QueryParameters queryParameters)' --> org.hibernate.exception.SQLGrammarException: could not execute query using scroll
	at com.communote.server.persistence.query.QueryHelperDaoBase.executeQuery(QueryHelperDaoBase.java:40)
	at sun.reflect.GeneratedMethodAccessor491.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at org.springframework.orm.hibernate3.HibernateInterceptor.invoke(HibernateInterceptor.java:111)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at com.sun.proxy.$Proxy54.executeQuery(Unknown Source)
	at com.communote.server.core.query.QueryManagement.query(QueryManagement.java:110)
	at com.communote.server.core.query.QueryManagement$$FastClassByCGLIB$$c3bddaa1.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:698)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:631)
	at com.communote.server.core.query.QueryManagement$$EnhancerByCGLIB$$209d5079.query(<generated>)
	at com.communote.server.web.fe.widgets.user.AuthorFilterWidget.handleQueryList(AuthorFilterWidget.java:100)
	at com.communote.server.web.fe.widgets.AbstractPagedListWidget.handleRequest(AbstractPagedListWidget.java:103)
	at com.communote.server.widgets.WidgetController.handleWidgetRequest(WidgetController.java:162)
	at com.communote.server.widgets.springmvc.SpringMVCWidgetController.handleRequest(SpringMVCWidgetController.java:76)
	at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:827)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at com.communote.server.web.commons.filter.CommunoteRestletForwardFilter.doFilter(CommunoteRestletForwardFilter.java:85)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at com.communote.server.web.commons.filter.ExposeLocaleToErrorPageFilter.doFilter(ExposeLocaleToErrorPageFilter.java:36)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at com.communote.server.core.security.AuthenticationFilterManagement$VirtualFilterChain.doFilter(AuthenticationFilterManagement.java:66)
	at com.communote.server.core.security.AuthenticationFilterManagement.doFilter(AuthenticationFilterManagement.java:107)
	at com.communote.server.web.filter.PluginAuthenticationFilter.doFilter(PluginAuthenticationFilter.java:40)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at com.communote.server.web.external.spring.security.CommunoteTokenAuthenticationProcessingFilter.doFilter(CommunoteTokenAuthenticationProcessingFilter.java:160)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at com.communote.server.web.external.spring.security.UserStatusFilter.doFilter(UserStatusFilter.java:83)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at com.communote.server.web.external.spring.security.CommunoteRememberMeProcessingFilter.doFilter(CommunoteRememberMeProcessingFilter.java:113)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at com.communote.server.web.external.spring.security.AuthenticationSuccessFailureFilter.doFilter(AuthenticationSuccessFailureFilter.java:60)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at com.communote.server.web.commons.filter.ForceSslChannelFilter.doFilter(ForceSslChannelFilter.java:136)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at com.communote.server.web.commons.filter.IpRangeChannelFilter.doFilter(IpRangeChannelFilter.java:81)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at com.communote.server.web.commons.filter.LanguageFilter.doFilter(LanguageFilter.java:102)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at com.communote.server.web.commons.filter.ClientStatusHandler.processActiveClient(ClientStatusHandler.java:53)
	at com.communote.server.web.commons.filter.ClientStatusHandler.processClient(ClientStatusHandler.java:82)
	at com.communote.server.web.commons.filter.ClientContextProcessingFilter.processClient(ClientContextProcessingFilter.java:162)
	at com.communote.server.web.commons.filter.ClientContextProcessingFilter.doFilter(ClientContextProcessingFilter.java:93)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at com.communote.server.web.commons.filter.BypassSessionTimeoutFilter.doFilter(BypassSessionTimeoutFilter.java:74)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at com.communote.server.web.commons.filter.InitializationFilter.doFilter(InitializationFilter.java:48)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at com.communote.server.web.commons.filter.KenmeiForwardFilter.doFilter(KenmeiForwardFilter.java:61)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at com.communote.server.web.fe.installer.servlet.InstallationForwardFilter.doFilter(InstallationForwardFilter.java:61)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at com.communote.server.web.commons.filter.EncodingFilter.doFilter(EncodingFilter.java:58)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:522)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1095)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:672)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query using scroll
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.loader.Loader.scroll(Loader.java:2650)
	at org.hibernate.loader.hql.QueryLoader.scroll(QueryLoader.java:521)
	at org.hibernate.hql.ast.QueryTranslatorImpl.scroll(QueryTranslatorImpl.java:417)
	at org.hibernate.engine.query.HQLQueryPlan.performScroll(HQLQueryPlan.java:269)
	at org.hibernate.impl.SessionImpl.scroll(SessionImpl.java:1353)
	at org.hibernate.impl.QueryImpl.scroll(QueryImpl.java:90)
	at com.communote.server.persistence.query.QueryHelperDaoImpl.getNthElement(QueryHelperDaoImpl.java:119)
	at com.communote.server.persistence.query.QueryHelperDaoImpl.limitQueryInstance(QueryHelperDaoImpl.java:217)
	at com.communote.server.persistence.query.QueryHelperDaoImpl.handleExecuteQuery(QueryHelperDaoImpl.java:157)
	at com.communote.server.persistence.query.QueryHelperDaoImpl.handleExecuteQuery(QueryHelperDaoImpl.java:138)
	at com.communote.server.persistence.query.QueryHelperDaoBase.executeQuery(QueryHelperDaoBase.java:36)
	... 123 more
Caused by: org.postgresql.util.PSQLException: FEHLER: Syntaxfehler in tsquery: „:*“
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:116)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
	at org.hibernate.loader.Loader.scroll(Loader.java:2615)
	... 133 more

[rwi]

Cause: the search term is split at the space character and the resulting tokens are used in the fulltext function. For PostgreSQL the function is defined like this (arguments list contains column identifier and parameter name for the value):

return "to_tsvector(" + textSearchConfigurationNameQueryPart + ", "
                + arguments.get(0) + ") @@ tsquery(plainto_tsquery("
                + textSearchConfigurationNameQueryPart + ", " + arguments.get(1)
                + ") :: varchar || ':*')";

The field textSearchConfigurationNameQueryPart has by default the value "simple". If this function is called with the token '/' (i.e. plainto_tsquery('simple', '/') :: varchar || ':*'), plainto_tsquery removes the slash and the tsquery becomes ':*' which causes the syntax error.

How to solve this? No idea. Skipping problematic characters isn't enough because if another dictionary than 'simple' is used stop words (e.g. 'the' when configuring English dictionary ) would lead to the same problem. Removing the concatenation of ':*' would disable prefix matching...

[rwi]

something like

coalesce(nullif(plainto_tsquery('/') :: varchar || ':*', ':*'), '');

could do the trick.