tracker issue : CF-3916658

select a category, or use search below
(searches all categories and all time range)
Title:

Invalid / Strange casting on cf_sql_integer

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/Duplicate

Reporter/Name(from Bugbase): JF Robichaud / JF Robichaud (jfrobishow)

Created: 01/06/2015

Components: Database

Versions: 11.0

Failure Type: Data Corruption

Found In Build/Fixed In Build: CF11_Final /

Priority/Frequency: Normal / Most users will encounter

Locale/System: ALL / Win 2008 Server R2 64 bit

Vote Count: 1

Duplicate ID:	CF-3784311

Problem Description: Passing a comma seperated list of value to cf_sql_integer will do an internal cast to a type I couldn't identify resulting in data corruption in the query.

Steps to Reproduce:

<cfset x = "1,100">

<cfdump var="#isNumeric(x)#">  <!--- FALSE --->
<cfdump var="#isValid("integer", x)#"> <!--- TRUE before CF 11, CF 11 FALSE which is expected --->

<cfquery name="myquery" datasource="yourdatasource">
  create table ##tmp (col1 int)
  
  insert into ##tmp (col1) values(<cfqueryparam cfsqltype="cf_sql_integer" value="#x#">)

  select * from ##tmp
  
  drop table ##tmp
</cfquery>

<cfdump var="#myquery#">

Actual Result:

-657436 is inserted in the table even though we have validated that the value passed to cfqueryparam is neither numeric or an integer.

Expected Result: Invalid data type exception

Any Workarounds: No

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3916658

External Customer Info:
External Company:  
External Customer Name: jfrobishow
External Customer Email:  
External Test Config: My Hardware and Environment details: Using a MS SQL 2012 datasource with the default driver.

Attachments:

Comments:

There is an internal cast happening at some level, not all comma list will result in a valid integer.
Comment by External U.
9113 | January 06, 2015 10:35:35 AM GMT
FYI - Railo handles this correctly and throw "cannot cast [1,100] string to a number value"
Comment by External U.
9114 | January 06, 2015 10:40:13 AM GMT
+1 - <cfquery>INSERT INTO mytable (myinteger) VALUES (<cfqueryparam cfsqltype="cf_sql_integer" value="1,100">)</cfquery> should throw an exception like "cannot cast [1,100] string to a number value" instead of inserting -657436 like it currently does
Vote by External U.
9116 | January 06, 2015 10:21:27 PM GMT
This issue is fixed as part of bug #CF-3784311.
Comment by Nimit S.
9115 | January 07, 2015 12:41:34 AM GMT