Working with SQL Parameters in Cold Fusion

Most modern Web application frameworks provide native mechanics to protect databases from undesirable SQL execution. Cold Fusion’s answer to input sanitization is the tag cfqueryparam that is only valid inside a cfquery block. It is not a newcomer to the language but I will still from time to time see it being underutilized. It is possible that this is because cfqueryparam is especially cumbersome to implement. Despite its usefulness it can become an obnoxious nuisance in heavily parametered queries. Consider the following typical implementation:

<cfquery name = "getFirst" dataSource = "cfdocexamples">
  SELECT *
  FROM courses
  WHERE Course_ID = <cfqueryparam value = "#Course_ID#" CFSQLType = “CF_SQL_INTEGER”>

</cfquery>


This example was taken from Cold Fusion’s documentation of the cfqueryparam tag. A simple select like above certainly doesn’t suffer much by using cfqueryparam in-line. But as we add more parameters our query becomes less SQL and more Cold Fusion, contributing to unintentional obfuscation.

Fortunately cfquery is a flexible animal and allows us to perform some unconventional formatting to achieve a more readable SQL query and yet retain the protection cfqueryparam provides. The following example is specific to Microsoft SQL Server, but will work for any database flavor that supports named variables embedded in sql. Let’s take the same query, but instead use both cfqueryparam and T-SQL variables to create a more readable (and maintainable) query.


<cfquery name = "getFirst" dataSource = "cfdocexamples">
   DECLARE @CourseId in = <cfqueryparam value = "#Course_ID#" CFSQLType = “CF_SQL_INTEGER”>
  SELECT *
  FROM courses
  WHERE Course_ID = @CourseId
</cfquery>

Our end result is clean SQL with Cold Fusion’s interference relegated to a few declarations before our functional query. This ends up being a few more lines of code, but I think the readability it yields is worth it.