0

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.

Cole Perry
  • 333
  • 1
  • 5
  • 27
  • 5
    You cannot use cfqueryparam in the FROM clause. – Scott Stroz Feb 18 '20 at 17:47
  • Ok I did not know this thank you. Could you offer a work around then? – Cole Perry Feb 18 '20 at 17:52
  • 1
    @ColePerry you will just have to write your own table check function. If you have a list of known good tables then checking if the name supplied is in that list would be the safest option. – Dan Roberts Feb 18 '20 at 19:06
  • 1
    Depending on your RDBMS, you might be able to get your answer by querying system tables. – Dan Bracuk Feb 18 '20 at 20:47
  • 1
    What RDBMS? Also, any time you have a loop on the outside of a query, there's _probably_ a better way to do what you're trying to do without beating up on your database and network. – Shawn Feb 20 '20 at 21:13
  • I would also recommend using a whitelist to validate tables you are allowed to request. Dynamic SQL, especially where a table name is allowed, can be very problematic. – Shawn Feb 20 '20 at 21:22

1 Answers1

3

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>

    ...
Alex
  • 7,743
  • 1
  • 18
  • 38
  • Two notes: In SQL Server, a table name can take any valid character (ie "My Table" is a valid name, and can be queried like `SELECT count(*) AS cnt FROM AUDITOR.[#t#]`. 2) Give the `SELECT count(*) and alias so that it can be referenced. – Shawn Feb 20 '20 at 21:18
  • I'll also echo my point above again. A `cfquery` inside of a `cfloop` is probably not the best way to do what needs to be done. – Shawn Feb 20 '20 at 21:19