tracker issue | What iT iS dESign studios

Title:

Named param replacement

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): Tim Mixell / Tim Mixell (Tim Mixell)

Created: 02/20/2015

Components: Database

Versions: 11.0

Failure Type: Incorrect w/Workaround

Found In Build/Fixed In Build: CF11_Final / CF11 Update7

Priority/Frequency: Trivial / Unknown

Locale/System: English / Windows 7 64-bit

Vote Count: 9

Listed in the version 11.0.07.296330 Issues Fixed doc

Related Bugs:
CF-3944215 - Similar to
CF-3949417 - Similar to


Problem Description: When using named parameters within queryExecute, a parameter that is the substring of another parameter is incorrectly being inserted in the statement where the latter exists.

Steps to Reproduce:

Two parameters declared within a query - :param and :param2 result in the following:

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "2" at line 1, column 48.

			queryExecute(
					"SELECT * FROM art WHERE artID > :param AND artID < :param2",
					{ 	param: { value: 1, cfsqltype: "cf_sql_integer" },
						param2: { value: 10, cfsqltype: "cf_sql_integer" }
					},
					{ datasource: "cfartgallery" }
				);

Expected Result: Named parameters shouldn't do this!

Any Workarounds: Updating the name of the first param so that it is no longer a substring of the second appears to resolve the issue:

			queryExecute(
					"SELECT * FROM art WHERE artID > :param1 AND artID < :param2",
					{ 	param1: { value: 1, cfsqltype: "cf_sql_integer" },
						param2: { value: 10, cfsqltype: "cf_sql_integer" }
					},
					{ datasource: "cfartgallery" }
				);

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

Watson Bug ID:	3943345

External Customer Info:
External Company:  
External Customer Name: Tim
External Customer Email:  
External Test Config: My Hardware and Environment details:

Attachments:

Comments:

We have a lot of instances of columns like "lastModified" and "lastModifiedBy" which means that, if we name the parameters after the columns, this will trip us up.

Vote by External U.

8342 | February 20, 2015 01:32:33 PM GMT

We also have a lot of column names that are a substring of another column name - param & param2, fixing this would make it possible to use the actual column names in our code.

Vote by External U.

8343 | February 20, 2015 01:43:08 PM GMT

Parameters need to be able to have substrings of other parameters! This is a very common practice across languages.

Vote by External U.

8344 | June 30, 2015 07:33:35 AM GMT

I also have lots of similarly named columns. I have already run into this bug and needed to make unsatisfactory workarounds.

Vote by External U.

8345 | June 30, 2015 07:34:19 AM GMT

https://bugbase.adobe.com/index.cfm?event=bug&id=CF-3943345

Vote by External U.

8346 | June 30, 2015 07:40:46 AM GMT

+1 Please explain in 25 characters or more how this bug impacts productivity and why you are adding a vote.

Vote by External U.

8347 | July 01, 2015 12:21:10 AM GMT

I submitted this same problem as a new bug (CF-4014578) before finding yours. Mine was marked as a duplicate.

Tim, would you please upgrade the frequency and/or priority of your bug? 

This is definitely a problem that needs to be addressed by Adobe. The workaround of putting named parameters in a particular order is really cramping my code!

Thank you

Comment by External U.

8334 | July 01, 2015 10:04:12 AM GMT

This issue is fixed and the fix for this issue will be shipped as part of next major release of ColdFusion.

Comment by Nimit S.

8335 | September 23, 2015 12:14:11 AM GMT

+1 ......................

Vote by External U.

8348 | September 23, 2015 01:53:18 AM GMT

@Nimit, thank you for fixing, this bug is a big deal for me. Does "next major release" mean that this bug will be fixed in a CF11 update or will it only be fixed in CF12?

Comment by External U.

8336 | September 23, 2015 09:50:37 AM GMT

Verified this is fixed in CF11 Update 7 (build 11,0,07,296330).

Thanks!,
-Aaron

Comment by External U.

8337 | December 03, 2015 04:07:27 AM GMT

Have had a similar Issue in CF11 Update 12 but only when using a crap ton of parameters - was the fix only for a certain amount of digits?

CF CODE (reduced, not full code) - loops about 500 times to produce 1k params (large update)

note that the last digit of the dump of loc.i is the same as the random digit included in the sql output

for ( loc.i = loc.start; loc.i <= loc.end; loc.i++ ) {
	writeOutput("
		UPDATE 
			Analytics.Imports.CarehomeTrafficData
		SET
			/* #loc.i# */
		       MemberUniqueID = :MemberUniqueID#loc.i#
		WHERE
			TrafficDataID = :TrafficDataID#loc.i#
	");

	 ( loc.i != loc.end ) {
		writeOutput( ";" );
	}
					
	loc.query.params[ "TrafficDataID#loc.i#" ] = {
		 value     = loc.arrQueryItems[ loc.i ].TrafficDataID
		,cfsqltype = "cf_sql_bigint"
	};

	loc.query.params[ "MemberUniqueID#loc.i#" ] = {
		value     = loc.arrQueryItems[ loc.i ].matchingID
		,cfsqltype = "cf_sql_bigint"
	};
}

queryExecute( loc.query.sql, loc.query.params );

SQL PRODUCED - from super far down the error produced (note that the issue only appears at param 19 - the first 18 are fine )

UPDATE Analytics.Imports.CarehomeTrafficData SET /* 1 */ MemberUniqueID = (param 1) WHERE TrafficDataID = (param 2) ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 2 */ MemberUniqueID = (param 3) WHERE TrafficDataID = (param 4) ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 3 */ MemberUniqueID = (param 5) WHERE TrafficDataID = (param 6) ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 4 */ MemberUniqueID = (param 7) WHERE TrafficDataID = (param 8) ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 5 */ MemberUniqueID = (param 9) WHERE TrafficDataID = (param 10) ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 6 */ MemberUniqueID = (param 11) WHERE TrafficDataID = (param 12) ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 7 */ MemberUniqueID = (param 13) WHERE TrafficDataID = (param 14) ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 8 */ MemberUniqueID = (param 15) WHERE TrafficDataID = (param 16) ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 9 */ MemberUniqueID = (param 17) WHERE TrafficDataID = (param 18) ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 10 */ MemberUniqueID = (param 19) 0 WHERE TrafficDataID = (param 20) 0 ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 11 */ MemberUniqueID = (param 21) 1 WHERE TrafficDataID = (param 22) 1 ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 12 */ MemberUniqueID = (param 23) 2 WHERE TrafficDataID = (param 24) 2 ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 13 */ MemberUniqueID = (param 25) 3 WHERE TrafficDataID = (param 26) 3 ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 14 */ MemberUniqueID = (param 27) 4 WHERE TrafficDataID = (param 28) 4 ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 15 */ MemberUniqueID = (param 29) 5 WHERE TrafficDataID = (param 30) 5 ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 16 */ MemberUniqueID = (param 31) 6 WHERE TrafficDataID = (param 32) 6 ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 17 */ MemberUniqueID = (param 33) 7 WHERE TrafficDataID = (param 34) 7 ; UPDATE Analytics.Imports.CarehomeTrafficData SET /* 18 */ MemberUniqueID = (param 35) 8 WHERE TrafficDataID = (param 36) 8 ; UPDATE 

... continues a long long way but i guess you cant post that much text (also note that when you try tracker just returns a 400 with no error shown to the user)

Comment by Chris H.

8338 | August 22, 2017 07:34:34 PM GMT

Had it happening with params using a sequential numeric suffix, and only after #29? Either way, this is still not fixed as of CF2016 u5. Workaround seems to be to just add any alphanumeric char after the numeric suffix. eg, "param#i#X" instead of "param#i#"

Comment by Thomas H.

8339 | October 04, 2017 01:16:41 PM GMT

Hi Chris/Thomas,

Can you please provide the isolated repro case along with the exception stacktrace?

If you are unable to paste lot of content, then I would recommend attaching the repro case and exception as a file.

Comment by Nimit S.

8340 | October 05, 2017 07:03:28 AM GMT

Hi Nimit, there is not a whole lot more code required to recreate the issue in what i posted, ill probably have to get back to you next week if you want a more fleshed out case as a bit busy this week.

Comment by Chris H.

8341 | October 05, 2017 08:16:53 AM GMT

tracker issue : CF-3943345

Named param replacement