CODEWITHSUNDEEP
coldfusioncoldfusion-8cfml
rrk
rrk

Reputation: 15875

Cfqueryparam'd Query generation from function

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?

Upvotes: 1

Views: 509

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

This is in response to "Is there anyway we can add cfqueryparam dynamically to a query?". There is, but you have to do it inside the cfquery block. Something like this is ok:

<cfquery>
    select SomeFields
    from SomeTables
    where 1 = 1
    <cfif something>
        and somefield = <cfqueryparam value="#SomeVariable#">
    </cfif>
</cfquery>

What you can't do, at least not on version 9 and lower, is to make query parameters part of a variable. In other words, this will not compile.

WhereClause = "where 1=1";
if (something)
WhereClause &= ' and somefield = <cfqueryparam value="#SomeVariable#">';

This is more or less what you were attempting.

Upvotes: 2

Adam Cameron
Adam Cameron

Reputation: 29870

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.

Upvotes: 5

Related Questions