How do I prevent SQL injection with ColdFusion

Question

How do I prevent SQL injection when it comes to ColdFusion? I'm quite new to the language/framework.

Here is my example query.

<cfquery name="rsRecord" datasource="DataSource">
    SELECT * FROM Table
    WHERE id = #url.id#
</cfquery>

I see passing in url.id as a risk.

Answer 1

Use a <cfqueryparam> tag for your id:
http://www.adobe.com/livedocs/coldfusion/6.1/htmldocs/tags-b20.htm

<cfquery name="rsRecord" datasource="DataSource">
    SELECT * FROM Table
    WHERE id = 
     <cfqueryparam value = "#url.id#"
        CFSQLType = "CF_SQL_INTEGER">
</cfquery>

Answer 2

• use a parameterized stored procedure
• cfqueryparam
• error handling around individual query
• error handling for site via <cferror>
• logic that limits the number of request that come from a specific IP in a given time
• ensure the database user account only has access to the specific actions it should

Answer 3

In addition to cfqueryparam you can use cfparam at the top of the page containing the SQL for each variable passed to it. This helps documentation also.

e.g.

<cfparam name="url.id" type="integer">

or more advanced:

<cfparam name="url.id" type="regex" pattern="\d" default="">

Since regular expression pattern are permitted, these can be extremely powerful:

<cfparam name="form.place" type="regex" pattern="[A-Z0-9]{1,6}|" default=""> 
       <!--- Upper case Alpa or Numeric, 1-6 characters or empty string --->

Also make sure you use a cferror in your application.cfm or application.cfc to prevent exposing your query table and column names.