CODEWITHSUNDEEP
sqlsecuritycoldfusionsql-injection
Daniel A. White
Daniel A. White

Reputation: 190897

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.

Upvotes: 10

Views: 4010

Answers (5)

Mike Graham
Mike Graham

Reputation: 31

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.

Upvotes: 3

Jason
Jason

Reputation: 17956

  • 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

Upvotes: 6

Pyae Phyoe Shein
Pyae Phyoe Shein

Reputation: 13787

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/

Upvotes: 0

Katrina
Katrina

Reputation: 79

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

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

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>

Upvotes: 19

Related Questions