I have this comic strip taped on my wall to always remind me of using cfqueryparam.

http://xkcd.com/327/

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.spidynamic...

@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.

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

@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...

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...)

@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.

@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.

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

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 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.

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.

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've been using it more and more comprehensivily, but I keep having problems w/ date/time. It seems to be the only time I need to use CFSQLTYPE. I found my Now()s weren't getting inseted/updated w/ time w/o using CF_SQL_TIMESTAMP.

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.

Get rid of the createODBCDateTime.

EDIT: My tests of those other types did produce results. I just had the wrong page loaded.

I still have the question about cfqueryparam and dates. Is this type the only exception to the rule, where it needs cfsqltype?

Removing the createODBCDateTime didn't work, and it also seems to throw a fit if I use cfsqtype in a cfqueryparam in a WHERE statement in an update:

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).

Whats the database column type for suUpdate?

Sorry again...the error there was again mine (not havign a good run here), and sorry for wasting your time again. The update runs fine...

...but again, only w/ the cfsqltype. So, again, my only confusion is why date/time seems to have problems w/o it.

I just wanted to follow up from my last post...in part because I probably sounded like a nutcase.

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.

Holy smokes thats a lot. :)

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.

Yes, except that cfqueryparam w/o a type that is a number is not a string. My understanding of cfQP (I'm tired of typing it out ;) ) was that it checked w/ the DB what the col type was, and validated the data. I guess it was my assumption it would see ODBC-formatted time correctly, as it does w/o using cfQP.

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.

Nope, according to the docs, if you leave the type off, the default is CF_SQL_CHAR.

You are correct, sir! ;)

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.