Ask Ben: Dynamic Table Names In ColdFusion Queries
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:
FROM #table_name#
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:
FROM #APPLICATION.ProductTable#
... 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:
FROM #URL.table#
I hope that helps.
Want to use code from this post? Check out the license.
Reader Comments
You should really be using parameters for 'constant' values in SQL as it affects the database's execution plan (this includes fixed value in your where clause). So the plain text approach is the way to go. :)
Oops type above should read:
"You shouldn't really be using parameters for 'constant' values in SQL as it affects the database's execution plan (this includes fixed value in your where clause). So the plain text approach is the way to go. :)"
Sorry!
@John,
Agreed. Plus, writing things like this:
WHERE is_deleted = 0
... is just so much easier :)
Just regarding this:
{quote}
FROM table_<cfqueryparam value="#arguments.suffix#" />
I have never seen this work. As much as I love ColdFusion's CFQueryParam tag
{quote}
One should not expect it to work. <cfqueryparam> is for setting paramater values. It has the dual purpose of where the parameter will be used in the SQL, as well as taking the value for the parameter. It's not simply for validated string substitution, and can't be used for "building" SQL.
If one looks at the SQL being passed when using <cfqueryparam> (in debug, or dumping the RESULT of the query), one doesn't end up with the values of the params simply inserted into the rest of the SQL; a marker is placed in the SQL where the parameter should go, and the value is sent separately.
In short, a parameter - and this has nothing to do with how mcfqueryparam> works, it's how SQL *parameters* work - can be used for a static *value*. Not part of the SQL (like SELECT or WHERE), or not in any reference to the DB schema (such as table names or column names, etc).
Ben, you may well know this, but you sounded a bit vague in your statement.
--
Adam
@Adam,
True, it would come through as:
table_?
Sorry for the vagueness. Everything you said is correct.
@Ben - it's certainly easier to read!
I really hope CF9 supports a cfscript version cfqueryparam something like:
WHERE is_deleted = #QueryParam(myvar, 'cf_sql_bit')#
which is much nicer than:
WHERE is_deleted = <cfqueryparam value="1" cfsqltype="cf_sql_bit" />
Hi Ben!
Thanks for answering my question - so quick too! As I mentioned, I'm not too familiar with certain parts of ColdFusion so this does clear a few things up for me.
While there are a lot of tutorials and code examples on the Internet, I find it hard to locate information on "best practices". If only we could log a few of these on a website somewhere, that would be sweet - because sometimes I'm not always sure if what I am doing is the best approach...then again, every application is usually different I suppose.
Many thanks for your help once again - you are really great at what you do!
Cheers,
Mikey.
@John Wish
Hey John,
Could you just write a user defined function for this called "queryParam"? Hmm, actually, on second thoughts, I'm not sure what would happen using this tag outside of a CFQUERY tag. Probably wouldn't work. Meh!
But yeah, looks a bit nicer doesn't it! :)
I think this is a case of someone hearing that cfqueryparam is "A Good Thing" but not understanding why. The result is, they just naturally think that if a few cfqueryparams are good, then more of them are better.
cfqueryparam solves two problems: 1) it helps guard against sql injection attacks. 2) it allows the database to cache the execution plan.
The reason sql injection attacks happen is that you have to get data from a user, and you have to pass that data on to the database. Seeing cfqueryparam used to construct a table name makes me think (as you did) that maybe the person who asked this question is getting a table name, or part of a table name from the user. That would be a really really bad idea, and I'm glad that you covered that. Even if cfqueryparam allowed you to specify a table name, it would not protect against sql injection in this case. Your note about using the cfparam tag is the way to go.
In summary - potentially really tremendously awe-inspiringly dangerous things going on here.
I do this sort of thing quite often and I don't feel like I'm opening myself up to a can of worms because I always use forms and I always explicitly control what get's passed to the CFC responsible for making the dynamic SQL. Basically if you allow the user to form the queries using only cfselect lists then you can avoid an injection attack. I typically will throw the table names in a cfselect and pass that to the CFC to dynamically make the query and return the results. If you allow users to key the table name into the form then you're asking for it and you probably will get hacked as somebody is going to come along and get mid-evil on your CFC.
@Andy: "Basically if you allow the user to form the queries using only cfselect lists then you can avoid an injection attack."
What is there to stop someone from selecting "file/save as" in their browser, then editing the form to add something to the select box, then hitting the submit button?
(or if you prefer, using a browser plugin like firebug)
I really don't think that cfselect protects you from an injection attack. You still have to manually check the input on the server side. If you disagree, I'd like to see the code.
I'm populating a cfselect list using a query returned from a cfc (querying a database table full of acceptable table names that can be used for query). I fail to see how this is some giant security hole. It seems as though you're implying that someone is going to save a copy of my page, edit it and then upload it as a malicious cfm page onto the server and then execute it. If you have that going on (your sever is open to the world) then you've got a lot more to be concerned about than just SQL queries.
Andy,
The attacker don't need to upload a malicious cfm page on your server. He just save the file on his own hard-drive, edit the code of the HTML (action attribute of the form and options of the select input), then he just open the modified file locally with his browser and submit it. The action attribute of the form can be change to http://www.yoursite.com/yourcfmpage.cfm and the data of the form will be sent to your cfm page. That's it, SQL Injection is done.
You always need to control what is coming from the user (url, form, cookie) because they can modify them in many ways.
This is a stupid argument. My code checks the value against my table in the database - see what I mean? It is not like I'm saying that you don't have to perform any validation on the back end.
Oh, sorry, I didn't see that you was saying that you check the value before doing the query. My bad! ;)
Hi
This is satyam
can u tell me how to convert one application into multiple languages.
Suppose one person develop the application in English language how to convert that into another languages.
Regards,
Satyam
@ satyam, Google Translate is your answer, Another way is to to create resource files for each language and then configure with CF to run it
Hi ben!
I have a Question here, I am listing the columns from a query which is a Join of two tables and it has some fields which is defined in both, we had maked it different as by using the tablename, my question is i am trying to search by choosing the column and and clicking on Search, how the query Knows which column of the table to refer to