Narrowing down what I'm looking for in terms of input validation:
For most inputs, yes, I'll be able to validate by datatype, and if possible can go further and validate by length, format, or verify that it's from a set of valid values (the whitelist approach). Those are straightforward.
What I'm interested in is how best to validate inputs in the worst case conditions:
* input is a text field, can have variable length, and possible red-flag characters such as '"();-- could be part of valid input, and therefore need to remain in the input without being stripped out.
* The query using that input uses the input with a SQL wildcard in a LIKE clause, and/or the query is cached, meaning I can't use CFQUERYPARAM.
Under those conditions a whitelist isn't really possible, so I'd either like to construct the best blacklist I can, or, ideally, avoid the need for a blacklist entirely by rendering injection impossible.
Here's a new question that narrows the scope of the problem:
TRUE OR FALSE: CFQUERY is only vulnerable to injection attacks in two cases:
1) when you supply a value that is not wrapped in single quotes, like the following:
WHERE ItemID = #FORM.DangerousInput1#
ORDER BY #FORM.DangerousInput2#
2) when you construct a SQL string containing single quotes outside of the CFQUERY, and then use PreserveSingleQuotes to output your string within CFQUERY:
<CFSET VARIABLES.SearchCriteria = "AND MovieTitle = '#FORM.DangerousInput3#'">
<CFQUERY>
SELECT MovieID
FROM Movies
WHERE
</CFQUERY>
According to this page from Allaire from 1999, that statement is true:
http://www.macromedia.com/devnet/security/security_zone/asb99-04.html
Cold Fusion automatically escapes single quotes used within inputs to a CFQUERY, leading me to believe that an injection attack is impossible in cases like this, even without using CFQUERYPARAM:
<CFQUERY>
SELECT ItemID
FROM Items
WHERE Title = '#FORM.ShadyInput#'
</CFQUERY>
CONCLUSION:
This would be my set of rules for preventing injection attacks:
1) Do not use any user input to supply column names, table names, SQL keywords, etc.; basically, anything other than an actual data value. Example:
Any code like that can be easily rewritten to eliminate the unguarded input.
2) Do not use PreserveSingleQuotes() within CFQUERY if the PreserveSingleQuotes() contains any user input.
3) Use CFQUERYPARAM for all non-character values supplied to CFQUERY that are not wrapped in single quotes.
Replace:
with something like the following, with the proper datatype specified:
You will not be able to use CFQUERYPARAM if the CFQUERY is cached using CACHEDWITHIN or CACHEDAFTER. If you encounter a situation like that, you will have to either validate the input yourself (for example by using IsNumeric() and checking for valid length or format), or you will have to cache the query yourself using APPLICATION or SESSION scope rather than the cache parameters.
4) For character inputs supplied to CFQUERY wrapped in single quotes, CFQUERYPARAM is not strictly necessary because Cold Fusion will automatically escape single quotes. However, it's recommended that you use CFQUERYPARAM anyway for consistency, for its performance benefits and additional validation options, and because it's possible that there are a few special cases where Cold Fusion's built-in escaping of single quotes is bugged (see rule 5).
In cases where you can't use CFQUERYPARAM (the CFQUERY is cached, or you are using a SQL wildcard with the input), it is ok to do so, as long as you don't run afoul of rule 5.
5) Be careful of values specified using formats other than the standard SCOPE.NAME style. They might not be properly escaped by Cold Fusion, and how they're handled may depend on the version of CF you're using:
According to a message posted here:
http://www.coldfusionmx.com.br/cfusion/webforums/forum/messageview.cfm?catid=7&threadid=966789&enterthread=y
For WHERE MovieTitle = '#Form[myVar]#', CFMX will escape single quotes in the value of myVar, which is the name of the form variable, but then will not escape single quotes in the actual value of the form variable. If you use variables like that you must use CFQUERYPARAM around the value.
6) If you insist on breaking any of these rules, you must be able to guarantee that any user input is safe through your own validation.
If you follow those 6 rules, injection should theoretically be impossible. You should still use whitelist validation where appropriate (verify that inputs have a required format or belong to a set of valid values), and you may also want to use some kind of blacklist in your application.cfm: loop over the FORM, URL, and COOKIE collections and look for red-flag inputs that you want to block even though you are presumably immune to injection attacks. Examples:
<script
drop table
drop database
exec xp_cmdshell
cmd.exe
root.exe
truncate table
truncate database
<object
<%
And possibly SQL keywords like SELECT, DELETE, UPDATE, INSERT, if you can look for them in combination with other inputs so that you will not block good inputs containing those words.
I've tried to cover everything for my own use and peace of mind. I hope it's helpful to others. Those rules are a little more specific and comprehensive than the results I've been able to find with Google, which usually just recommend using CFQUERYPARAM or stored procedures for everything (which can't always be done). If you see any problems or have anything to add, let me know.
Conan
At 09:20 PM 8/17/2005, you wrote:
oops... now with new and improved subject line! :)
In addition to <cfqueryparam>, on server-side I have been known to use
cflib.org's sqlSafe().
http://www.cflib.org/udf.cfm?ID=1219
~Dave
On 8/17/05, Matt Woodward <[EMAIL PROTECTED]> wrote:
> Personally I take the "whitelist" route as opposed to the "blacklist"
> route, which is what it sounds like you're trying to do. I check to
> see that the data I'm receiving is the data I'm expecting from a type
> standpoint for starters; that way they can't stick SQL commands at the
> end of stuff in the URL for example, and if I'm expecting a string
> it's not like them putting a drop statement in there is going to do
> anything anyway if I insert that into a varchar field. Nothing's
> foolproof but in my experience if you know what data you're expecting
> and check vigorously for that, and use cfqueryparam, you're pretty
> darn safe against sql injection attacks.
>
> Matt
