Jared Rypka-Hauer, Lead ColdFusion Developer, Minneapolis, MN

This whole 0x000000 thing really has me thinking, and I'd like to postulate a few more things you can do to offset the likelihood of SQL injection becoming a serious pain in your day. While I think that cfqueryparam is a very significant tool to defeat this attack, There are several other things you can do to alleviate the problem.

I'd like to propose some of them for conversation:

1. Never run ColdFusion, the webserver and your DB under the system account... always create separate accounts for each and restrict them to groups, roles and permissions appropriate for the activities they will undertake.
2. If you have access to do so, create a SQL Server user specifically for your ColdFusion DSN and restrict it to only the activities required for the application.
3. Failing that, use the settings in the CF admin to (or ask your hosting provider to) revoke privs for things like CREATE and GRANT
4. Whenever possible, in a production environment, run your database server and your web/application server on separate hardware.
5. Always shut off robust exceptions on production boxes... as the article says: it screams PLEASE HACK ME to someone with even a little skill.

While I'm sure this is by no means a comprehensive list, and I'm sure that most folks already know and do these things, it's just some "Cliff's Notes" that come to mind. While none of these things will prevent SQL injection, they can help minimize the impact of an attack should one sneak thru. The only way to really prevent SQL injection is to cfqueryparam your code religiously and validate the inputs from your form fields.

On another note, I'm curious about the MySQL comments made in the 0x000000 article... I wanna know if MySQL really is not protected because of the way cfqueryparam works? If that's the case then Adobe need to get on a fix ASAP because... well... think about it.

[NOTE: MySQL is protected by using cfqueryparam. The article refers to the fact that ColdFusion will automatically protect any text datatypes (like varchar) that are used within a cfquery tag in the format '#varName#' by escaping single quotes for you. The problem is that MySQL can escape single quotes using a backslash character (\) as well, but ColdFusion doesn't handle this automatically, giving hackers a way to embed a single quote that's actually successful at injecting SQL. Using bind variables (ala cfqueryparam) prevents this entirely, tho', and prevents SQL injection at all.]

That's BAD!!

Laterz...

Your queries are protected if you use cfqueryparam and MySQL (barring any dynamic SQL madness). What the author was referring to was the additional way MySQL allows for single ticks to be escaped. This is not a problem with cfqueryparam, but rather a problem with cfquery. I think Adobe should attempt to update the behavior of cfquery to account for MySQL's behavior, but there some difficulties to that as Jochem pointed out:
http://www.codersrevolution.com/index.cfm/2008/7/13/Just-when-you-felt-safe-SQL-Injection-and-MySQL#c226288B7-F453-2097-5D20D1E636674C11

The only real answer is just to always cfqueryparam.

Fair comment on disallowing CF's SQL account CREATE privilages, but surely that will prevent stored procedures from running that use CREATE for generating temporary memory tables?

If you know what the attack consists of (obviously you didn't publish the code for good reason) then are you able to do a test and attack your own CF/SQL setup with cfqueryparam in place? And do it again against code that uses stored procedures?

I'd like to try it myself (on test system!) but I have no idea what the injection consists of. It's very well talking about best practice to secure your site, but ultimately it needs to be put to the test otherwise you will never really know how water tight you are until someone attacks you for real.

Perhaps Adobe should put aside a day to run their own tests against the latest attacks and write a KBA detailing PROVEN techniques to prevent specific types of attacks.

Oh, I was referring to this nasty attack blogged about here: http://www.coldfusionmuse.com/index.cfm/2008/7/18/Injection-Using-CAST-And-ASCII

I just blogged about the idea of having a strict mode for cfquery which throws an error when cfml variables are used, ie force cfqueryparam usage

http://zacster.blogspot.com/2008/07/cfquery-stricttrue.html