Skip to:

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

CFQueryParam Somehow Able to Circumvent SQL Query Error With Large Number

Description

Using a large number in a parameter in a database query which should cause a database error is not causing an error.

In the example below the 2nd and 3rd queries fail as expected in CF (and when ran directly in a database client) but only the 2nd one fails in Lucee and the parameterized one seems to somehow work:

 

<cfset datasourceName = 'DATA SOURCE'> <cfset numero = 1000000000000000000000000000000000000000> <cftry> <cfquery name="one" datasource="#datasourceName#"> SELECT '#numero#' </cfquery> <cfdump var="#one.recordCount# record"> <cfcatch> <cfdump var="#cfcatch.message# 1"> </cfcatch> </cftry> <br /> <cftry> <cfquery name="two" datasource="#datasourceName#"> SELECT #numero# </cfquery> <cfdump var="#two.recordCount# record"> <cfcatch> <cfdump var="#cfcatch.message# 2"> </cfcatch> </cftry> <br /> <cftry> <cfquery name="three" datasource="#datasourceName#"> SELECT <cfqueryparam value="#numero#" cfsqltype="cf_sql_numeric"> </cfquery> <cfdump var="#three.recordCount# record"> <cfcatch> <cfdump var="#cfcatch.message# 3"> </cfcatch> </cftry>

 

Checked first on 5.3.8.201 which we use and 5.3.9.141 which I dev on locally

Environment

Windows / Default Lucee installation

Activity

Show:

Zac Spitzer 14 June 2023 at 16:25

Pothys - MitrahSoft 10 August 2022 at 07:17
Edited

I checked this ticket and confirmed the issue happened on the lucee latest version 5.3.10.51-SNAPSHOT. Yes, when using a large number value in a query with queryparam didn’t throw an error and without using queryparam lucee throws an error like The number '99999999999999980000000000000000000000000' is out of the range for numeric representation (maximum precision 38).

But In Lucee 6.0 when using a large number value in query with/without queryparam didn't throw an error.
(please see the two tests failed in 6.0 https://github.com/lucee/Lucee/runs/7761202929?check_suite_focus=true#step:20:1940 )

 

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

Zac Spitzer 9 August 2022 at 11:21

please always post to the mailing first, please don’t just go and create tickets

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

Sprint

Affects versions

Created 9 August 2022 at 11:14
Updated 5 February 2025 at 12:45

Flag notifications