Friday, July 25, 2008

CFQUERY STRICT="TRUE"

There's been a lot of SQL Injection attacks against Coldfusion following the attacking coldfusion article a few weeks ago. There are lots of very old CF apps out there and some newer ones which are a little slack when it comes to using CFQUERYPARAM.

I have a simple solution which could be easily added to Coldfusion. All we need is a STRICT option to disable cfml variables within CFQUERY, still allowing flow control tags, but any attempt at output in the cfquery will throw an error.

This could be configurable as STRICT, OPTIONAL or NONE at the server/application level and there could be an override option on the CFQUERY tag which would still throw in the STRICT mode.

With this enabled, testing any application for this problem would become a walk in the park.

5 comments:

Anonymous said...

Did you actually submit this to Adobe?

I really like that idea, but then that means you would also lose the ability to do a dynamic order by clause etc. That has the potential of being very dangerous or totally benign depending on how it is implemented.

I blogged a safe way to do it here:
http://www.codersrevolution.com/index.cfm/2008/7/22/When-will-cfqueryparam-NOT-protect-me

Zac Spitzer said...

I haven't submitted it yet, just thought of it...

yeah I know the hassle of the dynamic order by, but that would be easily solved with an extra cf tag like CFQUERYSTATIC, which only allows only valid characters for a single column name

that said you can always fall back on a QoQ for doing sorting which keeps the number of query parameters down

Anonymous said...

As I am the person who actually originally brought that option up about 6-10 months ago.

I would like to say that, that is not really what I was refering too when I made the comment in CFAussie :-(

The flag option would need to a security option, that if enabled would allow the tag cfquery tag, utilise the cfqueryparam underneath the hood. This would allow for older websites running under any newer version to work with the one flag being set.

This is basically allowing me as a developer to say that I would like ColdFusion to automaticall inject the security service and use the right cfqueryparam for the sql option in question.

After all, the JDBC driver can already decide if ColdFusion needs to bind variables or not. So that leaves me thinking that with strong type checking before calling your ORM service / model, we can protect ourselves with minimal fuss.

ArmchairDeity said...

Hey guys... I like the idea, but I don't think it's practical to implement. I can't see how you would force people to use cfqueryparam accurately based on their intent. For example, you can dynamically construct queries if they don't take input directly from form or variable scope, and simply output them... so your FROM clause or your ORDER BY clause can be dynamic and output with a simple #orderBy#.

The real problem is that SQL is very expressive and even (depending on the context) very individualized and you would have to be able to discern people's intent to be able to accurately enforce such a strict mode. Frankly, if it can't be done perfectly it's not worth doing at all... taking the CF team's time to try it and put all that effort in only to have it be useless would be a huge tragedy considering the number of other features everyone wants to see.

To be truly honest, it's the programmer's responsibility to use the cfqueryparam and not the server's responsibility to insure that you write secure and stable applications. I'm starting to cringe every time I see some sort of problem out there in the world and people start popping up saying "Let's make the server think this is mandatory!!"

It's been discussed here, btw... it's not a new idea: http://forta.com/blog/index.cfm?mode=entry&entry=512716E6-3048-80A9-EF9C4437D3C9EF9D

Zac Spitzer said...

Andrew, I think your suggestion would be much better implemented as a refactoring tool in CfEclipse.

Your idea got me thinking and that's where I came up with my approach.

As a server side solution your approach would be so fraught with problems that it wouldn't really help.

The most complicated part of the problem is that you are going from a loosely typed eco-system to a database which is rather strict about data types.

How often have you set the extra options on CFQUERYPARAM, with regards to the extact number type, decimal points, dates or nulls?

Tho, I have long wished for a null="auto" option

My understanding with the JDBC bind options are that if your provider supports it, they are always used. If the JDBC provider doesn't support binds, you probably shouldn't even be using it...