ColdFusion SQL Security

It’s common when working on a web application to interact with a database to read, insert, update, or delete data. In doing so you must take care when using variables in your SQL. The input may be coming from a search form or passed in the URL, but wherever it comes from there is a risk of SQL Injection, Cross Site Scripting or other attacks on your system.

ColdFusion provides a few helpful tools for preventing people from executing malicious SQL queries or executing JavaScript injected into your database. One is the cfqueryparam tag. Assuming we set first_name = “Kevin” This would look something like this

SELECT u.first_name, u.last_name
FROM users u
WHERE u.first_name = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#first_name#">

This will do a couple of things. First it will throw an error if the value of the first_name variable is not a string. Most any input would qualify as a string here so that seems unhelpful, but the second thing it does is make sure that no matter what you pass in as the first_name it will not be treated as a SQL command.

I still sometimes see tutorials that skip things like this, but really there is no reason why you should be writing queries that are not parameterized. This is not unique to ColdFusion either; the syntax varies by language, but you shouldn’t be passing variables into a query that aren’t parameterized.

Kevin Hall
Latest posts by Kevin Hall (see all)