luceneaemjcrjcr-sql2

JCR-SQL - contains function doesn't escape special characters?

I have the following query string:

SELECT jcr:title, jcr:created, jcr:description FROM cq:PageContent WHERE jcr:path LIKE '/content/.../%' AND CONTAINS (., '*') ORDER BY date ASC

The problem is that the query is returning all nodes from the given path even though they don't have asterisk in any proeprty. I wanted to escape the asterisk character, but the result is the same. I've tried something like this:

SELECT jcr:title, jcr:created, jcr:description FROM cq:PageContent WHERE jcr:path LIKE '/content/.../%' AND CONTAINS (., '\*') ORDER BY date ASC

or even something like this:

SELECT jcr:title, jcr:created, jcr:description FROM cq:PageContent WHERE jcr:path LIKE '/content/.../%' AND CONTAINS (., '\*\*\*\*\*\*\*\*\*\*\*') ORDER BY date ASC

In all these queries, the result is the same, even though none of these pages have property which contains the asterisk character (or 11 of them)

The documentation of jcr:contains function says:

Within the searchexp literal instances of single quote (“'”), double quote (“"”) and hyphen (“-”) must be escaped with a backslash (“\”). Backslash itself must therefore also be escaped, ending up as double backslash (“\”).

Other characters like * are not mentioned so it should work even without any escaping (?). Please let me understand why I'm getting such results here and how to properly escape such chars.

Solution

  • I'm not sure, if jcr:contains is the right method for you. Maybe jcr:like is the better approach for what you want.

    jcr:contains is a fulltext search, and uses a lucene index. So it might have some unexpected impacts. It also cannot so easily combined with other indexes.

    jcr:like is a attribute comparison with wildcards. And this wildcards can be escaped with a backslash. (https://docs.adobe.com/docs/en/spec/jcr/1.0/6.6.5.1_jcr_like_Function.html)

    1st Example SQL-2 Query

    Searches cq:PageContent nodes with an * in any attribute. The % (percent-sign) is the wildcard-symbol. The * is searched.

    SELECT * FROM [cq:PageContent] AS content
WHERE ISDESCENDANTNODE('/content/myproject/...')
AND content.* LIKE '%*%'

    2nd Example SQL-2 Query

    Searches cq:PageContent nodes with an % in any attribute. Therefore the percent-sign is escaped with \% (and surrounded by the wildcard %).

    SELECT * FROM [cq:PageContent] AS content
WHERE ISDESCENDANTNODE('/content/myproject/...')
AND content.* LIKE '%\%%'

    3rd Example XPath Query

    Almost the same as the last one, just as XPath query. Only I just don't know, how you can search for any attribute. So this example searches for jcr:title attributes.

    /jcr:root/content/myproject/...//element(*, cq:PageContent)[jcr:like(@jcr:title, '%\%%')]