Does CFArgument Typing Protect Against SQL Injection
This question was asked on one of the several lists to which I subscribe. The author wanted to know if he needed to do anything else as long as he was specifying the "type" attribute of the Cfargument tag - or was that sufficient protection against the dreaded SQL Injection Attack (see my previous post on Application Security). Like the Elves of the Shire my answer is both yea and nay. Consider this example:
<cfargument type="numeric" name="userid"/>
<cfquery name="get" datasource="mydsn">
SELECT *
FROM Users
WHERE userid = #userid#
</cfquery>
<cfreturn get/>
</cffunction>
<cfset user = getMyUser(form.userid)/>
In this example, if a clever scripter passed in something designed to inject into the query, say.... 15 OR userid <> 0 ... the function would throw an error because whatever was in form.userid was not numeric. So in this case - yes, the argument scope and the strong (or strongish) typing is protecting you against injection. But now consider this example:
<cfargument type="string" name="userid"/>
<cfquery name="get" datasource="mydsn">
SELECT *
FROM Users
WHERE userid IN (#userid#)
</cfquery>
<cfreturn get/>
</cffunction>
<!--- form.users is a list of IDS--->
<cfset user = getUsers(form.users)/>
In this example the site is expecting a list of userids. The argument scope treats that as a string. Consider what would happen if our budding bill gates passed in 14,15,16) OR userid NOT IN (0. The resultant where clause would end up being WHERE userid IN (14,15,16) OR userid NOT IN (0). This would not throw an error because it is still a string and allowed by the argument scope.
Conclusion
My take is the same as always. I can think of no reason not to use CFQUERYPARAM. It binds the type to the variable in query during the query preparation. Now, in CF8, you can even use binding with caching - taking away the last nettlesome obstacle.
5 Comments
-
I believe I said that in my last sentence :) But to be clear - if using user inputs in queries that will be cached on platforms prior to CF 8 you need to scrub them to be sure. -
You are correct with the cfargument. In certain case such as numeric it can help against SQL injection. But for other types such as string it does nothing to prevent ";drop table" or other sql commands.
The fact that CF8 (and older cf versions) do not support cfqueryparam in some cases (or at all) is the exact reason why I have created many of my own data validation functions over the years. In my own code nothing goes into a query that has not already been through various validation functions to make sure that the data is exactly what I am expecting. -
If caching is needed pre-CF 8, you can always roll your own mechanism. There is no excuse for not using cfqueryparam.
Think of it like this:
How long does it take to roll your own caching so that you can use cfqueryparam? Now how long (and how much money and complete and utter frustration) would it take to rebuild your DB because someone brought it down. Or how much of a pain would it be to deal with someone hacking a login to your admin section and deleted all your users, or worse yet, stole their personal information?
No excuses. Period.
Sites to Watch
Aggregators
RSS
