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

我收到以下错误: You have an error in your SQL syntax; check ... near ''AND platforms.id = 1' ''' at line 6
知道如何绕过这个限制并仍然确保 SQL 注入(inject)的安全性吗？

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