This question was a bit lengthy, but it basically came down to creating dynamic table names in ColdFusion queries. The questioner was attempting to do something like this:
FROM table_<cfqueryparam value="#arguments.suffix#" />
I have never seen this work. As much as I love ColdFusion's CFQueryParam tag (it really is the bomb-digity), I think when it comes to creating dynamic table names in ColdFusion queries, you just have to use the good-old hash signs:
Of course, once we do this, we do open ourselves up to some potential danger. If these are system-based values, such as application-scoped names:
... then you are safe. If these dynamic table names are fairly static and just part of some package configuration, then go ahead and use them without concern - only you can do harm in this scenario. However, if a user has any influence at all over which table can be selected wether through a link or a form submission, then we have to really lock the code down.
In cases where my table selection is user-based (even if it is an administrative user), I have put in one of two types of security. I either CFParam the table name using a regular expression in such a way that only acceptable tables can be used:
<!--- Check to make sure that the user-selected table is a valid table for this situation. ---> <cfparam name="URL.table" type="regex" pattern="contact|company|vendor|partner" />
What this does is create a pipe-delimited list of viable table names that the user may select. If the user selects a table that is not valid (or tries to hack the URL or FORM submission), then ColdFusion will throw a validation error.
I have also performed this same type security logic using a standard ColdFusion list:
<!--- Make sure a table is passed in. ---> <cfparam name="URL.table" type="string" /> <!--- Make sure this table is valid. ---> <cfif NOT ListFind( "contact,company,vendor,partner", URL.table )> <!--- The selected table was not valid. Throw error. ---> <cfthrow type="InvalidTable" message="You have selected an invalid table." detail="The table that you selected, [#URL.table#], is not a valid choice on this page." /> </cfif>
This is doing the same things as the ColdFusion CFParam tag but allows us to handle errors in a slightly different way (by manually throwing them rather than catching validation exceptions).
Either way, once you are sure that the user-selected table is valid, I would say that there is nothing wrong with just throwing that table name into the dynamic query using hash signs:
I hope that helps.
Want to use code from this post? Check out the license.