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

Another option is to use stored procedures (if you database supports them).

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_r-s_22.html

Answer 4

Using cfqueryparam is for preventing SQL injection is good. But, you can't use cachewithin in cfquery tag if you want to use cfqueryparam. My another advice is do just like that

Put this condition at the top of your page.

<CFIF IsDefined("id") AND NOT IsNumeric(id)> <cfabort showerror="Invalid Query String"> </CFIF>

In your query tag, use just like this:

WHERE ID = #Val(id)#

See also, how to prevent: http://ppshein.wordpress.com/2008/08/28/block-ip-in-coldfusion/