It's obvious what not to do. Don't pass raw, unclean data to a query without some type of insurance that it can't cause damage. Especially when the column is numeric. I'd also advise anyone not to believe what you said about cfquery 'automatically' protecting you 'most of the time' without cfqueryparam since you didn't include 'as long as you use something else in its place like val() on numeric fields'
You gave exmaples but are still begging for more so... <cfquery datasource="datasoruce" name="myQry"> select username from users where userid = #url.myvar# </cfquery> I would think that this is a perfect example of a 'most of the time' case. The first thing someone would do would be to assume that 'myvar' is also the name of the db column. If they are correct, then things move right along. If not, they will toss an invalid string in hopes to see an actual error message complete with the full query. If they get it, they know the real field(s), else they start guessing. If they are just passing by and trying, they will most likely try a couple and move on if they don't get it. If it's someone who has chosen the specific target, the odds are that they will keep at it until they guess the column name(s). If url.myvar is [1], the query tries to find the record where userid is 1 blah blah etc etc... If url.myvar is [0 or 0=0] or [0%20or%200=0] then you might have a problem since it would find all records. If you have admins in the same table then the very first record is most likely the author and therefore most likely the admin with the most rights yes? If url.myvar is [0 or 0=0 order by userid] or [0%20or%201=1%20order%20by%20userid] then the first record is the one CF is looking at What to do to prevent it in this case? where userid = #val(url.myvar)# or where userid = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.myvar#" /> if its being passed to a cfc, yes you can type it as an integer and be fine but if you accidentally type it as a string then you are right back to the same spot as you would be without typing it at all sicne CF is typeless and will treat 0 as a string if that's what you tell it to do. If you accidentally type an int to a varcahr in cfqueryparam, you'll still be safe since cfqueryparam will not allow the string to be executed as a statement. And obviously, single quotes and semicolons aren't always needed for a sql injection 'attack'. ..:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -----Original Message----- From: Russ [mailto:[EMAIL PROTECTED] Sent: Friday, February 22, 2008 9:11 AM To: CF-Talk Subject: RE: CFC protect from SQL Injection? > -----Original Message----- > From: Tom Chiverton [mailto:[EMAIL PROTECTED] > Sent: Friday, February 22, 2008 8:37 AM > To: CF-Talk > Subject: Re: CFC protect from SQL Injection? > > On Friday 22 Feb 2008, Russ wrote: > > CFqueryparam is not always beneficial. Lets say you are doing a batch > > insert with 1000 records. The cfqueryparam method is going to be a LOT > > slower. > > It shouldn't be. > It is, I've tested it. There is a lot of overhead in passing that many parameters. > > Additionally, everyone keeps talking about how you should use > cfqueryparam > > to avoid sql injection, but nobody has shown me an example of sql > injection > > without cfqueryparam. I think I can get the same results from val. > > But you don't get the cached execution plan benefits then. This is true. Most of the time, it will perform slightly better. My point, though, is that it's not necessary 100% of the time, and you should know how to construct a query without it that doesn't have sql injection vulnerabilities. This is why I want to start the conversation and get some real world examples of what not to do. Unfortunately, nobody has been able to provide any so far... Russ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:299714 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
