by Victory Design

SQL Queries Using cfqueryparam

The cfqueryparam can be used to secure ColdFusion queries from unauthorized users or those who may try and pass malicious strings to a SQL query.

The tag is also useful for SQL queries using lists and under some conditions enhances performance when interacting with databases.

Using cfqueryparam to Enhance Security
Malicious strings can be passed to your database like this:

The SQL query would therefore be:

FROM   customer
WHERE  custid = 23 DELETE FROM customers

The result would be that the entire database table would be deleted.

To protect your database using the cfqueryparam tag use this syntax

FROM   customers
WHERE  cuid = <cfqueryparam value="#cuid#"

Of course when the string passed is numerical you can use the Val() function to strip off mailicious code.

FROM   customer
WHERE  custid = #Val(custid)#

Using cfqueryparam with strings

FROM   customer
WHERE  name = <cfqueryparam value="#name#"
                cfsqltype="cf_sql_char" maxLength="15">

cfqueryparam performs the following checks:
  1. It ensures that Name contains a string.
  2. It ensures that the string is 15 characters or less.
  3. It escapes the string with single-quotation marks so that it appears as a single value to the database. Even if a hacker passes a bad URL, it appears as follows:
    WHERE name = 'Bob DELETE FROM customer'.

Using cfqueryparam for Lists Queries
The cfqueryparam tag is particularly useful for queries involving lists as it escapes the list variable in a text string with single quotes. For example if your list string (mylist) is "Bob,Mary,Julie"

FROM   customer
WHERE  name IN (<cfqueryparam value="#mylist#"
                cfsqltype="CF_SQL_VARCHAR" list="yes">)

This would produce the equivalent of:

FROM   customer
WHERE  name IN ('Bob', 'Mary', 'Julie')

For an integer string:

FROM   customer
WHERE  cuid IN (<cfqueryparam value="#mylist#"
                cfsqltype="cf_sql_integer" list="yes">)