[MGNLADMLEG-65] JCR Queries app fails with some operators Created: 01/Jul/16 Updated: 03/Aug/16 Resolved: 15/Jul/16 |
|
| Status: | Closed |
| Project: | Admininterface Legacy 4.x (closed) |
| Component/s: | None |
| Affects Version/s: | 5.2.5, 5.3.1 |
| Fix Version/s: | 5.2.6, 5.3.2 |
| Type: | Bug | Priority: | Critical |
| Reporter: | Michaël van der Mark | Assignee: | Hieu Nguyen Duc |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | 1d 2h 50m | ||
| Time Spent: | 2d 5h 10m | ||
| Original Estimate: | 4d | ||
| Issue Links: |
|
||||
| Template: |
|
||||
| Acceptance criteria: |
Empty
|
||||
| Release notes required: |
Yes
|
||||
| Date of First Response: | |||||
| Sprint: | Saigon 52 | ||||
| Story Points: | 3 | ||||
| Description |
|
If I create a JCR query and I want for example to exclude some page templates in the result I get an error message: AND p.[mgnl:template] <> 'eybbww-templating-module:pages/bbwwArticle' In Magnolia 5.3.x I can still perform these queries. However in Magnolia 5.4.x this gives errors. I also tried several combinations by putting ( ) in the statements. ORDER BY score() desc; expected: =, <>, <, >, <=, >=, LIKE, IS, NOT 2016-07-01 08:02:13,000 ERROR .magnolia.module.admininterface.pages.JCRUtilsPage: Error in JCR query: javax.jcr.query.InvalidQueryException: ORDER BY score() desc; expected: =, <>, <, >, <=, >=, LIKE, IS, NOT at org.apache.jackrabbit.commons.query.sql2.Parser.getSyntaxError(Parser.java:978) at org.apache.jackrabbit.commons.query.sql2.Parser.getSyntaxError(Parser.java:968) at org.apache.jackrabbit.commons.query.sql2.Parser.parseCondition(Parser.java:324) at org.apache.jackrabbit.commons.query.sql2.Parser.parseCondition(Parser.java:264) at org.apache.jackrabbit.commons.query.sql2.Parser.parseAnd(Parser.java:243) at org.apache.jackrabbit.commons.query.sql2.Parser.parseConstraint(Parser.java:233) at org.apache.jackrabbit.commons.query.sql2.Parser.createQueryObjectModel(Parser.java:117) at org.apache.jackrabbit.commons.query.sql2.SQL2QOMBuilder.createQueryObjectModel(SQL2QOMBuilder.java:55) at org.apache.jackrabbit.core.query.QOMQueryFactory.createQuery(QOMQueryFactory.java:69) at org.apache.jackrabbit.core.query.CompoundQueryFactory.createQuery(CompoundQueryFactory.java:67) at org.apache.jackrabbit.core.query.QueryManagerImpl$2.perform(QueryManagerImpl.java:95) at org.apache.jackrabbit.core.query.QueryManagerImpl$2.perform(QueryManagerImpl.java:91) at org.apache.jackrabbit.core.session.SessionState.perform(SessionState.java:216) at org.apache.jackrabbit.core.query.QueryManagerImpl.perform(QueryManagerImpl.java:197) at org.apache.jackrabbit.core.query.QueryManagerImpl.createQuery(QueryManagerImpl.java:91) at info.magnolia.cms.util.QueryUtil.search(QueryUtil.java:265) at info.magnolia.cms.util.QueryUtil.search(QueryUtil.java:249) at info.magnolia.module.admininterface.pages.JCRUtilsPage.query(JCRUtilsPage.java:124) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at info.magnolia.cms.servlets.MVCServletHandlerImpl.execute(MVCServletHandlerImpl.java:118) at info.magnolia.cms.servlets.CommandBasedMVCServletHandler.execute(CommandBasedMVCServletHandler.java:76) at info.magnolia.cms.servlets.MVCServlet.doPost(MVCServlet.java:119) at info.magnolia.cms.servlets.MVCServlet.doGet(MVCServlet.java:65) at javax.servlet.http.HttpServlet.service(HttpServlet.java:622) at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) at info.magnolia.cms.filters.ServletDispatchingFilter.doFilter(ServletDispatchingFilter.java:148) at info.magnolia.cms.filters.AbstractMgnlFilter.doFilter(AbstractMgnlFilter.java:85) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:81) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:81) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:81) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:81) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:81) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:81) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:81) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:81) at info.magnolia.cms.filters.CompositeFilter.doFilter(CompositeFilter.java:65) at info.magnolia.cms.filters.AbstractMgnlFilter.doFilter(AbstractMgnlFilter.java:85) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.cms.filters.VirtualUriFilter.doFilter(VirtualUriFilter.java:69) at info.magnolia.cms.filters.AbstractMgnlFilter.doFilter(AbstractMgnlFilter.java:85) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:81) at info.magnolia.cms.i18n.I18nContentSupportFilter.doFilter(I18nContentSupportFilter.java:74) at info.magnolia.cms.filters.AbstractMgnlFilter.doFilter(AbstractMgnlFilter.java:85) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.cms.filters.RangeSupportFilter.doFilter(RangeSupportFilter.java:84) at info.magnolia.cms.filters.AbstractMgnlFilter.doFilter(AbstractMgnlFilter.java:85) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.cms.security.BaseSecurityFilter.doFilter(BaseSecurityFilter.java:57) at info.magnolia.cms.filters.AbstractMgnlFilter.doFilter(AbstractMgnlFilter.java:85) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:81) at info.magnolia.cms.security.CsrfSecurityFilter.doFilter(CsrfSecurityFilter.java:106) at info.magnolia.cms.filters.AbstractMgnlFilter.doFilter(AbstractMgnlFilter.java:85) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.cms.security.SecurityCallbackFilter.doFilter(SecurityCallbackFilter.java:79) at info.magnolia.cms.filters.OncePerRequestAbstractMgnlFilter.doFilter(OncePerRequestAbstractMgnlFilter.java:59) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.cms.security.LogoutFilter.doFilter(LogoutFilter.java:94) at info.magnolia.cms.filters.OncePerRequestAbstractMgnlFilter.doFilter(OncePerRequestAbstractMgnlFilter.java:59) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.module.site.filters.SiteMergeFilter.doFilter(SiteMergeFilter.java:119) at info.magnolia.cms.filters.AbstractMgnlFilter.doFilter(AbstractMgnlFilter.java:85) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.multisite.filters.MultiSiteFilter.doFilter(MultiSiteFilter.java:106) at info.magnolia.cms.filters.AbstractMgnlFilter.doFilter(AbstractMgnlFilter.java:85) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.cms.filters.MultiChannelFilter.doFilter(MultiChannelFilter.java:83) at info.magnolia.cms.filters.AbstractMgnlFilter.doFilter(AbstractMgnlFilter.java:85) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.module.cache.filter.GZipFilter.doFilter(GZipFilter.java:73) at info.magnolia.cms.filters.OncePerRequestAbstractMgnlFilter.doFilter(OncePerRequestAbstractMgnlFilter.java:59) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:81) at info.magnolia.cms.security.auth.login.LoginFilter.doFilter(LoginFilter.java:127) at info.magnolia.cms.filters.AbstractMgnlFilter.doFilter(AbstractMgnlFilter.java:85) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.enterprise.registration.RegistrationFilter.doFilter(RegistrationFilter.java:64) at info.magnolia.cms.filters.AbstractMgnlFilter.doFilter(AbstractMgnlFilter.java:85) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:81) at info.magnolia.cms.filters.CosMultipartRequestFilter.doFilter(CosMultipartRequestFilter.java:87) at info.magnolia.cms.filters.OncePerRequestAbstractMgnlFilter.doFilter(OncePerRequestAbstractMgnlFilter.java:59) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.module.devicedetection.filter.DeviceDetectionFilter.doFilter(DeviceDetectionFilter.java:71) at info.magnolia.cms.filters.OncePerRequestAbstractMgnlFilter.doFilter(OncePerRequestAbstractMgnlFilter.java:59) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.personalization.preview.filter.PreviewFilter.doFilter(PreviewFilter.java:92) at info.magnolia.cms.filters.OncePerRequestAbstractMgnlFilter.doFilter(OncePerRequestAbstractMgnlFilter.java:59) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:81) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:81) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:81) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:81) at info.magnolia.cms.filters.ContentTypeFilter.doFilter(ContentTypeFilter.java:148) at info.magnolia.cms.filters.AbstractMgnlFilter.doFilter(AbstractMgnlFilter.java:85) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.cms.filters.ContextFilter.doFilter(ContextFilter.java:128) at info.magnolia.cms.filters.AbstractMgnlFilter.doFilter(AbstractMgnlFilter.java:85) at info.magnolia.cms.filters.MgnlFilterChain.doFilter(MgnlFilterChain.java:79) at info.magnolia.cms.filters.CompositeFilter.doFilter(CompositeFilter.java:65) at info.magnolia.cms.filters.AbstractMgnlFilter.doFilter(AbstractMgnlFilter.java:85) at info.magnolia.cms.filters.SafeDestroyMgnlFilterWrapper.doFilter(SafeDestroyMgnlFilterWrapper.java:107) at info.magnolia.cms.filters.MgnlFilterDispatcher.doDispatch(MgnlFilterDispatcher.java:67) at info.magnolia.cms.filters.MgnlMainFilter.doFilter(MgnlMainFilter.java:108) at info.magnolia.cms.filters.MgnlMainFilter.doFilter(MgnlMainFilter.java:94) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) 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.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:521) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1096) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:674) 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) |
| Comments |
| Comment by Hieu Nguyen Duc [ 12/Jul/16 ] |
DescriptionThe issue just occurs in old JCR app (JCR Queries) so it's related to admininterface-legacy module. Root cause+ StringEscapeUtils#escapeHtml escapes SQL operators so query execution can't recognize those characters and shows syntax errors. + As XSS Prevention Cheat Sheet mentions, we violated rule #1. "path", "statement" and "result" fields haven't been escaped correctly before being inserted into HTML document. What we did is to escape input in setters. Consequently the SQL operators are also escaped unexpectedly. SolutionEscape HTML in getters instead of setters for all fields in order to guarantee: + SQL query doesn't fail with operators + XSS invulnerabibility |
| Comment by Mikaël Geljić [ 12/Jul/16 ] |
|
Hi mvdmark, Judging by the stack trace, this affects our "old" JCR queries app, which was still based on the Magnolia 4.x UI. So I moved it to the appropriate Jira project. Meanwhile, we will still fix this one, at least for the sake of the 5.3 branch. Cheers, |
| Comment by Michaël van der Mark [ 12/Jul/16 ] |
|
Hi @mgeljic, I used magnolia 5.4.5 in my project. In newer version I saw there is a new JCR app in use. However I also encountered this error when I was using this query in Java. After this I tested my query in the jcr queries app and it gave me the same error message. But If I use the same query on a 5.3.x instance I could perform my query. So If I upgrade to 5.4.7 I also have the possibility to execute this query? In java I used code like this:
predicate.append("\n AND (p.[mgnl:template] <> 'eybbww-templating-module:pages/bbwwArticle' " );
And made a workaround for now by using code like this: predicate.append("\n AND (p.[mgnl:template] = 'eybbww-templating-module:pages/bbwwArticle' " ); predicate.append("\n OR p.[mgnl:template] = 'eybbww-templating-module:pages/bbwwVideoArticle'" ); predicate.append("\n OR p.[mgnl:template] = 'eybbww-templating-module:pages/bbwwHTML5Embed'" ); predicate.append("\n OR p.[mgnl:template] = 'eybbww-templating-module:pages/bbwwHTML5Embed0'" ); If the query is build in Java we use info.magnolia.cms.util.QueryUtil
/**
* Searches for statement and then pops-up in the node hierarchy until returnItemType is found. If the result
* is not returnItemType or none of its parents are then next node in result is checked. Duplicate nodes are
* removed from result.
* For date/time expressions use <code>DateUtil.create*Expression()</code> methods.
*
* @return query result as collection of nodes
*/
public static NodeIterator search(String workspace, String statement, String language, String returnItemType) throws LoginException, RepositoryException {
return search(workspace, statement, language, returnItemType, false);
}
Are we using the correct method for doing this query? We construct a search query btw. Cheers, |
| Comment by Mikaël Geljić [ 12/Jul/16 ] |
|
Okay, that's good input thanks. Using QueryUtil#search is the correct way. Alternatively, one may use JCR's QueryManager straight—but that's what our QueryUtil does for you anyway, without altering the statement whatsoever. Re: upgrade to 5.4.7, I also could not point to any recent change in QueryUtil, so I doubt it's gonna solve your problem by itself. In that case, I suppose the InvalidQueryException might highlight a different issue. I would advise to debug/intercept the query when it is fully built, and execute it in the new JCR Tools app (because the old one suffers the problem here). Hope this helps, |