Geek asks and answers

Dynamic SQL in CFScript

I need to add conditions for 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 message: 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 provide security against SQL injection?

+3
source share
3 answers

Why not install SQL and add a parameter if necessary in a conditional expression 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();
+2
source

I like to use 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();
+6
source

addParam cfqueryparam CFML. , , value "1" "foobar", SQL. platformID arguments.platformid . : WHERE.

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 has some good info: http://www.bennadel.com/blog/1678-Learning-ColdFusion-9-Using-CFQuery-And-Other-Service-Tags-In-CFScript.htm

+3
source

More articles:


All Articles