What? Folks aren't using cfqueryparam?
It seems that while I was gone there was some noise about CF and SQL Injection. I find this a bit surprising as I thought everyone was using cfqueryparam now, aren't they? In all seriousness, here are some few links to consider:
- Announcing the first ever International Operation cf_SQLprotect: I'm a few days late on this (let's see - Friday at this time I was downtown Austin), but Brad Wood decided that July 25th would be a good day to scan your code for sql vulnerabilities. While we should do this constantly, there is nothing wrong with doing it again, over your entire code base.
- Brad linked to QueryParam Scanner by Peter Boughton.
- Brad also linked to Daryl Banttari's scanner.
Now all we need is a one stop var scope checker/queryparam checker in one tool.
Comments
I would like to share with you this utility which designed by HP. The program call Scrawlr which scan any URL and finds SQL injection vulnerabilities. It will crawl a website while simultaneously analyzing the parameters of each bage for SQL injection.
https://download.spidynamics.com/Products/scrawlr/...
https://download.spidynamics.com/Products/scrawlr/...
# Posted By Mamdoh
| 7/29/08 2:24 PM
@Ray - Unfortunately, it doesn't surprise me in the least that many, if not most developers are not using cfqueryparam, and I can guess at precisely why: no one learns it.
I think most developers (of any technology) fall into three groups: 1) folks who have been doing it "forever"; 2) folks who took a class to learn it; and 3) folks who learn by reading a book.
The reality is that most of the folks in group 1 assume that they know everything about the language; things that have been added to CF since they learned it with version 1 are nice, but when a new version comes out, they will at best scan the docs for the "gee whiz" features. A single tag that improves security on queries? Not likely to garner much attention.
The folks in group 3 will get it - they will have read CFWACK (partly because it's really good, partly because there's not much other choice), and they will be in your camp of folks who think that not using it is crazy.
But that second group: lord help them. They signed up for a course at a local Adobe certified training center, so the only exposure they've had to CF is the horrible, horrible pathetic piss-poor excuse for an introduction to ColdFusion that is "Fast Track to ColdFusion." It never brings up cfqueryparam. Not once - even in the advanced course. Sadly, that's not even the worst of its sins, not by far. If they didn't take it at an Adobe certified center, then they may have taken it from a corporate training center that isn't certified, and thus probably got a one day course (yup, you read that right) written by ElementK, which is the only thing out there that manages to make the certified course look good. Or, they may have taken it at a community college, in which case their prof was probably someone from group 1 above.
So I would bet just about anything that the sad truth is that most CF developers just simply don't know about cfqueryparam, and so yes, SQL injection attacks can and will still happen.
What needs to be done to solve the problem? Well, there's really not much to do about the group 1 folks, and the group 3 folks are already OK. As for the folks in group 2, well, that's a much longer discussion for a different day, and I don't want to get too far astray. Maybe we can schedule an unofficial BOF at MAX in a bar somewhere for everyone that shutters when the words "adobe" and "certified" get used together.
I think most developers (of any technology) fall into three groups: 1) folks who have been doing it "forever"; 2) folks who took a class to learn it; and 3) folks who learn by reading a book.
The reality is that most of the folks in group 1 assume that they know everything about the language; things that have been added to CF since they learned it with version 1 are nice, but when a new version comes out, they will at best scan the docs for the "gee whiz" features. A single tag that improves security on queries? Not likely to garner much attention.
The folks in group 3 will get it - they will have read CFWACK (partly because it's really good, partly because there's not much other choice), and they will be in your camp of folks who think that not using it is crazy.
But that second group: lord help them. They signed up for a course at a local Adobe certified training center, so the only exposure they've had to CF is the horrible, horrible pathetic piss-poor excuse for an introduction to ColdFusion that is "Fast Track to ColdFusion." It never brings up cfqueryparam. Not once - even in the advanced course. Sadly, that's not even the worst of its sins, not by far. If they didn't take it at an Adobe certified center, then they may have taken it from a corporate training center that isn't certified, and thus probably got a one day course (yup, you read that right) written by ElementK, which is the only thing out there that manages to make the certified course look good. Or, they may have taken it at a community college, in which case their prof was probably someone from group 1 above.
So I would bet just about anything that the sad truth is that most CF developers just simply don't know about cfqueryparam, and so yes, SQL injection attacks can and will still happen.
What needs to be done to solve the problem? Well, there's really not much to do about the group 1 folks, and the group 3 folks are already OK. As for the folks in group 2, well, that's a much longer discussion for a different day, and I don't want to get too far astray. Maybe we can schedule an unofficial BOF at MAX in a bar somewhere for everyone that shutters when the words "adobe" and "certified" get used together.
# Posted By Rob Huddleston
| 7/29/08 9:13 PM
Hi all,
Been thinking about this recently, and having some older sites that were written before I was around, there is quite a lot to do in some places.
Would it not also be sensible to stick a little checker in OnRequest to check all URL and FORM variables for characters that could cause a problem? (semi-colons, etc) I can see problems with this, but I think there is a valid use for it somewhere.. assuming you can read URL and FORM vars from onRequest, can't say i've ever tried.
Is there anything like this already? Had a look on RIAForge but couldn't find anything related.
Any observations I have missed? I am NOT proposing this as a replacement for cfqueryparam, just maybe a quick way of minimising the problem while it is fixed.
Cheers
Mat
Been thinking about this recently, and having some older sites that were written before I was around, there is quite a lot to do in some places.
Would it not also be sensible to stick a little checker in OnRequest to check all URL and FORM variables for characters that could cause a problem? (semi-colons, etc) I can see problems with this, but I think there is a valid use for it somewhere.. assuming you can read URL and FORM vars from onRequest, can't say i've ever tried.
Is there anything like this already? Had a look on RIAForge but couldn't find anything related.
Any observations I have missed? I am NOT proposing this as a replacement for cfqueryparam, just maybe a quick way of minimising the problem while it is fixed.
Cheers
Mat
@Matt
This is exactly what I did when a big e-commerce site I built was attacked.. Or rather I had a scanner in place which alerted me to the first set of attacks so no damage was done, but then I just thought I'd check the logs in case they had got past it (which I didn't expect).. Guess what? I began to witness in almost realtime (via log downloads) an attack in progress that was attempting to rip off personal data from the D/B. They actually gained access to the admin system.. (Not having the admin URL on /admin is a GOOD idea).. However I was able to shut them down before they got any real data.. Phew.
Anyway, I do think the URL and Form filter is a good idea although by no means a proper solution. I implemented this on many of my sites as a first level of defense. I also added a global error handler to ensure no useful data could be exposed to the hackers. The filter has caught many an attack but the correct way of doing this it to ensure all queries use cfqueryparam.
I'm not using Transfer which takes care of a lot of this risk but making sure that every list query I build manually uses cfqueryparam...
This is exactly what I did when a big e-commerce site I built was attacked.. Or rather I had a scanner in place which alerted me to the first set of attacks so no damage was done, but then I just thought I'd check the logs in case they had got past it (which I didn't expect).. Guess what? I began to witness in almost realtime (via log downloads) an attack in progress that was attempting to rip off personal data from the D/B. They actually gained access to the admin system.. (Not having the admin URL on /admin is a GOOD idea).. However I was able to shut them down before they got any real data.. Phew.
Anyway, I do think the URL and Form filter is a good idea although by no means a proper solution. I implemented this on many of my sites as a first level of defense. I also added a global error handler to ensure no useful data could be exposed to the hackers. The filter has caught many an attack but the correct way of doing this it to ensure all queries use cfqueryparam.
I'm not using Transfer which takes care of a lot of this risk but making sure that every list query I build manually uses cfqueryparam...
# Posted By James Allen
| 7/30/08 5:45 AM
Am I right in thinking that an Access datasource isn't vunerable to SQL injection? If so, this might be why many developers who start in M$ Access and move to SQL never understand the problems?
And to be honest, when I first started using CF, back in about 1998 I never knew about the function, concentrating on just getting the code/site delivered to the client (and getting paid was more important than RTFM...)
And to be honest, when I first started using CF, back in about 1998 I never knew about the function, concentrating on just getting the code/site delivered to the client (and getting paid was more important than RTFM...)
@Mat
*In Theory* this is a workable solution.
Sadly, theories don't always play out. XSS, XSRF, and SQL Injection are difficult to manually filter- which is why web application firewalls are getting a lot of attention.
It wouldn't be a bad idea, I think, to set up a web application firewall (I don't know any of them well enough to recommend) on an older site to give you the breathing room you need to make improvements like cfqueryparam.
*In Theory* this is a workable solution.
Sadly, theories don't always play out. XSS, XSRF, and SQL Injection are difficult to manually filter- which is why web application firewalls are getting a lot of attention.
It wouldn't be a bad idea, I think, to set up a web application firewall (I don't know any of them well enough to recommend) on an older site to give you the breathing room you need to make improvements like cfqueryparam.
# Posted By Matt Osbun
| 7/30/08 6:50 AM
@RobH: This is a bit OT now, but it's sad to hear about the FTCF course. I remember taking it (I had to as a prereq to teaching) and it was quite intense - 3 days full of training. I remember it doing a good job, but honestly don't remember it they mentioned cfqueryparam back then (this was quite some time ago). I've toyed with the idea of doing CF training myself, over Breeze, but never got around to it while I was a contractor.
# Posted By Raymond Camden
| 7/30/08 7:16 AM
For the security reason I stopped using Microsoft access and start to use SQL server or MSDE. And I don't use online stream query but I use Stored Procedures and CFStoredProc tag to execute them. Store procedures not just more secure but faster to process.
Keep the good work Ray
Mamdoh
Keep the good work Ray
Mamdoh
# Posted By mamdoh
| 7/30/08 8:49 AM
Warning, OT story I've told before (I'm old, I'm allowed to repeat):
I wrote DeathClock (http://www.deathclock.com) way back in 90something or another. The first iteration was Perl. When I switched to CF, I used Access for the db. I couldn't afford (and was too cheap) to use SQL Server. Access worked fine, even with the high traffic (4+ million page views per month), but I cached the heck out of things.
I wrote DeathClock (http://www.deathclock.com) way back in 90something or another. The first iteration was Perl. When I switched to CF, I used Access for the db. I couldn't afford (and was too cheap) to use SQL Server. Access worked fine, even with the high traffic (4+ million page views per month), but I cached the heck out of things.
# Posted By Raymond Camden
| 7/30/08 8:57 AM
I always though Access was safer because it could not do sub queries. Which is what most SQL injections are.
By sub queries i mean tacking on a query to the WHERE section of the query to drop a table or delete records. Last i knew Access could not do them.
By sub queries i mean tacking on a query to the WHERE section of the query to drop a table or delete records. Last i knew Access could not do them.
Hi Ray,
Thanks for the info and links!
As a relatively new Coldfusion developer (2 1/2 years), I've always used cfqueryparam in my code.
Unfortunately, I'm also responsible for maintaining many older apps, some up to 10 years old! Daryl and Peter's scanners have reduced DAYS of code auditing to just a few hours.
Thanks for the info and links!
As a relatively new Coldfusion developer (2 1/2 years), I've always used cfqueryparam in my code.
Unfortunately, I'm also responsible for maintaining many older apps, some up to 10 years old! Daryl and Peter's scanners have reduced DAYS of code auditing to just a few hours.
# Posted By Mike Hodgson
| 7/30/08 10:56 AM
MySQL gives you the option to disallow more than one query in a request.
I think having this enabled alone would prevent almost all injections, but combined with cfqueryparam you should be pretty safe.
I think having this enabled alone would prevent almost all injections, but combined with cfqueryparam you should be pretty safe.
http://xkcd.com/327/