CFQUERYPARAM with LIKE and IN Clauses
July 23, 2008
ColdFusion’s CFQUERYPARAM tag is important for several reasons, most notably data type validation, improved query performance, and foiling SQL injection attacks.
Basic usage is simple:
SELECT *
FROM tblCustomers
WHERE customer_id = <cfqueryparam value="#FORM.custID#" cfsqltype="cf_sql_integer">The cfsqltype parameter validates the passed value before it is sent to the database server. Once validated, the query is sent to the database with a placeholder where the criteria would otherwise be. The criteria itself is passed separately and plugged in to the query where the placeholder exists. This allows the query to be cached by the database server, improving performance the next time the query is run.
CFQUERYPARAM also prevents SQL injection attacks that attempt to trick your code into running an extra query “injected” into the URL. SQL Injection attacks can do anything from modifying data in your tables to dropping your tables entirely if you don’t take preventative measures.
For further detail on CFQUERYPARAM, check out this post by Ben Forta.
Now, back to the point of my post. I simply want an easy place to find the correct syntax for a couple of the trickier clauses, namely “IN” and “LIKE”. I find myself researching the syntax repeatedly and it always seems time-consuming to find a post describing the correct method when I need it. So here they are:
IN Clause
The IN clause is used to find all of the records that match the criteria in a comma separated list of values. The SQL syntax is as follows:
SELECT *
FROM tblCustomers
WHERE customer_id IN (12345, 54321, 22222)The query above will find customer records with IDs of 12345, 54321, or 22222.
To use cfqueryparam, place the parentheses outside the tag and use the “list” parameter:
<cfset IDLIST = "12345, 54321, 22222" >
<cfquery name="q" datasource="myDS">
SELECT *
FROM tblCustomers
WHERE customer_id IN ( <cfqueryparam value="#IDLIST#" cfsqltype="cf_sql_integer" list="yes">)
</cfquery>LIKE Clause
The LIKE clause uses % as a wildcard symbol to find records that contain a certain string of text. The SQL syntax for the LIKE clause is as follows:
SELECT *
FROM tblCustomers
WHERE customer_lastname LIKE '%ski'This query will find all of the customers whose last name ends in ’ski’.
To use cfqueryparam, you concatenate the percent sign to the tag, like this:
<cfquery name="q" datasource="myDS">
SELECT *
FROM tblCustomers
WHERE customer_lastname LIKE '%' + <cfqueryparam value="ski" cfsqltype="cf_sql_varchar">
</cfquery>Note that, while my example uses the wildcard at the beginning of the text string, it can also be used on both sides, or at the end only.
-rG
Posted in coldFusion
content rss
July 23rd, 2008 at 10:22 am
Just as an addendum, you can use the wildcard symbols inside of the value attribute (i.e. ) if you hate using concatenation, as I do.
July 23rd, 2008 at 10:38 am
Good tips. Note also that you can put the percent sign directly inside the value of cfqueryparam.
July 23rd, 2008 at 10:38 am
@Kyle & Steve,
Thanks for the comment. I thought that initially myself but I’ve actually run into problems placing the wildcard inside the value attribute. The concat method seems to work more consistently and is the preferred method in the other posts that I’ve seen.
July 23rd, 2008 at 11:16 am
Good post… make everyone’s life easier by linking to the print version of the sys-con site though:
http://coldfusion.sys-con.com/read/41712_p.htm
July 23rd, 2008 at 11:22 am
@Duncan: Thanks for the suggestion. The change has been made.
July 23rd, 2008 at 11:32 am
When dealing with the list=”true” attribute of the CFQueryParam tag, I have often found it good to add a “,0″ to the value attribute:
value=”#IDList#,0″
The reason for this is that if IDList is empty then the value will be :
value=”,0″
And since, ColdFusion ignores empty list items, it really get evalutated as:
value=”0″
Now, why go through that? Cause if the ID list is empty and you don’t have anything else there (ex. value=”"), SQL will throw a syntax error. Sure, there will be times that you *know* an ID list will always have values; but, I have found that this is a good habit to form.
July 23rd, 2008 at 11:44 am
@Ben: Thanks for the tip. I can’t tell you how many times I’ve found solutions on your site, Kinky Solutions. Truly a great resource for ColdFusion information.
July 23rd, 2008 at 12:06 pm
Great set of tips. I was literally just struggling with the same issue and stumbled on this post by chance - thanks a lot, you just saved me a fair bit of time!
July 23rd, 2008 at 12:20 pm
[...] unneccessarily link off to other blog entries that are syndicated on the same aggregator, but this post at Rabidgadfly regarding the use of LIKE and IN and CFQUERYPARAM is just too useful to pass [...]
July 23rd, 2008 at 1:27 pm
moderately related — Null=”#YesNoFormat(NOT LEN(MyVar))#” (I don’t think the YesNoFormat() is necessary in CF8) is also handy.
July 23rd, 2008 at 8:37 pm
Doing this:
LIKE ‘%’ +
Seems quite clunky compared to simply doing this:
LIKE
There’s no need for the string concatenation operation.
–
Adam
July 23rd, 2008 at 8:38 pm
Oh for goodness sake. You should get your blog to escape the submitted text. I’ll try again.
Doing this:
LIKE ‘%’ + <cfqueryparam value=”ski” cfsqltype=”cf_sql_varchar”>
Seems quite clunky compared to simply doing this:
LIKE <cfqueryparam value=”%ski” cfsqltype=”cf_sql_varchar”>
There’s no need for the string concatenation operation.
–
Adam
July 24th, 2008 at 1:18 am
[...] Using cfqueryparam with LIKE and IN Clauses [...]
July 24th, 2008 at 9:45 am
@JC: yesNoFormat wasn’t necessary on 7 either.
@Everyone else: I agree - put the % inside the value. Not all dbms handle concatenation the same way so the safest bet is to throw the wildcard operator inside the value attribute.
December 4th, 2008 at 12:52 pm
What about LIMIT 6 in a query. Does the 6 have to be put in a cfqueryparam tag before the query will be cached in the DB correctly?