CODEWITHSUNDEEP
sqlcoldfusionsql-injection
Mohamad
Mohamad

Reputation: 35359

Dynamic SQL in CFScript

I need to add if conditions to my SQL query. I came up with this solution, but it does not work, and I'm not sure why.

local.platformId = arguments.platformId ? "AND platforms.id = #arguments.platformId#" : "";

local.pages = new Query(dataSource=variables.wheels.class.connection.datasource);
local.pages.setSQL
("
    SELECT          COUNT(games.id) AS totalRecords
    FROM            games
    INNER JOIN      platforms ON games.platformId = platforms.id 
    WHERE           0=0
:platform
");

local.pages.addParam(name="platform", cfsqltype="CF_SQL_VARCHAR", value=local.platformId);      
local.pages = local.pages.execute().getResult();

I get an error of: You have an error in your SQL syntax; check ... near ''AND platforms.id = 1' ''' at line 6

Any idea how to get around this limitation and still ensure safety from SQL injection?

Upvotes: 3

Views: 2449

Answers (3)

Ryan Lynch
Ryan Lynch

Reputation: 7786

Why not set the SQL and add the param if neccessary in a conditional statement in your code?

local.pages = new Query(dataSource=variables.wheels.class.connection.datasource);
local.baseSQL = "
    SELECT          COUNT(games.id) AS totalRecords
    FROM            games
    INNER JOIN      platforms ON games.platformId = platforms.id 
    WHERE           platforms.id = :platform
";

if(StructKeyExists(arguments, "platformId")
{
  local.baseSQL &= "AND platforms.id = :platformId";
  local.pages.setSQL(baseSQL);
  local.pages.addParam(
    name="platformId", 
    cfsqltype="CF_SQL_VARCHAR", 
    value=arguments.platformId);
}
else
  local.pages.setSQL(baseSQL)
local.pages = local.pages.execute().getResult();

Upvotes: 2

Adam Tuttle
Adam Tuttle

Reputation: 19834

I like using savecontent for this:

savecontent variable="local.sql"{
    WriteOutput("
        SELECT          COUNT(games.id) AS totalRecords
        FROM            games
        INNER JOIN      platforms ON games.platformId = platforms.id 
        WHERE           0=0
    ");
    (arguments.platformId)
      ? WriteOutput("AND platforms.id = :platform")
      : WriteOutput("");
}

local.pages = new Query(dataSource=variables.wheels.class.connection.datasource);
local.pages.setSQL(local.sql);
if (arguments.platformId){
    local.pages.addParam(name="platform", cfsqltype="CF_SQL_VARCHAR", value=arguments.platformId);
}
local.pages = local.pages.execute().getResult();

Upvotes: 6

duncan
duncan

Reputation: 31920

addParam is the equivalent of using cfqueryparam in CFML. So it's expecting the value attribute to be like '1' or 'foobar', not a bit of SQL. Instead just set the value of platformID to either be the arguments.platformid or an empty string. Then refer directly to :platform in your WHERE clause.

local.platformId = arguments.platformId ? arguments.platformId : "";

local.pages = new Query(dataSource=variables.wheels.class.connection.datasource);
local.pages.setSQL
("
    SELECT          COUNT(games.id) AS totalRecords
    FROM            games
    INNER JOIN      platforms ON games.platformId = platforms.id 
    WHERE           platforms.id = :platform
");

local.pages.addParam(name="platform", cfsqltype="CF_SQL_VARCHAR", value=local.platformId);      
local.pages = local.pages.execute().getResult();

This article here has some good info: http://www.bennadel.com/blog/1678-Learning-ColdFusion-9-Using-CFQuery-And-Other-Service-Tags-In-CFScript.htm

Upvotes: 3

Related Questions