Skip to:

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

Query with :: and passed params fails

Description

:: is a Cast Operator in Postgres, e.g. current_timestamp::text is a shorthand for cast(current_timestamp as text).

But when Lucee sees that operator and params are passed, it tries erroneously to parse the operator as a param and fails with an error.

So while this works as expected:

sqlStmt = " select current_timestamp::text as curr_ts; "; query name="q" sql=sqlStmt;

This fails with error:

sqlStmt = " select current_timestamp::text as curr_ts; "; params = {}; query name="q" sql=sqlStmt params=params;

lucee.runtime.exp.DatabaseException: ERROR: syntax error at or near ":"
  Position: 26
  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
  at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
  at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
  at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:303)
  at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:289)
  at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:266)
  at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:262)
  at lucee.runtime.type.util.QueryUtil.execute(QueryUtil.java:319)
  at lucee.runtime.type.QueryImpl.execute(QueryImpl.java:274)
  at lucee.runtime.type.QueryImpl.<init>(QueryImpl.java:228)
  at lucee.runtime.tag.Query.executeDatasoure(Query.java:1125)
  at lucee.runtime.tag.Query._doEndTag(Query.java:688)
  at lucee.runtime.tag.Query.doEndTag(Query.java:554)
  at test_cfm$cf$h.call(/test.cfm:32)

Environment

None

Activity

Show:

Zac Spitzer 23 November 2021 at 11:00

one approach would be allowing specifying which characters are use for param markers? i.e only : or ?

but, if there are no passed params (i.e. an empty struct), there’s also zero need to parse for parameter markers in the sql

Igal Sapir 22 November 2021 at 18:32

A better solution might be to allow :: in the parser

Igal Sapir 1 December 2020 at 02:44

I now remember that this was changed in https://luceeserver.atlassian.net/browse/LDEV-1063 to allow escaping the : with :: but TBH I’m not sure that was the best approach. It makes the queries less readable.

Pothys - MitrahSoft 23 November 2020 at 14:31

I've checked this ticket and confirm the issue happened in lucee latest version 6.0.0.13-SNAPSHOT also.
Yes, Above the SQL was works fine without params, But It throws an error with params as same as above said. Seems ACF works fine.

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 21 November 2020 at 03:10
Updated 23 November 2021 at 11:00

Flag notifications