Web application security is growing to be an interest of mine, especially after going through a round of audits against the application that pays my salary. With those audits (almost) behind me, my intent is to write a series of articles detailing some of the common vulnerabilities found in web applications and how they can be resolved in ColdFusion.

First up, SQL injection.

What Is It?

OWASP defines SQL injection as:

A SQL injection attack consists of insertion or "e;injection"e; of a SQL query via the input data from the client to the application.

What Can Be Done About It?

The best protection against a SQL injection attack is to use parameterized queries. A parameterized query uses placeholders for values inserted into the query at runtime. To create a parameterized query in ColdFusion, use the tag, for queries, and the for stored procedure calls.

For example, the following query:

view plain print about
1SELECT
2 id
3 , first_name
4 , last_name
5FROM users
6WHERE id = #form.id#

should be rewritten as:

view plain print about
1SELECT
2 id
3 , first_name
4 , last_name
5FROM users
6WHERE id = <cfqueryparam value="#form.id#" cfsqltype="cf_sql_integer">

The cfsqltype attribute of the <cfqueryparam> tag binds the data to an integer data type, which further helps the security effort. If an attacker modified the value of form.id to a non-numeric value, the query will fail.

Added Benefits

In addition to the knowledge that the query is more secure, parameterized queries usually perform faster than unparameterized queries.