We’re all aware of the concerns of SQL Injection these days. Lots of articles written about over the years. ColdFusion back in 6.1 I believe even came out with a nice tag called cfqueryparam which indeed helps.
My issue is with the ‘Order By’ clause. It seems that since you don’t have a nice neat little tag such as cfqueryparam to use here, you are on your own to protect yourself. I’m willing to bet there are several applications out there where the developer just plugged in cfqueryparam into the where clause when needed and yet still exposed the order by statement.
Example:
Select name, address, dateadded
From tablename
Where ID = <cfqueryparam cfsqltype=”CF_SQL_INTEGER” value=”#url.sort#” />
ORDER BY #URL.SORT#
What’s to prevent the hacker from typing into the url “index.cfm?ID=25&sort=fieldname;delete from tablename”
I brought this up on the cf-talk message board and you can read it here.
The best solution in the most basic form as far as I can tell is to abstract the field names from the client and use cfif or case/switch like the following:
SELECT name, address, dateadded
From tablename
Where ID = <cfqueryparam cfsqltype=”CF_SQL_INTEGER” value=”#arguments.InventoryID#” />
ORDER BY
<cfswitch expression=”#url.sort#”>
<cfcase value=”a”>
name
</cfcase>
<cfcase value=”b”>
address
</cfcase>
<cfdefaultcase>
dateadded
</cfdefaultcase>
</cfswitch>
This insures you are testing for exactly the field name you expect and that you send the correct information to the database. Be sure to use cfdefaultcase in the event that none is passed.
I’d love you hear your thoughts and experiences on this. I’m especially interested to see if you have a more elegant or concise way of doing this. Especially if you have 10 plus column names and want the option to reverse search. The switch will get huge and ugly with my example.
Vince Collins
