CFQUERYPARAM with LIKE and IN Clauses

Posted at 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

coldFusion

If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.

Comments

15 Responses to “CFQUERYPARAM with LIKE and IN Clauses”

Leave Comment

(required)

(required)