JCR SQL2 query: binding of ISDESCENDANTNODE param

Question

I'm writing a JCR SQL2 query that lists all the nodes of given type that are descendants of a certain path. When I write my query like this:

Query query = queryManager.createQuery(
    "SELECT * FROM [cq:PageContent] where ISDESCENDANTNODE(\"/content\") AND ([sling:resourceType] = $resourceType)", Query.JCR_SQL2);
query.bindValue("resourceType", session.getValueFactory().createValue("my-type"));

it works fine, but when I try to bind what's under ISDESCENDANTNODE like this:

Query query = queryManager.createQuery(
    "SELECT * FROM [cq:PageContent] where ISDESCENDANTNODE($base) AND ([sling:resourceType] = $resourceType)", Query.JCR_SQL2);
query.bindValue("base", session.getValueFactory().createValue("/content"));
query.bindValue("resourceType", session.getValueFactory().createValue("my-type"));

an exception is thrown on query.bindValue("base", ...):

"javax.jcr.query.InvalidQueryException: java.text.ParseException: Query: SELECT * FROM [cq:PageContent] where ISDESCENDANTNODE($base(*)) AND ([sling:resourceType] = $resourceType); expected: )"

I know I can just concatenate the parameter value into the query, but I think it's a bad practice.

toniedzwiedz toniedzwiedz · Accepted Answer · 2019-06-05T20:41:00

As far as I can tell by reading the JSR-283 spec path in ISDESCENDANTOF does not allow variable bindings. The same grammar is described in the Jackrabbit documentation which includes pretty readable railroad diagrams.

You're right that it's an extremely bad practice in SQL to directly concatenate queries with user input. However, contrary to SQL, JCR-SQL2 does not allow you to perform updates in the content repository. You need to explicitly use the JCR API to do so once you've obtained a result set containing nodes. It is, therefore, a bit safer that way. On the other hand, you could still inject a query that is extremely bad performance-wise.

If the path comes from user input, what you could do here is:

Read the path parameter from wherever it comes from
Encode the path using org.jackrabbit.util.Text or use another validation/escape method of your choice.
Concatenate the path with the rest of the query and prepare it by binding the rest of the variables (or just don't if the path is invalid)

JCR SQL2 query: binding of ISDESCENDANTNODE param

1 Answers