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

[Anonymous]

Originally created by: 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</generated></generated>

[Anonymous]

Originally posted by: 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...

[Anonymous]

Originally posted by: rwi

something like

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

could do the trick.