Search code examples
sqlcoldfusioncfmlcfquerycfqueryparam

ColdFusion Sanatize SQL

I'm trying to loop over tableList and run a query for each table getting the count from each. Not all of the code is included but the problem is the cfqueryparam. When I run this code right now the error says "INVALID TABLE NAME". Here is what I am trying right now:

<cfloop list="#tableList#" index="t">
<cfquery name="getcount" datasource="erisnetselect">
SELECT COUNT(*) FROM <cfqueryparam value='AUDITOR.#t#' sqltype="VARCHAR">
</cfquery>

The problem is specifically this line:

SELECT COUNT(*) FROM <cfqueryparam value='AUDITOR.#t#' sqltype="VARCHAR">

I have also tried:

SELECT COUNT(*) FROM AUDITOR.<cfqueryparam value='#t#' sqltype="VARCHAR">

But I get the same error.

I think there might be a way to sanitize these table names before they hit the query but I'm not sure how to do it. If you need all of the code I can provide more but this is a huge page.

Solution

  • cfqueryparam is a value placeholder for a prepared statement. You cannot use value placeholders for table or column names because a prepared statement requires the query to be complete and valid before any values are put in. One of the design goals of prepared statements is to prevent malicious injection by separating query and values. The separation is achieved by sending the query without actual values first (value placeholders are usually indicated with a question mark ?), have the SQL server parse and understand it (query interpreter) and then wait for data to place into the prepared value slots. This also comes with a performance benefit, because the SQL server can reuse already interpreted queries while sending a plain string statement with query and values would always require parsing it again.

    To solve your problem, you will have to sanitize the table names either by using the appropriate command builder/quoter (depends on the SQL vendor, check your JDBC driver) or validate the name manually.

    If you have to go with the manual way, you should be conservative and only allow foolproof characters, such as alphabetic letters, digits, underscores and hyphens. Consider this:

    <cfloop list="#tableList#" index="t">

    <!--- make sure the table name only consists of alphabetic letters, digits, underscores and hyphens --->
    <cfif not reFind("^[a-zA-Z0-9_-]+$", t)>
        <cfthrow message='The specified table name, which is "#t#", contains illegal characters.'>
    </cfif>

    <cfquery name="getcount" datasource="erisnetselect">
        SELECT COUNT(*) FROM AUDITOR.#t#
    </cfquery>

    ...