Reputation: 15875
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
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
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):
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:
Query.cfc
instead of <cfquery>
, you can put placeholders in for the parameters, and pass the parameter data into the query separately.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