SQL Injection is only the beginning. <cfqueryparam> is the easy part.

Well, I'm sure most will be glad that I am not going to try to beat this SQL Injection/cfqueryparam stuff any further into the ground. We get it now, right? Use cfqueryparam!! Unless you have a good reason not to, then use something else that will serve the purpose of creating a prepared statement for use in a query. You'll also have to do something to handle dynamic table names, dynamic sort statements, etc. These topics have been so well handled by other bloggers that I am not going to try to take it any further. Look at the bottom of the page for links to some of the best explanations.

But this post is not about SQL Injection or about <cfqueryparam> it is about having our eyes opened.

I have been very pleased to see people taking security a little bit more seriously. I think this little "rash of attacks" has been good for the community as a whole. It's time to open our eyes and see that we can't just write and release. The security plan for our applications needs to be as much a part of the project planning process as any other piece. Security should not be an afterthought.

Like I said, I am glad to see the great reaction to this from the community. People are actually starting to take these threat seriously. And that is awesome. Now, I hope the community can prepare themselves for the other threats. This SQL injection thing has been a pretty big deal, but it is by no means the only show in town. We have a lot more to worry about, and we need to handle these threats with as much vigor and attention as we have given the SQL injection threat.

If a sites is susceptible to SQL injection attacks, it is, likely, because the developer did not take the time to add cfqueryparam to queries. So I can only assume that the same developers have not taken the time to protect their applications from other attacks. Cross-Site Scripting (XSS), Cross-Site Request Forgeries (XSRF), On-Site Request Forgeries (OSRF), etc.

We need to be taking all of these threat seriously. Let's not wait for the next hacker article to cause a "rash of attacks". If your application accepts user input, and more importantly, if your application then displays that user output (even if only to the administrator), then your site is, likely, susceptible to some sort of Cross-Site or On-Site attack.

I am going to try to post some entries/articles further detailing these attacks and how we can combat them. This will require some research and testing. In the meantime, let's get those cfqueryparams in place.

I suspect that most everyone has seen these posts already. I am placing them here for archival purposes so that anyone going through my security series later will have access to good info on SQL injection.

Links to blog posts about SQL Injection and <cfqueryparam>:

Comments
Doug's Gravatar So, after reading this and other blogs, it appears that MSSQL is immune to injection via a character field. All the MSSQL/CF examples I see are of the type www.mysite.com?myid=123 where myid is an integer, not a character field.

Am I right in this or are there examples with MSSQL where myid could be charactgers? The reason I ask is that I've inherited a CF3-CF5 site with hundreds and hundreds of queries and zero cfqueryparam's and I want to quickly prioritize what needs to be immediately cfqueryparamed and what can wait for the second run.

Doug
# Posted By Doug | 1/2/09 7:18 PM
Jason Dean's Gravatar Doug, Thanks for the comment. Your comment has a touch of accuracy, but not enough to warrant not adding cfqueryparams to your code.

1. MSSQL is not immune to SQL injection attacks. It is just as vulnerable as any other DBMS, this is because it is not the database that has the vulnerability that opens your application up to a SQL injection attack, it is your application and the engine it runs on.

The reason that most SQL injection attack samples use numeric fields is because they are the easiest to exploit. This is because ColdFusion automatically escapes single quotes that are passed into the query. This means that an extra single quote passed in, in most cases, cannot be used to prematurely end part of a WHERE clause to allow for an injection attack. So, really, it is ColdFusion that is preventing the attack, not MSSQL.

2. ColdFusion's auto-escaping quotes ONLY protect string fields

3. Auto-escaping quotes is not without its issues. There are a couple of ways that SQL injection attacks can be launched against the queries anyway. The first is an exploit against MySQL, I won't go into detail, you can find more on it here: http://tinyurl.com/65cost

The second way around the auto-escaping is if your queries use the PreserveSingleQuotes() function. If you are using PreserveSingleQuotes() in your queries (which is very possible in an older app) then single quotes will not be escaped and you become vulnerable.

One final thing to keep in mind. If you are using any version of ColdFusion prior to CF8, remember that you cannot cache queries that use cfqueryparam.

Frankly, I think you need to take the time to add the cfqueryparams to all of your queries. I know it sucks, it's tedious work. But in the long run, your application will run faster and more securely. Also, be sure to look at QueryParam Scanner to help you find them all: http://qpscanner.riaforge.org/
# Posted By Jason Dean | 1/2/09 9:39 PM
Doug's Gravatar Thanks, Jason, for the quick reply. I guess what I was trying to get across was that IF the WHERE clause was using an integer then that should be a higher priority for a <cfqueryparam> than a WHERE text field. I have done a lot of searching and still haven't found an example for MSSQL and CF of a SQL text injection, only for MySQL.

With so many queries ( well over 1000 as it turns out and many duplicates ) I really wanted to prioritize which queries got "treated" first to stop "casual" attacks first, and then fix the remainder of the queries as a second phase. So WHERE integer clauses have become my top priority and cfqueryparaming everything else will come next.

Luckily, the code has very few PreserveSingleQuotes() in it, so that has not been a problem.

BTW, loved your blog tutorial on ColdBox. If you ever want to continue it you will have at least one reader here.

I sure wish this code I've inherited were written in CB. It would surely save quite a bit of time by reducing the number of templates and number of duplicate cfqueries I have to deal with. Unfortunately, a good portion of the code was written with CF2.x and CF3.x and is as spaghetti as it gets. Somehow, it still works, though.

Doug
# Posted By Doug | 1/3/09 11:34 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.9.1. Contact Blog Owner