Posted in ColdFusion | Posted on 07-29-2008 | 4,562 views
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.
http://xkcd.com/327/
https://download.spidynamics.com/Products/scrawlr/...
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.
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
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...
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...)
*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.
Keep the good work Ray
Mamdoh
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.
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.
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.
I think having this enabled alone would prevent almost all injections, but combined with cfqueryparam you should be pretty safe.
Now I seem to have a problem using it to select. I have this date:
<CFSET temp = CreateDateTime(2009, 10, 17, 0, 0, 0)>
<CFSET date17 = CreateODBCDateTime(temp)>
None of the following seem to work w/o a type error on a select (CF7, SQL datetime column):
<CFQUERYPARAM VALUE="#date17#">
<CFQUERYPARAM VALUE="#date17#" CFSQLTYPE="CF_SQL_TIMESTAMP">
<CFQUERYPARAM VALUE="#date17#" CFSQLTYPE="CF_SQL_DATE">
<CFQUERYPARAM VALUE="#date17#" CFSQLTYPE="CF_SQL_DATETIME">
Can you let me know where I'm going wrong? My searches make me feel like I'm the only one getting hung up here.
I still have the question about cfqueryparam and dates. Is this type the only exception to the rule, where it needs cfsqltype?
UPDATE users
SET suUpdate = suSUUpdateDate
WHERE suUpdate= <CFQUERYPARAM VALUE="#date17#" CFSQLTYPE="CF_SQL_TIMESTAMP">
I love this tag, so it's frustrating that date values seem to have so many rules (seemingly).
...but again, only w/ the cfsqltype. So, again, my only confusion is why date/time seems to have problems w/o it.
I set up a simple test, w/ an new table, and attempted to just insert a date (col is datetime) using cfqueryparam:
<CFQUERY DATASOURCE="#APPLICATION.data_dsn#" NAME="add_date">
INSERT INTO test_tbl (tDate)
VALUES (<CFQUERYPARAM VALUE="#Now()#">)
</CFQUERY>
That gets me "Conversion failed when converting datetime from character string" (I also tried ParseDateTime(Now())).
Adding CFSQLTYPE="CF_SQL_DATE" works, and passes {ts '2009-10-26 14:30:06'}, but only 2009-10-26 00:00:00 shows up in the table. As I mentioned before, CF_SQL_TIMESTAMP fixes this. It also has the same behavior in SELECT statments.
So far, this seems to just be a problem w/ Now(), since <CFQUERYPARAM VALUE="1/2/2008 13:45"> works fine w/o a type. As does Now() if used w/ DateFormat/TimeFormat. The same is true of CreateODBCDateTime and CreateDateTime, and only working w/ Date/Time formats.
I think I understand now how to use date/time w/ CFQueryParam, but I guess I'm just confused about this aspect of it, and date/time alone needing this special attention.
So a cfqueryparam w/o a type means varchar which means string. You don't want to use that for dates.
If you want a full date/time, then you want timestamp. Period. If you use now() along with timestamp, it should work fine.
You can get away w/ not using CFSQLTYPE as long as you use DateFormat() and TimeFormat(). I'm thinking this is almost preferable, unless you're going to be in the habit of using CFSQLTYPE. Although, one could argue this just means you now need to be in the habit of formatting your dates.
I guess this all just seems like the CF version of an "i before e..." kind of thing.
I guess I had missed or overlooked that in my indoctrination into cfQP. I also mislead myself in how well it seemed to handle numerics any fuss.
Thanks for sticking w/ my through this line of posts. I'm going to work out a personal best practice going forward.
[Add Comment] [Subscribe to Comments]