tracker issue : CF-3985498

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

The product of listLast(x) and listFirst(x) changes in the context of cfquery and outside the context of cfquery

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/AsDesigned

Reporter/Name(from Bugbase): Stephen Johnson / Stephen Johnson (Stephen Johnson)

Created: 05/11/2015

Components: Language

Versions: 11.0

Failure Type:

Found In Build/Fixed In Build: CF11_Final /

Priority/Frequency: Major / All users will encounter

Locale/System: English / Win 2012 Server x64

Vote Count: 0

Problem Description: My expectation is that listFirst() and listLast() should behave like a static idempotent subroutine. And yet both listLast(x) and listFirst(x) produce different results depending on whether those functions exist inside a cfquery and outside a cfquery. Assume these functions are called within a cfquery. If the input (x) into either function contains a single quote, the product will include 2 single quotes. This problem exists in CF9, CF10, and CF 11. And no, this side effect is not a feature, it's a major bug.

Steps to Reproduce:

<b>Outside cfquery:</b><br>
<cfset company = "'I'BM'">
<cfset thisisright = ListFirst(company)>
<cfset thisisreallyright = ListLast(company)>
<cfoutput>#thisisright#</cfoutput><br>
<cfoutput>#thisisreallyright#</cfoutput>
<br><br>
<b>Inside cfquery:</b><br>
<cfquery name="qtest" datasource="webd">	
	select * from dual where 1 = 2	
	<cfset thisaintright = ListFirst(company)>
	<cfset thisreallyaintright = ListLast(company)>
</cfquery>
<cfoutput>#thisaintright#</cfoutput><br>
<cfoutput>#thisreallyaintright#</cfoutput>

Actual Result:

Outside cfquery:
'I'BM'
'I'BM' 

Inside cfquery:
''I''BM''
''I''BM''

Expected Result:

Outside cfquery:
'I'BM'
'I'BM' 

Inside cfquery:
'I'BM'
'I'BM'

Any Workarounds:

Don't use listLast() and listFirst().

Stephen Johnson
Adobe Advanced ColdFusion Certified Developer (ADB184437)
Mentor Graphics Inc.

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

Watson Bug ID:	3985498

External Customer Info:
External Company:  
External Customer Name: Stephen
External Customer Email:  
External Test Config: Confirmed on Windows 7, ColdFusion 9,0,1,274733

and Windows Server 2012, ColdFusion  11,0,03,292480



Oracle 11g

Attachments:

Comments:

Update: This side effect also occurs with uCase(x) when doing variable assignment in a cfquery. So I'm apt to believe that any string manipulation functions called within a cfquery where the product is assigned to a variable will have this effect. I understand the need to escape single quotes within a cfquery, but that should be explicitly handled via preserveSingleQuotes() and <cfqueryparam>, not side effecting.
Comment by External U.
7471 | May 11, 2015 11:37:10 AM GMT
Sorry, Stephen: this is not a bug. Any string-yielding dynamic expression withing <cfquery> tags has any single quotes escaped. This is by design. It's not very clear, but it's documented behaviour: https://wikidocs.adobe.com/wiki/display/coldfusionen/PreserveSingleQuotes I've got edit privileges to that page, so I'll improve it. -- Adam
Comment by External U.
7472 | May 11, 2015 02:24:24 PM GMT
Interesting...I'm sure I'm not alone in assuming that the need for preserveSingleQuotes() is to protect variable references within the actual SQL body at runtime, not outside that body. You might want to document this on <cfquery> and not preserveSingleQuotes(). Thanks for the quick response.
Comment by External U.
7473 | May 11, 2015 03:50:04 PM GMT
"the need for preserveSingleQuotes() is to protect variable references within the actual SQL body at runtime" Which is where you're seeing the issue. So by your own admission... shouldn't've surprised you ;-) I guess the poor wording is that Adobe say "variable", not "expression". -- Adam
Comment by External U.
7474 | May 11, 2015 03:53:47 PM GMT
By SQL body I mean "in the SQL". Ex: select * from dual where name in (#preserveSingleQuotes#) These expressions are occurring outside the actual SQL body that gets passed to the RBMS at runtime. This is what surprises me and I've been writing CFML for 18 years. Also note that this behavior does not occur Railo. Running the above code in Railo yields the results I would expect.
Comment by External U.
7475 | May 11, 2015 04:22:51 PM GMT
<cfquery name="qtest" datasource="webd"> select * from dual where 1 = 2 <cfset thisaintright = ListFirst(company)> <cfset thisreallyaintright = ListLast(company)> </cfquery> That's INSIDE the "SQL body". ListFirst(company) (etc) is an expression within the <cfquery> tags. Single quotes within it get escaped. Bear in mind that "at runtime" CF has to pass *a string* to the DB driver, so all CFML of any sort has to be processed before the result gets anywhere near any of the driver's methods. You *could* make an argument that CF is doing this escaping at the wrong time... those expressions are not used within the SQL string, so shouldn't be "escaped". I wonder if there's a logical way for the parser to determine this. It does - on reflection - need to be done @ parse time, not runtime, I think (perhaps decompile the code and check?). As for Railo: that's down to a setting in Railo Admin which it seems you have unchecked (the default). C'est la vie. 18yrs experience or 18hrs is irrelevant, I'm not trying to sway your opinion, I'm explaining what the situation is. Personally I think it's shite that CF does special escaping like this in the first place, but it's been in CFML for longer than I've been using it, and perhaps even as long as you've been doing it. [gallic shrug]
Comment by External U.
7476 | May 11, 2015 04:41:20 PM GMT
Thanks Adam It is what it is and it seems to have been that way since MX. Thanks for all your help. - Steve
Comment by External U.
7477 | May 11, 2015 04:49:38 PM GMT
As has been documented and also rightly commented by Adam, this has existed in the language since its inception. One will have to use PreserveSingleQuotes if one wants to preserve the quotes. Let us know if you have a stronger use case for this as we have not heard from many folks asking for the same. Thanks!
Comment by Suchika S.
7478 | June 05, 2015 01:04:04 AM GMT
Please close ticket. It is what it is.
Comment by External U.
7479 | June 08, 2015 10:27:40 AM GMT
Thanks Stephen! Closing the bug!
Comment by Suchika S.
7480 | June 09, 2015 12:56:06 AM GMT