UnexpectedBuggy ColdFusion behaviour with CFML statements in <cfquery> tags

G'day:
This is just me editorialising on a bug that someone raised over night: 3585643. The gist of it is that - unexpectedly (?) - CFML statements involving variables which have embedded single quotes (eg: as apostrophes) in them have the single-quotes escaped even if the statement is nothing to do with the SQL string.

"Huh?" you said. Yeah, fair enough, lemme demonstrate:

<cfset test = "   Here's my test string. It's got apostrophes and leading/trailing space   ">
<cfset q = queryNew("col1")>
<cfquery name="data" dbtype="query">
    <cfset trimmed = trim(test)>
    <cfset upper = ucase(test)>
    <cfset trimmedWithPSQ = trim(preserveSingleQuotes(test))>
    SELECT * FROM q
</cfquery>

<cfoutput>
    test: [#test#]<br>
    trimmed: [#trimmed#]<br>
    upper: [#upper#]<br>
    <hr>
    trimmedWithPSQ: [#trimmedWithPSQ#]<br>
</cfoutput>

This outputs:

test: [ Here's my test string. It's got apostrophes and leading/trailing space ]
trimmed: [Here''s my test string. It''s got apostrophes and leading/trailing space]
upper: [ HERE''S MY TEST STRING. IT''S GOT APOSTROPHES AND LEADING/TRAILING SPACE ]
trimmedWithPSQ: [Here's my test string. It's got apostrophes and leading/trailing space]

NB, before I go on: this is only a ColdFusion thing... this does not happen on either of Railo or OpenBD (I was pleased for once my test code actually ran on OpenBD's version of CFML!).

We all know that CFML messes with single-quotes in variables used in the SQL string in <cfquery> (well I hope we do!), and the way to solve this is to use preserveSingleQuotes() to make it not mess with them. To me this has always been an arse-backwards approach to solving a problem that existed mostly in the minds of Allaire's CF Team, but we're stuck with it now. This has been how CF has worked for as long as I've been using it (CF5, for all intents and purposes).

Anyway, note that in the example above there's two statement which simply set a variable, but the variable is in no way used in the SQL string that CF ought to be interested in as far as messing up single quotes goes. Yet they are still messed with. Fail.

Also note that if one simply uses preserveSingleQuotes() in these sorts of expressions, then there is no issue. Other than it being daft to have to do so.

I've tested this on CF5, CFMX7.0.2, CF9.0.2 and CF10.0.10, and CF5 does not behave like this, but all the others do.

TBH, I think having unrelated CFML within <cfquery> tags is a practice with little merit, but I guess if one is building the SQL statement dynamically within the <cfquery> (a practice to be avoided, IMO) then it's perhaps unavoidable. So I guess this falls under the category of "to be aware of" (both the situation, and that preserveSingleQuotes() works around it), but "you kinda bring it upon yourself if you fall foul of it".

That said, it's stupid behaviour, so it should be fixed. I can hear Adobe now using "backwards compat!" as an excuse to not do it, but this does not wash with me. Clearly CF5 is the intended (and sensible) behaviour, and it was broken by CFMX.

Update:

OK, the behaviour is more stupid than I thought. James Moberg put me onto some more info. If one uses a UDF rather than a built-in function, one does not get the single-quotes doubled up. I updated my code thus:

<cfset test = "   Here's my test string. It's got apostrophes and leading/trailing space   ">
<cfset q = queryNew("col1")>
<cfquery name="data" dbtype="query">
    <cfset trimmed = trim(test)>
    <cfset upper = ucase(test)>
    <cfset trimmedWithPSQ = trim(preserveSingleQuotes(test))>
    <cfset myTrimmed = myTrim(test)>
    <cfset myUpper = myUCase(test)>
    SELECT * FROM q
</cfquery>

<cfoutput>
    test: [#test#]<br>
    <hr>
    trimmed: [#trimmed#]<br>
    upper: [#upper#]<br>
    <hr>
    trimmedWithPSQ: [#trimmedWithPSQ#]<br>
    <hr>
    myTrimmed: [#myTrimmed#]<br>
    myUpper: [#myUpper#]<br>
</cfoutput>

<cfscript>
    function myTrim(){
        return trim(arguments[1]);
    }

    function myUCase(){
        return uCase(arguments[1]);
    }
</cfscript>

And the relevant output is:

myTrimmed: [Here's my test string. It's got apostrophes and leading/trailing space]
myUpper: [ HERE'S MY TEST STRING. IT'S GOT APOSTROPHES AND LEADING/TRAILING SPACE ]

Initially I was thinking "weeellll... it's just that preserveSingleQuotes() requirement manifesting where I didn't expect it to, and it might be legit". Now it just seems like shonkiness. It either should be escaping the single-quotes, or shouldn't. Not sometimes yes, sometimes no.

Anyway, that's it.

--
Adam