Preventing SQL Injection attacks in my CFML application with db-dot-cfc

Sat, May 25, 2013 at 1:45AM

Preventing SQL injection attacks against the database is often something that is easy to know how to do as a developer, but also something you have to discipline yourself and other developers on your team to do consistently all the time. I often am in a rush, and find myself overlooking or modifying code incorrectly. These kinds of mistakes can lead to security holes in an application - sometimes serious ones. While we haven't had any security breaches so far, I want to have the code preventing this the best I can.

To avoid making mistakes, I wrote the db-dot-cfc project. It is able parse SQL statements before they run to analyze whether you've securely implemented all your parameters such as numbers and strings. It also has plenty of flexibility so that it can run faster in production with fewer checks, and run full featured on a development machine. In Jetendo CMS, db-dot-cfc is also extended to verify if the site_id column is used for all tables in Jetendo CMS that have a site_id column.  This ensures that content isn't mixed between domains by mistake. There are ways to disable this security when you want to, but with it on by default, it makes it very hard for me to accidentally write an insecure query.

Unfortunately, this new component was totally different code from what I have been using for queries.  While the SQL mostly stays the same, the way you build and SQL statements had to be completely replaced to implement db-dot-cfc.

I felt an important milestone for making Jetendo CMS open source in the future was to make sure all the queries are secure. Once the source code is out there other people would be able to see its security flaws. Perhaps, someone might even try to compromise our server or get access to data they shouldn't. This meant I had to rewrite the code for over 1,500 queries.  That's a pretty major amount of rewriting!

I started on this extremely tedious task three days ago. This kind of task may sound like something you might be want to automate. I tried to as much as possible with regular expressions and find/replace searches on the entire project. In the end, I had to manually review and test every query again. However some of the queries are hard to execute, so I probably have caused some bugs since I've only had the time to test the most commonly used features. In some cases, securing the queries was more difficult because of how they are built in the app. Perhaps 100 of the queries required extra steps to rewrite. After a 30 hours of seemingly endless updates to the code and late hours with lost sleep, and lots testing today, I've been able to launch the more secure query code to production. Over the new few days, I expect I'll have to fix a few more bugs, but it seems to be working great so far.

I'm excited to have this work behind me.  Now Jetendo CMS is more secure then ever before.

Bookmark & Share