Trouble using cfqueryparam with sql query strings

Question

So I have a string that was constructed in a different file being passed into a query as a global variable that I need to bind (legacy code):

<cfset queryString="((playerID=1223) OR playerID=1224))">

<cfquery name="testQuery">
    SELECT *
    FROM teamRoster
    WHERE teamID = 9876
    AND <cfqueryparam value="#queryString#" cfsqltype="CF_SQL_VARCHAR">
</cfquery>

Is there a way to use cfqueryparam for query strings here? Or is there a different way to protect myself from sql injection here? Thanks for your help!

Answer 1

You cannot use cfqueryparam for query strings. It can only be used for those things sql allows you to paramaterize (basically, just literals like strings or numbers). They can't be bound to keywords or field names, let alone complex clauses.

FWIW, this is a SQL limitation, not a ColdFusion issue.

Answer 2

(Too long for comments ...)

No, because cfqueryparam (or bind variables) are designed to prevent the very thing you are trying to do, which is execute a string as a sql command. Bind variables cannot be used on anything that must be interpreted as a command by the dbms, such as table or column names, operators, etcetera - only literals like numbers or simple strings.

Given that cfqueryparam can only be used inside a cfquery tag, the cfscript version makes it a bit easier to parameterize dynamic statements. However, as long as you must execute arbitrary strings, there is really no bullet proof way to protect the query against sql injection. If at all possible, I would recommend restructuring to eliminate the dynamic SQL. Given that it is a legacy app, I realize it is more challenging, but the end result is worth it.

FWIW, keep in mind that while sql injection protection is probably the most critical benefit of using cfqueryparam, there are other benefits as well. Most notably performance improvements for queries executed multiple times, with varying parameters. That is actually the primary purpose of bind variables. The injection protection that comes along with it just a nice side effect.