Postgres: Error using question mark operator in cfquery

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

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

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

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)

I’ve added this to the sprint for 6.0

1 Like

Just seen. Thanks Zack. :grinning_face_with_smiling_eyes:

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?

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

@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?

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 ??

1 Like