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

17 thoughts on “CFQUERYPARAM with LIKE and IN Clauses

  1. 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. 😛

  2. @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.

  3. 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.

  4. moderately related — Null=”#YesNoFormat(NOT LEN(MyVar))#” (I don’t think the YesNoFormat() is necessary in CF8) is also handy.

  5. Doing this:

    LIKE ‘%’ +

    Seems quite clunky compared to simply doing this:

    LIKE

    There’s no need for the string concatenation operation.


    Adam

  6. 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

  7. @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.

  8. 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?

  9. When I use cfqueryparam to SQL view tables, getTickCount() gives me 5 seconds more.

    does that mean, cfqueryparam is not efficient for sql view tables.

    1. @damiththa: If anything, cfqueryparam should improve performance because it’s submitting a parameterized query. This prevents SQL injection and allows most databases to cache the query plan.

Comments are closed.