sql - ColdFusion Query - Injection Protection

Question

I ask this question with a bit of sheepishness because I should know the answer. Could someone be kind and explain if and how injection could occur in the following code?

<cfquery>
    select * from tableName
    where fieldName = '#value#'
</cfquery>

I'm specifically curious about injection attempts and other malicious input, not about best practices or input validation for handling "normal" user input. I see folks strongly advocating use of CFQueryParam, but don't think I see the point. If user input has been validated for consistency to the database schema (e.g. so that input must be numeric for numerical database fields), is there anything else gained by using CFQueryParam? What does <cfqueryparam CFSQLType = "CF_SQL_VARCHAR"> do that '#value#' doesn't do?

Answer 1

Update:

While this answers part of your question, Peter's response is better, in that it directly addresses your question of "Why use cfqueryparam, when CF automatically adds protection by escaping single quotes?". Answer: In short, because the latter does not always work. Bind variables do.

---

It says in the docs "escapes string variables in single-quotation marks" but doesn't CF already "magically" do this in CF query tag when you wrap evaluated variables in single quotes?

Yes, most versions automatically escape single quotes as a protection measure for those not using cfqueryparam. However, as Scott noted above, it is better to use cfqueryparam (ie bind variables) because they ensure parameters are not executed as sql commands. Bind variables work, even in cases where the automatic escaping does not, as Peter's answer demonstrates.

That said, sql injection protection is really just a side effect of using bind variables. The primary reason to use bind variables is performance. Bind variables encourage databases to re-use query plans, instead of creating a new plan every time your #parameters# change. That cuts down on compilation time, improving performance.

Cfqueryparam also has a number of other benefits:

• Provides data type checking (length, value, type, ...)
• Provides attributes that simplify handling of "lists" and null values
• Performs data type checking before any sql is sent to the database, preventing wasted database calls

While it does not really apply to string columns, IMO another big reason to use it is accuracy. When you pass a quoted string to the database, you are relying on implicit conversion. Essentially you are leaving it up to the database to figure out how to best perform the comparison, and the results are not always what you were expecting. (Date strings are a prime example). You may end with inaccurate results, or sometimes slower queries, depending on how the database decides to execute the sql. Using cfqueryparam avoids those issues by eliminating the ambiguity.