Hi hoping someone can give me some info on the usage of cfset within a cfquery tag. I've come across something I wasn't aware of. (Using CF2021) If a cfset is called within a cfquery tag it escapes an apostrophe with a double apostrophe but the same cfset outside the tag does not result in this. When that variable is then used with a cfqueryparam the extra apostrophe is actually breaking the expected result. e.g.

x = findPerson(searchTerm="John's Stuff");

    ...    

    <cffunction name="findPerson">
        <cfargument name="searchTerm" type="string" required="yes" hint="John's Stuff" >

        <cfquery name="local.mainQ" datasource="somedatasource">

            SELECT  person.Lastname
            FROM    person
            WHERE   1=1
                    <cfset local.ownername = TRIM(arguments.searchTerm)><!---results in double apos e.g. John''s Stuff--->
                    AND person.Lastname LIKE <cfqueryparam value="%#local.ownername#%">

        </cfquery>

        <cfreturn local.mainQ>

    </cffunction>

vs

    <cffunction name="findPerson">
        <cfargument name="searchTerm" type="string" required="yes" hint="John's Stuff" >

        <cfset local.ownername = TRIM(arguments.searchTerm)><!---John's Stuff. As expected--->
        <cfquery name="local.mainQ" datasource="somedatasource">

            SELECT  person.Lastname
            FROM    person
            WHERE   1=1
                    
                    AND person.Lastname LIKE <cfqueryparam value="%#local.ownername#%">

        </cfquery>

        <cfreturn local.mainQ>

    </cffunction>

We use cfqueryparams everywhere that's possible and for rare cases where it doesn't suit we usually just have a safe list. Is there a setting to disable the above or is just terrible that in some functions we have a cfset at all within the cfquery tag.

Use preserveSingleQuotes() - https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/preservesinglequotes.html

Hi Adam, thank you for the reply. You know I tried that earlier and it didn't work but after you said it again I went back to make sure and redid it and it worked this time. I can only assume I mucked up earlier and didn't put it in the place for the first assignment (the real code had a bit more going on than my example). Thank you for your help

Why are you setting a var in the query only to use it in the param after it? Why not just do what is in the value of the set in the param?

the code I pasted was just an example to highlight how the issue occurred. The real code was doing a bit more than just trimming a value which would be too messy to just be inline as part of the queryparam (cleaning it up, splitting into multiple vars depending on what was set etc.). As to why that was within a cfquery tag, it really shouldn't have been but that's just the way it was.

I would create a private function to do what you need. I wouldn't put complex, not sql code, inside a query.

I second dfgrumpy and would recommend that any complex variable manipulation be done before the cfquery. I usually only use cfqueryparams within a cfquery.... occasionally I will add in some conditional logic, but I have never used a cfset within a query... something about that just feels wrong to me.

Agreed. it reduces the testability of code to have logic munged in with a DB call: given the way

<cfquery>

works it pretty much forced one's tests to require the DB, which in turn is a barrier to entry for tests. It's also less than ideal in the "single responsibility principle" department, so reducing the maintainability and understandability of the code. The method that makes the DB call should do nothing else other than the DB call. I'd avoid using

<cfquery>

if I needed to have conditional logic to build the SQL string; I'd separate that out into its own thing, and just use

queryExecute

to run the SQL . (that said, I really try to avoid having to write dynamically-constructed SQL strings anyhow. I do know it's unavoidable sometimes)