Security – How to Test and Secure ColdFusion Queries Against SQL Injection Attacks

coldfusionSecuritysqlsql-injection

I'm running Coldfusion 8 and SQL server 2008.

I've been building serveral forms that insert data into the database from external users, we have a custom built security module built by the guy who I've taken his job.

1) How can we test our HTML forms to ensure that we're protected from SQL injection attacks?

2) How do I secure CFqueries in CFC's?

3) What are some best practices in terms of SQL & Coldfusion for security?

— A lot I know!

Best Answer

This article from Adobe discusses most of the issues you'll need to deal with.

The best protection against SQL injection is to use a parametric query - that is, a query that is complete and can be compiled by the SQL engine but that you attach data to after the fact. I haven't used Coldfusion in many years, but it appears that it doesn't support parametric queries - the article I linked lists some solutions to the problem.

Related Solutions

How to Format SQL Queries – Best Practices

For source control reasons, we have linebreaks after every where clause, or comma. So your above turns into

SELECT bla 
     , bla2 
     , bla 
FROM   bla 
WHERE  bla=333 
  AND  bla=2
ORDER  BY nfdfsd
        , asdlfk;

(tabbing and alignment has no standard here, but commas are usually leading)

Still, makes no performance difference.

SQL Testing – How to Test Data Based on SQL Queries

Been there, done that.

First let's make something clear:

That's not unit testing. Unit testing is about code. You are not runnning the tests after a code change to test if code alterations introduced a bug or unwanted behavior. Instead you want to run some routines at the end of a business day to see if some business performance indicator has met some predefined goal or is between some predefined threshold. If the data doesn't comply with the 5% rule, that's not because a bug in the software.
What you are really doing is monitoring your business performance based on the data records.
That could also be made to detect suspected deviations.

What I have made is this:

I've programmed some routines (the language is not important) and scheduled them in a crontab to run every date at a certain time.
That routines populate a table.
That table is used by a web app to render a report.

I have not used any specialized software so I cannot recommend you one.

You can integrate those reports into any portal or any Balanced Scorecard software that allows ad-hoc conectors to customize dashboards.

Best Answer

Related Solutions

How to Format SQL Queries – Best Practices

SQL Testing – How to Test Data Based on SQL Queries

Related Topic