Cfqueryparam'd Query generation from function

Question

There is a search query, and a function I am using to generate query elements

<cffunction name="GetSearchQuery" output="true" returntype="string" access="public">
    <cfargument name="arrayName" type="array" required="yes">
    <cfargument name="columnName" type="string" required="yes">
    <cfargument name="searchtype" type="string" required="no" default="wildcard">
    <cfset var o = "">
    <cfset var i = "">
    <cfset var search_item = "">
    <cfset search_item = "(">
    <cfloop from="1" to="#ArrayLen(Arguments.arrayName)#" index="o">
        <cfif Arguments.arrayName[o][1] EQ #Arguments.columnName#>
        <cfloop from="2" to="#ArrayLen(Arguments.arrayName[o])#" index="i">
            <cfset search_item = search_item & #Arguments.columnName#>
            <cfswitch expression="#Arguments.searchtype#">
                <cfcase value="wildcard">
                    <cfset search_item = search_item & ' LIKE 
                    <cfqueryparam value="%' & #Arguments.arrayName[o][i]# & '%"> AND '>
                </cfcase>
                <cfcase value="startswith">
                    <cfset search_item = search_item & ' LIKE 
                    <cfqueryparam value="' & #Arguments.arrayName[o][i]# & '%"> AND '>
                </cfcase>
                <cfcase value="endswith">
                    <cfset search_item = search_item & ' LIKE 
                    <cfqueryparam value="%' & #Arguments.arrayName[o][i]# & '"> AND '>
                </cfcase>
                <cfcase value="exactmatch">
                    <cfset search_item = search_item & ' = 
                    <cfqueryparam value="' & #Arguments.arrayName[o][i]# & '"> AND '>
                </cfcase>
            </cfswitch>
        </cfloop>
        </cfif>
    </cfloop>
    <cfif Len(search_item) GT 4>
    <cfset search_item = Left(search_item, Len(search_item)-4) & ") ">
    </cfif>
    <cfreturn search_item>
</cffunction>

And then call it like this in the query

SELECT * FROM #request.tablename#
  WHERE #utilObj.GetSearchQuery(arrsearch, "photonumber", true)# OR
        #utilObj.GetSearchQuery(arrsearch, "takenby", true)# OR 
        #utilObj.GetSearchQuery(arrsearch, "category", true)# OR 
        #utilObj.GetSearchQuery(arrsearch, "area", true)# OR 
        #utilObj.GetSearchQuery(arrsearch, "description", true)#

But it causes an error in the query

But without cfqueryparam in the function this woks fine.
eg. <cfset search_item = search_item & ' LIKE "%' & #Arguments.arrayName[o][i]# & '%" AND '>

Is there anyway we can add cfqueryparam dynamically to a query?

Answer 1

You can't create a string containing CFML and output it and expect that to somehow mean it'll be actually executed.

For one thing, that's a bit daft when you stop and think about it, innit? (sorry, I don't mean that in a mean-spirited way). And don't feel bad: I reckon we've all done this at some stage.

Secondly: CFML is compiled before it's executed. So the process is (for all intents and purposes):

1. File containing code is requested
2. Code from file is passed to the CF compiler
3. CF compiler spits out java byte code
4. JVM executes java byte code

So your code to generate the string with the CFML code is not executed until (4), but it is needed back at (2). Unless you can time travel, that ain't gonna work.

I discuss this in my blog: "The ColdFusion request/response process"

There's a coupla things you can do:

1. don't write dynamic generic SQL like this. All of us do it when we first start, but quickly come to realise dynamic/generic SQL is never a good solution to whatever issue is at hand.
2. Use one of the already-existing DB abstraction tiers out there. CF 9+ comes with Hibernate support baked in.
3. If you use Query.cfc instead of <cfquery>, you can put placeholders in for the parameters, and pass the parameter data into the query separately.
4. Write your dynamic code to disk, then include it. This'll subvert the compile-time/run-time thing. It will be slow, as your included file will need to be compiled before it will run. It's ugly.

That list is in my order of preference for dealing with this issue.