Preventing SQL Injection for Column Names

[datac0re]

I've noticed how the cfquery tag will automatically escape single quote
characters embedded in variables used in SQL statements, but is there a built
in way to prevent SQL injection if I'm using a variable as part of an "order
by" clause?

For example:

Select ColumnA, ColumnB
From MyTable
Order By #Url.ColumnNameVariable#

What's to prevent the user from entering something like this (with special
characters encoded of course, I'm not encoding them here for clarity):

[url]http://www.mysite.com/mypage.cfm?ColumnNameVariable=Amount;[/url] insert into
MyTable values (0, 0)

The net result would be that they inserted a record into MyTable. Of course
they could do more dangerous stuff too like dropping tables, xp_cmdshell, etc..

Thanks in advance!

[Kronin555]

What's to prevent the user from entering it? nothing
What's to prevent it from working? you and your code :-)

Semicolons are a red flag for maliciousness. Don't let them in anywhere that
isn't using a cfqueryparam.

You can't use <cfqueryparam> in this case, but using that as much as possible
helps alot.

[MikerRoo]

You should already be doing something like this:
<CFPARAM name="Url.ColumnNameVariable" type="variableName"
default="N0_VALUE_SENT"> <!--- Use a sensible default or the value
"N0_VALUE_SENT". --->

This will automatically stop apostrophes, semicolons, etc. Encoded or not.

However, this is not a substitute for using cfqueryparam!
If you cannot use cfqueryparam directly in the SQL, put the SQL in a stored
procedure. At the very least, use a switch/case to limit the possibilities
(don't use Form or URL variables directly in SQL).

Finally, all of the above still isn't enough for data columns. Remember that
if the user can store any data at all, you must guard against cross-site
scripting too.
HTMLEditFormat covers that contingency, for the most part.

Regards,
-- MikeR