[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:
causality
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 ]
Description

The 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.

Solution

Escape 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.
Mind that since Magnolia 5.4.6, the new Vaadin-based JCR Tools app replaced it, and such queries with "<>" operator work like a charm there.

Meanwhile, we will still fix this one, at least for the sake of the 5.3 branch.

Cheers,
Mika

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,
Michaël

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.
As far as I could test, I could show no evidence of <> being unsupported, escaped or altered in any way, when invoked by code.

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,

Generated at Sun Feb 11 23:09:14 CET 2024 using Jira 9.4.2#940002-sha1:46d1a51de284217efdcb32434eab47a99af2938b.