Postgres: Error using question mark operator in cfquery

dev
1

When working with the “jsonb” data type, Postgres uses the “?” as an operator.
Other operators are with the question mark are “? |” and “?&”.

Using these operators in cfquery Lucee raises an exception.

For testing:

CREATE TABLE public.test_json ( field_json JSONB );
 
INSERT INTO public.test_json ("field_json")
  VALUES 
  (E'["Food", "Wine"]'),
  (E'["Food", "Water"]'),
  (E'["Water"]');

SELECT field_json
FROM test_json
WHERE field_json ? 'Water'; <- ERROR in cfquery

I can’t figure out if it’s a driver or Lucee problem.

The error is this:

java.lang.RuntimeException
Message: there are more question marks in the SQL than params defined

Stacktrace:

lucee.runtime.exp.NativeException: there are more question marks in the SQL than params defined
at lucee.runtime.db.SQLImpl.toString(SQLImpl.java:119)
at lucee.runtime.exp.DatabaseException.getCatchBlock(DatabaseException.java:162)
at lucee.runtime.PageContextImpl._setCatch(PageContextImpl.java:3020)
at lucee.runtime.PageContextImpl.setCatch(PageContextImpl.java:3000)
at system.web.controller_cfc$cf$x.udfCall3(/coldbox/system/web/Controller.cfc:992)
at system.web.controller_cfc$cf$x.udfCall(/coldbox/system/web/Controller.cfc)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213)
at lucee.runtime.type.scope.UndefinedImpl.callWithNamedValues(UndefinedImpl.java:804)
at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866)
at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1781)
at system.web.controller_cfc$cf$x.udfCall3(/coldbox/system/web/Controller.cfc:667)
at system.web.controller_cfc$cf$x.udfCall(/coldbox/system/web/Controller.cfc)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.callWithNamedValues(UDFImpl.java:213)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:698)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:585)
at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1951)
at lucee.runtime.util.VariableUtilImpl.callFunctionWithNamedValues(VariableUtilImpl.java:866)
at lucee.runtime.PageContextImpl.getFunctionWithNamedValues(PageContextImpl.java:1781)
at system.bootstrap_cfc$cf$w.udfCall1(/coldbox/system/Bootstrap.cfc:285)
at system.bootstrap_cfc$cf$w.udfCall(/coldbox/system/Bootstrap.cfc)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223)
at lucee.runtime.type.scope.UndefinedImpl.call(UndefinedImpl.java:786)
at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:787)
at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1762)
at system.bootstrap_cfc$cf$w.udfCall1(/coldbox/system/Bootstrap.cfc:519)
at system.bootstrap_cfc$cf$w.udfCall(/coldbox/system/Bootstrap.cfc)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:697)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:585)
at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1932)
at lucee.runtime.util.VariableUtilImpl.callFunctionWithoutNamedValues(VariableUtilImpl.java:787)
at lucee.runtime.PageContextImpl.getFunction(PageContextImpl.java:1762)
at apps._public.application_cfc$cf$1.udfCall(/apps/public/Application.cfc:38)
at lucee.runtime.type.UDFImpl.implementation(UDFImpl.java:112)
at lucee.runtime.type.UDFImpl._call(UDFImpl.java:350)
at lucee.runtime.type.UDFImpl.call(UDFImpl.java:223)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:697)
at lucee.runtime.ComponentImpl._call(ComponentImpl.java:585)
at lucee.runtime.ComponentImpl.call(ComponentImpl.java:1932)
at lucee.runtime.listener.ModernAppListener.call(ModernAppListener.java:444)
at lucee.runtime.listener.ModernAppListener._onRequest(ModernAppListener.java:135)
at lucee.runtime.listener.MixedAppListener.onRequest(MixedAppListener.java:44)
at lucee.runtime.PageContextImpl.execute(PageContextImpl.java:2475)
at lucee.runtime.PageContextImpl._execute(PageContextImpl.java:2465)
at lucee.runtime.PageContextImpl.executeCFML(PageContextImpl.java:2436)
at lucee.runtime.engine.Request.exe(Request.java:45)
at lucee.runtime.engine.CFMLEngineImpl._service(CFMLEngineImpl.java:1194)
at lucee.runtime.engine.CFMLEngineImpl.serviceCFML(CFMLEngineImpl.java:1140)
at lucee.loader.engine.CFMLEngineWrapper.serviceCFML(CFMLEngineWrapper.java:97)
at lucee.loader.servlet.CFMLServlet.service(CFMLServlet.java:51)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:711)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:459)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:385)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:313)
at org.tuckey.web.filters.urlrewrite.NormalRewrittenUrl.doRewrite(NormalRewrittenUrl.java:213)
at org.tuckey.web.filters.urlrewrite.RuleChain.handleRewrite(RuleChain.java:171)
at org.tuckey.web.filters.urlrewrite.RuleChain.doRules(RuleChain.java:145)
at org.tuckey.web.filters.urlrewrite.UrlRewriter.processRequest(UrlRewriter.java:92)
at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:394)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:890)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.RuntimeException: there are more question marks in the SQL than params defined
… 92 more

2

It’s Lucee. There’s a problem with query param resolution when queries contain literal question marks.

https://luceeserver.atlassian.net/browse/LDEV-2754

3

Thanks Julian!

I add my comment to ticket about PostgreSQL behavior.

For the moment I have solved it using the undocumented function:

jsonb_exists(jsonb, text)

4

I’ve added this to the sprint for 6.0

1 Like
5

Just seen. Thanks Zack. :smile:

6

This seems like quite a nasty regression. Things that worked fine in Lucee 5.2.x are now broken by simply upgrading to Lucee 5.3.x, so it doesn’t quite feel right to wait for Lucee 6.0.x to solve it?

1 Like
7

Agree with @justincarter on not being thrilled about having to wait for Lucee 6 to get this resolved. Just tried to upgrade to 5.3.10-SNAPSHOT+79 and this bit us.

FWIW, I believe this was working acceptably on 5.3.8+206 - at least we were able to escape the question mark by doubling it there - i.e. WHERE field_json ?? 'Water'

1 Like
8

@Zackster I assume this is logic in the QueryParamConverter class. It shouldn’t be too hard to modify it so when it finds a ? that it checks to see if the next char is another ? and then skips them both. So long as you’d never legitimately have two actual params like that, it should work. I don’t know the history here, but I assume this class had some refactoring that lost that ?? escape logic?

9

Interesting, it would appear that logic was refactored 8 months ago in this commit

Those changes surely don’t seem appropriate for that ticket!

Then part of that change was reversed one month later in this commit

The regression was

lucee.runtime.exp.NativeException: Index 0 out of bounds for length 0
at java.base/jdk.internal.util.Preconditions.outOfBounds(Preconditions.java:64)
at java.base/jdk.internal.util.Preconditions.outOfBoundsCheckIndex(Preconditions.java:70)
at java.base/jdk.internal.util.Preconditions.checkIndex(Preconditions.java:248)
at java.base/java.util.Objects.checkIndex(Objects.java:372)
at java.base/java.util.ArrayList.get(ArrayList.java:459)
at lucee.runtime.tag.util.QueryParamConverter.convert(QueryParamConverter.java:195)

so it just appears the original escape logic wasn’t fully tested and can probably be put back and fix the bugs it had. However, I’ll point out it appears the escape char was \?, not ??. Does anyone know what Adobe CF does for this? It would appear the generally accepted JDBC standard is the double ??

2 Likes
10

It should also be ignoring SQL comments. I know that doesn’t seem like much of a blocker because a ? is easily removed from a comment, but not always trivial (for e.g. third party code that you don’t necessarily control).

2 Likes