CFQUERYPARAM with LIKE and IN Clauses

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 = 

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:




SELECT *
FROM tblCustomers
WHERE customer_id IN ( )

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:



SELECT *
FROM tblCustomers
WHERE customer_lastname LIKE '%' + 

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