Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Query Params are not Resolved When Query has Question Mark

Description

https://luceeserver.atlassian.net/browse/LDEV-2754 mentioned queries with comments; but several comments on this story that match our use case were unresolved from this fix.

Running the following query will result in the error “there are more question marks in the SQL than params defined in the SQL String”:

<cftry> <cfset var local = structNew()> <cfquery datasource="my_dsn" name="local.qryTest"> SET NOCOUNT ON; <!--- if this query has no other cfqueryparams, the query will run just fine. Adding this in will cause the query to fail. ---> declare @orgID int = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="0">; declare @tblCountries TABLE (countryID int, country varchar(2)); insert into @tblCountries (countryID, country) values (1, 'US'), (2, 'CA'); SELECT country as [Country/Entity?], countryID as countryID FROM @tblCountries; </cfquery> <cfdump var="#local#"> <cfcatch> <cfdump var="#cfcatch#"> </cfcatch> </cftry>

 

If the declare @orgID line is commented out, so no params are sent with the query, then the query will run successfully.

This was working in 5.2.9.31.

I confirmed this query will not run in 5.3.10.125-SNAPSHOT

I confirmed I cannot escape that ? to get it to run successfully.

Environment

None

Activity

Show:

Zac Spitzer 26 July 2024 at 16:12
Edited

It's a question of priorities, sure it breaks but the provided example doesn't seem that critical (i.e using a special character in a column name)

I've already spent a lot of time on the linked tickets which solves real problems, this is an edge case which solving would add overhead to all queries Lucee executes, i know there’s some postgres syntax which is related, but that’s a somewhat different story

Timothy Lux 26 July 2024 at 14:53

You want to solve this because why... because it's hard? Because you erroneously believe a use case is “limited”? I provided an example that breaks under your code. seems like you should fix that.

Zac Spitzer 26 July 2024 at 14:41
Edited

this contrived example is quite challenging, we could ignore ?, dos the column name really need to be [county/Entity?]

because currently we reparse the output sql for debugging or logging to datasource log, it becomes quite problematic, unless we store the source sql as well, logging to datasource isn’t such a problem, as that’s done immediately, but debug logs can potentially hang around for a long time before they are fetched and rendered on demand

escaping the ? with ?? i can make the queryParamConverter handle [county/Entity??] but that breaks the debug / logging rendering,

if i change it to ? on the preparse, leaving it in as ?? makes the column name becomes county/Entity?? which is also suboptimal

I’d like to solve this, but this current example seems more trouble than it’s worth?

I have a pending patch for https://luceeserver.atlassian.net/browse/LDEV-4866 and https://luceeserver.atlassian.net/browse/LDEV-4867 but that doesn’t make any difference here, I did try tweaking that code locally, to no avail

Zac Spitzer 29 March 2023 at 11:22

Pothys - MitrahSoft 17 March 2023 at 15:30
Edited

I've checked this ticket and confirmed the issue happened on the lucee latest version 5.4.0.37-SNAPSHOT.
Using the sql with '?' and queryParam throws error like there are more question marks in the SQL than params defined.

the regression starts from 5.3.9.70-SNAPSHOT and this commit causes to the issue https://github.com/lucee/Lucee/commit/107c7c5566927056b96a60d03127be1463659de8

this error is occurred in the _doEndTag() method on query tag class after the query execution completed.
That error occurred while trying to log the query details to datasource.log here
https://github.com/lucee/Lucee/blob/420e60a670f218ff9887d904c49c3bea17d53ffb/core/src/main/java/lucee/runtime/tag/Query.java#L783
https://github.com/lucee/Lucee/blob/420e60a670f218ff9887d904c49c3bea17d53ffb/core/src/main/java/lucee/runtime/db/SQLImpl.java#L141

So if I set the log level to trace for the datasource.log then the error is not occurred. The cfquery runs fine and I can confirm this by dump the result attribute value.
But If I try to dump the query result the same error thrown from the dump.cfc file. Please see the both stacktrace.


I added the testcase to this ticket
Pull Request: https://github.com/lucee/Lucee/pull/1984


Stacktrace

lucee.runtime.exp.NativeException: there are more question marks in the SQL than params defined at lucee.runtime.db.SQLImpl.toString(SQLImpl.java:140) at lucee.runtime.tag.Query._doEndTag(Query.java:781) at lucee.runtime.tag.Query.doEndTag(Query.java:566) at test.testcases.ldev4424.test_cfm$cf.call(/test/testcases/LDEV4424/test.cfm:15) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1056) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:948)

 

Stacktrace from the dump.cfc

lucee.runtime.exp.NativeException: there are more question marks in the SQL than params defined at lucee.runtime.db.SQLImpl.toString(SQLImpl.java:140) at lucee.runtime.type.util.QueryUtil.toDumpData(QueryUtil.java:261) at lucee.runtime.type.QueryImpl.toDumpData(QueryImpl.java:1350) at lucee.runtime.dump.DumpUtil.toDumpData(DumpUtil.java:289) at lucee.runtime.functions.other.DumpStruct.call(DumpStruct.java:93) at lucee.runtime.functions.other.DumpStruct.call(DumpStruct.java:82) at dump_cfc$cf.udfCall1(/Dump.cfc:111) at dump_cfc$cf.udfCall(/Dump.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:697) at lucee.runtime.ComponentImpl._call(ComponentImpl.java:584) at lucee.runtime.ComponentImpl.callWithNamedValues(ComponentImpl.java:1950) at lucee.runtime.tag.CFTag.cfcStartTag(CFTag.java:384) at lucee.runtime.tag.CFTag.doStartTag(CFTag.java:178) at test.testcases.ldev4424.test_cfm$cf.call(/test/testcases/LDEV4424/test.cfm:18) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:1056) at lucee.runtime.PageContextImpl._doInclude(PageContextImpl.java:948)

Details

Assignee

Reporter

Priority

Labels

New Issue warning screen

Before you create a new Issue, please post to the mailing list first https://dev.lucee.org

Once the issue has been verified, one of the Lucee team will ask you to file an issue

Affects versions

Created 16 March 2023 at 17:28
Updated 26 July 2024 at 16:19

Flag notifications