An Architect's View

40 responses so far ↓

• 1 Anuj Gakhar // Aug 6, 2008 at 8:14 AM

  Yup, I also saw hundreds of emails today from one of the CF sites I work on.
  
  Good that cfqueryparam is to the rescue.

• 2 todd sharp // Aug 6, 2008 at 8:19 AM

  John Whish has put together a nice presentation that covers the use of cfqueryparam and much more for protecting your application.
  
  http://slidesix.com/view/ColdFusion--Protecting-Your-Applications

• 3 Steve Withington // Aug 6, 2008 at 8:25 AM

  I concur, thank you Ray!

• 4 Jim Pickering // Aug 6, 2008 at 8:33 AM

  Yup me too. In fact, I get a ton of spam email from the name of US States@jimpickering.com, which say they are blog entries from my blog, but they are merely copies of the entries on my blog. I think I've received email from every state by now. The blog isn't erroring out though, but it's good to know BlogCFC can handle all of these various attacks. Thanks Ray!

• 5 Jason Fisher // Aug 6, 2008 at 10:39 AM

  Yep, I had a site newly attempted last night with the whole injection attempt thing, but they only bounced against it a few times (only a few errors). Model-Glue actually seems to be generating an exception long before it gets to my cfqueryparams at any rate, but I'm happy to know I also don't have any un-param'd queries in any of my apps that are out there.

• 6 ike // Aug 6, 2008 at 11:10 AM

  Or an ORM that uses cfqueryparam. DataFaucet does and I'm betting Transfer does. When I looked at ObjectBreeze a long time ago (in CFDJ) it didn't... haven't seen it since.

• 7 Sean Corfield // Aug 6, 2008 at 11:55 AM

  Transfer uses cfqueryparam everywhere so it's "safe".

• 8 todd sharp // Aug 6, 2008 at 2:54 PM

  So whoever the asshole was that tried to attack you must be reading your blog, because he tried the same crap on SlideSix after I posted my comment today...

• 9 Dmitry Yakhnov // Aug 6, 2008 at 9:07 PM

  Almost every website, I have developed in CF, are under constant attacks since yesterday.

• 10 Gert Franz // Aug 6, 2008 at 9:10 PM

  My two cents here. While CFQueryparam is a very good solution for preventing hackers to penetrate your site, it is a nightmare for SQL DBA's. You only see prepared statements going through in (for instance) the SQL Profiler. So you get lines listed like: EXEC @23,34,54,23,21
  This means the server will execute the prepared statement nr. 23 and pass the parameters 34,54,23 and 21 to it. But it is another matter to find out which prepared statement that might be. This is the only downside I see in queryparams and prepared statements. In Railo 3.1 you will have an option to turn of the background use of prepared statements but still being able to use cfqueryparam. Of course prepared statements are faster since the DB server does not have to recalculate the execution plan for a particular query all over again. But in order to find errors it might be useful to temporary turn of the use of prepared statements.
  
  Gert

• 11 Sean Corfield // Aug 6, 2008 at 9:48 PM

  @Gert, but it's easy to enable CF debugging and see exactly what SQL is being executed and what parameters are passed to it. Also, in my experience, DBAs *are* able to see exactly what is going on - certainly with Oracle. Perhaps you are referring to a deficiency in Microsoft SQL Server?

• 12 Dane Pescaia // Aug 6, 2008 at 11:17 PM

  @Gert/Sean - You can see everything that's going on in MS SQL Profiler. Here's an example of what you'd see in that tool when running <cfquery> with a <cfqueryparam> to pass the 'some_id = ' value:
  
  declare @p1 int
  set @p1=NULL
  exec sp_prepexec @p1 output,N'@P1 int',N'select top 1 * from some_table where some_id = @P1 ',100
  select @p1

• 13 Tom Chiverton // Aug 7, 2008 at 2:37 AM

  Reactor uses cfqp throughout too.
  
  @Gert: I have *never* had an issue where use of a prepared statement made it hard to debug anything. I've never even heard anyone moan that it was a problem.

• 14 Sebastiaan // Aug 7, 2008 at 3:36 AM

  Here's a possible solution for those that don't use CFQUERYPARAM or who want to stop the bots:
  
  http://www.coldfusiondeveloper.com.au/go/blog/protect-your-cf-application/
  
  Good luck with it - I've not been hit yet, but I know a lot of CFWebstore users have had problems with these attacks for some time now.

• 15 Patrick McElhaney // Aug 7, 2008 at 5:39 AM

  cfqueryparam is all good, but I'm getting sick of the error emails. This has kept them out of my hair for the past couple of days.
  
  RewriteCond %{QUERY_STRING} EXEC\(@S\) [NC]
  RewriteRule .* - [F]

• 16 William from Lagos // Aug 7, 2008 at 6:00 AM

  I also received error reports sent to my email for sites, built with CF, that came under SQL injections. but thanks to good old cfqueryparam

• 17 Pete Freitag // Aug 7, 2008 at 12:04 PM

  @Gert You can get the full SQL Statement in your SQL Server Traces, you just have to enable some non-default options. I wrote a blog entry about this a few weeks ago: http://www.petefreitag.com/item/674.cfm
  
  @Dane - I see statements like that in CF7, but with CF8 I see statements like Gert describes. So must be due to how the prepared statements are invoked (and must have changed in CF7-8), I think there are a few different ways of doing this on SQL Server.

• 18 samuel // Aug 7, 2008 at 7:40 PM

  >> RewriteCond %{QUERY_STRING} EXEC\(@S\) [NC] RewriteRule .* - [F]
  
  Which goes where and does what exactly?

• 19 Sean Corfield // Aug 7, 2008 at 8:23 PM

  @Samuel, it's an Apache directive - so it goes in your httpd.conf file or .htaccess file if you are using Apache.

• 20 Dane Pescaia // Aug 7, 2008 at 9:18 PM

  @Pete - Actually that statement I posted was from CF8. I haven't seen the other format, so there must be some difference in my setup and those that produce it - perhaps the db driver.

• 21 Gert Franz // Aug 7, 2008 at 10:19 PM

  Good to know there is an actual solution for the SQL Server DBA's out there. I must admit I wasn't aware of it. I'll pass that on. Thanks for the hints...
  
  Gert

• 22 Pete Freitag // Aug 8, 2008 at 9:20 AM

  @Dane - Thanks for clarifying, I had seen statements like you posted in CF7, so I had assumed that was the difference, must be the JDBC Driver version.

• 23 Mary Jo Sminkey // Aug 8, 2008 at 11:51 AM

  @Sebastiaan - Actually I've only heard from a handful of people running older stores that haven't upgraded yet that got hit...I've never yet found a good way to convince people to upgrade these older stores until something happens (hey, it costs money!) but hopefully they at least implemented some of the patches I sent out when we first heard about this several weeks ago. But actually most of the problems I've heard about, particularly this week, revolved more about preventing the errors being thrown as a result of the nasty URL strings, but that's easy enough to deal with. My current release already does a pretty good job of trapping them but I'm always tweaking things to make it work better and my next release in beta testing now will have a few updates to address this. cfqueryparam is all well and good, but it's still best to have some error trapping in there on top of it so the code isn't throwing errors for hundreds of hack attempts on it.

• 24 Sebastiaan // Aug 8, 2008 at 11:13 PM

  @Mary Jo: Sorry if I neglected to add a remark on your excellence in reacting to possible problems with CFWebstore - you truly dispatch fixes very quickly if something serious is amiss, like the recent hack-attacks with malformed URL's.

• 25 Jeff Knooren // Aug 9, 2008 at 1:00 AM

  I just read on the houseoffusion a solution of throwing an error if "cgi.query_string contains 'DECLARE%20'". Would it be worth putting that into the application file of the fusebox distribution?

• 26 Sean Corfield // Aug 9, 2008 at 11:09 AM

  @Jeff, that would only protect you against the current attack - it is not a general solution. You MUST use cfqueryparam to avoid general attacks (and it's worth doing better server-side validation of link and form data too since an attack may still cause bad data in your application even if it "valid" at the DB level).

• 27 ike // Aug 9, 2008 at 12:15 PM

  @Jeff - Someone emailed me directly the other day saying they were having some difficulty implementing a similar solution that had been posted on Russ Michaels blog and that I had compressed down from ~40 lines of code to about 3 lines...
  
  Beyond Sean's advice that it's not a general solution, I also had to add when I replied that I see these only as temporary solutions while working on implementing cfqueryparam because as long as that solution is in place, you're going to get false positives. The problem with false positives is that friendly users who are doing what they are supposed to be doing will be prevented from legitimate uses of your site.
  
  For example in the case of throwing an error on "declare " in the query-string you're going to get some user writing a forum entry with the phrase "I declare this project a success!" - your workaround for the sql-injection attack will produce a false positive, log the error and frustrate the hell out of your previously friendly and now possibly irate user.
  
  So I wouldn't leave anything like that in any application on a permanent basis, just as a last-ditch to give you some breathing room while you deal with the problem at the source. Then code like hell until you're pretty sure all your queries are param'ed and comment it out and wait to see if you missed any.
  
  So even if it weren't for the fact that it's not a general solution, I wouldn't recommend that as an addition to any framework distribution.

• 28 Jason Fisher // Aug 9, 2008 at 2:15 PM

  @ike,
  
  I agree with not putting such specific traps into a framework's codebase, but you really should not have to worry about false positives with the DECLARE test. First of all, if you use Find("DECLARE", cgi.query_string) as your test, then it will be case-sensitive, and secondly, your forms shouldn't ever be posting to your query_string so long as you're using method="post". Basically, by testing 'DECLARE', you would be trapping only for bot and URL injection attempts that use the specific pattern being currently observed across these attacks.
  
  Still wouldn't want it in the core distro, but little or no worry about false positives that I can see.

• 29 ike // Aug 9, 2008 at 2:57 PM

  @Jason - Slippery slope there my friend. :) The can of worms this opens up only starts with the fact that the declare keyword isn't case sensitive in SQL server (and probably other databases) and so, by using a case-sensitive find, you've immediately decided to omit testing for a more clever sql-injection attempt. The notion of forms being submitted using post instead of get, while true is still incidental. I could easily see a small twitter-esque form designed to submit a certain limited number of characters in text that would be submitted with method "get" for some specific (although currently unknown) reason. And I suspect the discussion only becomes more esoteric from there, leaving me still thinking I'd rather work toward using cfqueryparam as the ultimate barrier. Though I also agree with Sean that careful case-by-case data validation is also a good idea as an extra layer.

• 30 Jordan // Aug 14, 2008 at 6:47 AM

  The only drawback with cfqueryparam is that you can't cache the query using cachedwithin.

• 31 Pete Freitag // Aug 14, 2008 at 10:25 AM

  @Jordan, as of CF8 you can cache queries using cfqueryparam.

• 32 Sean Corfield // Aug 14, 2008 at 10:38 AM

  @Jordan, as of CF8, you can use cfqueryparam with cachedWithin.

• 33 Sean Corfield // Aug 14, 2008 at 10:40 AM

  Oops, I commented before noticing Pete had also said the same thing :)

• 34 ike // Aug 14, 2008 at 10:58 AM

  I just find it interesting that not only did you "say it in stereo", but that your word selection was even nearly identical, right down to "@Jordan, as of CF8"... hmm... There can be only three possible answers
  
  1) twins separated at birth (in this day and age?)
  2) clones ... (in this day and age?)
  3) the orbital mind control lasers are working...
  
  :)

• 35 Sean Corfield // Aug 14, 2008 at 11:07 AM

  @Ike, you should live in my wife's family - they are Borg-like in their communication. And it's rubbing off on me: I constantly find myself thinking something only to have my wife answer my unspoken question - and we often say the same thing at the exact same time. After nine years of being together, it no longer freaks me out but it is still a bit spooky at times :)

• 36 Tom Chiverton // Aug 15, 2008 at 7:37 AM

  Even if you are not on CF8, adding your own cache is fairly trivial, and worth the hassle in order to be able to use cfqp.

• 37 Sean Corfield // Aug 15, 2008 at 8:02 AM

  @Tom, certainly cfqp is more important - it's better to be correct and slow than wrong and fast ;)

• 38 ike // Aug 15, 2008 at 9:14 AM

  agree with Tom re: caching -- and there are several caching projects on riaforge.org that could probably be used for that. There's a built-in caching utility in DataFaucet that does it and most of the major frameworks have one. Not that they're all "ready-made" solutions, you might have to tweak them, but they're available. I've just barely started working on a new "framework" called CacheBox that will hopefully provide an invisible caching layer (meaning it will be mostly self-configuring so you don't have to configure it), that will automate the selection of caching strategies ranging from naive expiration based caching to soft-cache to memcached to Railo's cluster scope, all dependent upon what is needed and available on a given machine. The hope really is to help free up everyone's time from reinventing the caching wheel so we can focus on other things. :)
  
  @Sean - I can't say I've ever had that experience... I think I might enjoy it. :)

• 39 ike // Aug 15, 2008 at 3:34 PM

  @Sean - in a few weeks or so when I have a working draft, I'd love to get some peer review of the CacheBox code if you're willing and able. :)

• 40 Sean Corfield // Aug 15, 2008 at 3:55 PM

  @Ike, unlikely I'll have time but feel free to ping me on IM anyway.

Leave a Comment