How do I prevent SQL injection with ColdFusion

问题:

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.

回答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>

回答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

回答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.

回答4:

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

回答5:

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/