CFQueryparam and Lists

A word on SQL Injection

SQL Injection is a pervasive problem in the Web Application World. A quick search for URLS that use raw SQL brings up hundreds of thousands of dangerously formed URLS. Any developer worth his salt knows to clean user input before using it.

Defend Against SQL Injection in ColdFusion

CFQueryparam is a recommended tag that helps to keep your queries safe from SQL Injection. Any ColdFusion worth his salt uses CFQueryparam to help keep malicious parameters from being executed by the database engine. I ran across some code today that used CFQueryparam in most cases, but there was a particular, recurring use case that used raw parameters.

Example 1



SELECT productName,
FROM product
WHERE productID IN ( #productIDList# )



Note the use of the list. It is a common paradigm to pass a delimited list of data to an SQL statement. In this case, the developer chose not to use CFQueryparam because he/she was under the impression that the result would be a single parameter, not a chain of parameters.

However, CFQueryparam can be used successfully in this case by setting the optional attribute ‘list’ to true. This is a supported attribute on all database engines.

Example 2



SELECT productName,
FROM product
WHERE productID IN (" )



The resulting query will be parametrized in such a way as to render the list as a list and the results of the second query are equal to the first. Except for the case of an SQL Injection attack.

In the case of an SQL Injection attack, the developer of the first code sample would have a lot of explaining to do…