http://coldfusion.com logo
#cfml-general
Title
# cfml-general
s

Simone

04/15/2022, 3:06 PM
i am trying to use cfqueryparam in rereplace to replace the value using cfqueryparam any sample how can i do it i don't want to use directly like cfqueryparam , i am using in cfsavecontent and using in the functions where needed, here is my code
Copy code
<cfif len(trim(arguments.sSearch))>
				AND
				    ( 
				    <cfloop list="#arguments._listColumns#" index="thisColumn">
				    <cfif thisColumn neq listFirst(arguments._listColumns)> 
				    OR 
				    </cfif>
				    	#thisColumn# LIKE '%#trim(arguments.sSearch)#%'   
				    </cfloop>
				    )
				</cfif>
m

Michael Schmidt

04/15/2022, 3:11 PM
Can you please supply a full code snippet of what you are trying to do? but one thing is things like column names can't be query param'd so you definitely want to make sure the value of thisColumn is contained in a white list of valid values.
if using SQL server and if sSearch is always static i do soemthing like this....
DECLARE @sSearch varchar(max) = <cfqueryparam cfsqltype="cf_sql_varchar" value="%#Trim(arguments.sSearch)#%">
Copy code
<cfif len(trim(arguments.sSearch))>
				AND
				    ( 
				    <cfloop list="#arguments._listColumns#" index="thisColumn">
				    <cfif thisColumn neq listFirst(arguments._listColumns)> 
				    OR 
				    </cfif>
				    	#thisColumn# LIKE @sSearch  
				    </cfloop>
				    )
				</cfif>
but you still must!!! make sure that thisColumn is a valid column name and not junk before proceeding
s

Scott Bennett

04/15/2022, 3:21 PM
I would do something like what michael posted above, but since it looks like you might have potentially have more than one variable, you would need to also build a list of variables to declare in your actual query. In your loop where you are generating your cfsavecontent, you could also build an array of structs to define the params names and values, and even datatype if you have other data types. then in your query at the beginning you can dyncamically create all the declare statements to create the sql variables and cfqueryparam them.
s

Simone

04/15/2022, 3:30 PM
btw i am using mysql
m

Michael Schmidt

04/15/2022, 3:35 PM
It looks like for mysql you don't need to DECLARE a variable you can just set it...
SET @sSearch varchar(max) = <cfqueryparam cfsqltype="cf_sql_varchar" value="%#Trim(arguments.sSearch)#%">
s

Scott Bennett

04/15/2022, 3:41 PM
Nevermind on my previous comment btw... I see now you are just searching multiple columns for the same search value. For filtering your columns:
Copy code
<cfset goodcolumnlist = "column1,column2,col3">

<cfsavecontent variable="somefilters">
    <cfif len(trim(arguments.sSearch))>
				AND
				    ( 
				    <cfset useOr = "">
				    <cfloop list="#arguments._listColumns#" index="thisColumn">
				    <cfif listfindnocase(thisColumn, goodcolumnlist)>
				    	#useOr# #thisColumn# LIKE @sSearch  
				    	<cfset useOr = "OR">
    				</cfif>
    				</cfloop>
				    )
				</cfif>
</cfsavecontent>
(or if you arguments._listColumns is already hardcoded earlier, or otherwise not being set by user input then you can just ignore that)
a

Adam Cameron

04/15/2022, 4:10 PM
i am trying to use cfqueryparam in rereplace to replace the value using cfqueryparam
any sample how can i do it
Seriously... what??? What is that supposed to mean? Do you ever re-read your questions before you press send and think "will this make any sense to anyone other than me?"
🤣 4
👎 1
s

Scott Bennett

04/15/2022, 4:43 PM
Yeah, the question was very unclear and didn't really ask what you really wanted to ask... I was able to guess what you were trying to do based on the sample code, but I had to read it a few times to figure that out too.
d

David Buck

04/18/2022, 1:53 PM
If I understand correctly, you are using
<cfsavecontent>
to create a SQL string, and you want to know how to parameterize the client variables since you can't use
<cfqueryparam>
outside of
<cfquery>
. Right? You might want to consider using the queryExecute() function instead of
<cfquery>
for this sort of thing. The queryExecute function allows you to define the parameters outside of the query string. Below is an example that I copied from the Adobe docs. It contains 1 parameter, named country.
Copy code
QueryExecute("select from Employee where country=:country and citizenship=:country", {country='USA'});
The first argument is the query string, which uses a placeholder variable (:country) for the parameter, and the second argument is a struct in which the parameter is actually defined. Read the full documentation for more info.
2
2 Views