August 6, 2008
You know the one I mean. It's been doing the rounds lately. This morning I checked my webmaster mailbox and found hundreds of error messages from my blog. Normally, I only get one or two a day due to URL hackery attempts so something was clearly up. Yup, that SQL injection attack had been tried on my blog. Fortunately, the code uses
cfqueryparam in all of the queries (it's an old version of BlogCFC but Ray has always been really good about this!) so every attack was repelled, with just an error raised due to the URL string being too long for the DB column.
Thank you Ray!
Moral: always use cfqueryparam!
Comments
Yup, I also saw hundreds of emails today from one of the CF sites I work on.
Good that cfqueryparam is to the rescue.
Posted By Anuj Gakhar / Posted At
8/6/08 11:14 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
Posted By todd sharp / Posted At
8/6/08 11:19 AM
Posted By Steve Withington / Posted At
8/6/08 11:25 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!
Posted By Jim Pickering / Posted At
8/6/08 11:33 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.
Posted By Jason Fisher / Posted At
8/6/08 1:39 PM
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.
Posted By ike / Posted At
8/6/08 2:10 PM
Transfer uses cfqueryparam everywhere so it's "safe".
Posted By Sean Corfield / Posted At
8/6/08 2:55 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...
Posted By todd sharp / Posted At
8/6/08 5:54 PM
Almost every website, I have developed in CF, are under constant attacks since yesterday.
Posted By Dmitry Yakhnov / Posted At
8/7/08 12:07 AM
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
Posted By Gert Franz / Posted At
8/7/08 12:10 AM
@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?
Posted By Sean Corfield / Posted At
8/7/08 12:48 AM
@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
Posted By Dane Pescaia / Posted At
8/7/08 2:17 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.
Posted By Tom Chiverton / Posted At
8/7/08 5:37 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.
Posted By Sebastiaan / Posted At
8/7/08 6:36 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]
Posted By Patrick McElhaney / Posted At
8/7/08 8:39 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
Posted By William from Lagos / Posted At
8/7/08 9:00 AM
@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.
Posted By Pete Freitag / Posted At
8/7/08 3:04 PM
>> RewriteCond %{QUERY_STRING} EXEC\(@S\) [NC] RewriteRule .* - [F]
Which goes where and does what exactly?
Posted By samuel / Posted At
8/7/08 10:40 PM
@Samuel, it's an Apache directive - so it goes in your httpd.conf file or .htaccess file if you are using Apache.
Posted By Sean Corfield / Posted At
8/7/08 11:23 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.
Posted By Dane Pescaia / Posted At
8/8/08 12:18 AM
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
Posted By Gert Franz / Posted At
8/8/08 1:19 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.
Posted By Pete Freitag / Posted At
8/8/08 12:20 PM
@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.
Posted By Mary Jo Sminkey / Posted At
8/8/08 2:51 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.
Posted By Sebastiaan / Posted At
8/9/08 2:13 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?
Posted By Jeff Knooren / Posted At
8/9/08 4:00 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).
Posted By Sean Corfield / Posted At
8/9/08 2:09 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.
Posted By ike / Posted At
8/9/08 3: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.
Posted By Jason Fisher / Posted At
8/9/08 5:15 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.
Posted By ike / Posted At
8/9/08 5:57 PM
The only drawback with cfqueryparam is that you can't cache the query using cachedwithin.
Posted By Jordan / Posted At
8/14/08 9:47 AM
@Jordan, as of CF8 you can cache queries using cfqueryparam.
Posted By Pete Freitag / Posted At
8/14/08 1:25 PM
@Jordan, as of CF8, you can use cfqueryparam with cachedWithin.
Posted By Sean Corfield / Posted At
8/14/08 1:38 PM
Oops, I commented before noticing Pete had also said the same thing :)
Posted By Sean Corfield / Posted At
8/14/08 1:40 PM
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...
:)
Posted By ike / Posted At
8/14/08 1:58 PM
@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 :)
Posted By Sean Corfield / Posted At
8/14/08 2:07 PM
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.
Posted By Tom Chiverton / Posted At
8/15/08 10:37 AM
@Tom, certainly cfqp is more important - it's better to be correct and slow than wrong and fast ;)
Posted By Sean Corfield / Posted At
8/15/08 11:02 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. :)
Posted By ike / Posted At
8/15/08 12:14 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. :)
Posted By ike / Posted At
8/15/08 6:34 PM
@Ike, unlikely I'll have time but feel free to ping me on IM anyway.
Posted By Sean Corfield / Posted At
8/15/08 6:55 PM
Post Your Comments
Hosting provided by
