CFScript中的动态SQL

Question

我需要在我的SQL查询中添加if条件。我想到了这个解决方案，但它不起作用，我也不确定为什么。

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();

我遇到了一个错误：您的SQL语法有误；请检查...near''AND platforms.id = 1'''在第6行

有什么办法可以解决这个限制，同时确保免受SQL注入攻击的安全呢？

- Mohamad

Answer 1

我喜欢使用savecontent来完成这个操作：

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();

- Adam Tuttle

---

需要在三元条件周围加括号吗，例如(arguments.platformId)？ - user3071284

Answer 2

addParam是在CFML中使用cfqueryparam的等价物。因此，它期望value属性的值类似于'1'或'foobar'，而不是SQL代码片段。相反，只需将platformID的值设置为arguments.platformid或空字符串之一。然后直接在WHERE子句中引用:platform。

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();

这篇文章包含了一些有用的信息：http://www.bennadel.com/blog/1678-Learning-ColdFusion-9-Using-CFQuery-And-Other-Service-Tags-In-CFScript.htm

- duncan

---

如果我这样做，而且没有指定平台，就什么也不会返回...因此需要使用“where 0=0”。 - Mohamad

好的，既然如此，我会采纳Adam的建议。 - duncan

Answer 3

为什么不在代码中使用条件语句来设置SQL并添加必要的参数？

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();

- Ryan Lynch