Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 564
Daniel A. White Avatar asked Apr 07 '10 13:04

Daniel A. White


3 Answers

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>
like image 186
Joel Coehoorn Avatar answered Nov 08 '22 11:11

Joel Coehoorn


  • 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
like image 39
Jason Avatar answered Nov 08 '22 11:11

Jason


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.

like image 3
Mike Graham Avatar answered Nov 08 '22 11:11

Mike Graham