Tutorial :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.


Solution: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>  


Solution: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


Solution: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.


Solution: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


Solution: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/


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »