sql-server - 使用 if 语句构建动态 cfquery - ColdFusion

Question

我正在寻找基于从 coldfusion 中的表单提交的值创建查询的最佳方法。

我打算使用 <cfif> 创建它语句，但开始遇到在 AND 语句后添加逗号但不添加逗号的问题，如果它是查询中的最后一个 AND。以及确保查询中包含第一个 WHERE。

看我下面的例子:

<cfquery name="reportInfo" datasource="DataSource1">
SELECT  *
FROM    Basic_Info

<cfif company_name NEQ "false">
WHERE   Sold_to_Party = '#company_name#',
<cfelse>
WHERE   Sold_to_Party != '',
</cfif>

<cfif user_type EQ "both">

<cfelseif user_type EQ "rittal">

AND   Sold_to_Party != 'Distributor Submission',

<cfelseif user_type EQ "distro">

AND   Sold_to_Party = 'Distributor Submission',

</cfif>

<cfif status NEQ "false">

AND   Status = '#status#'

<cfelse>

AND   Status != ''

</cfif>


ORDER BY ID
</cfquery>

您可以看到我在其中添加了 elseif 语句，这样我就可以添加不带逗号的相同语句。

Answer 1

WHERE 子句中不需要逗号 - 它不是有效的 SQL。

在构建动态 SQL WHERE 子句时，一个快速提示是只使用一个“虚拟”where 子句。这是一个简单的示例(我没有包含您的所有子句):

<cfquery name="reportInfo" datasource="DataSource1">
    SELECT  *
    FROM    Basic_Info
    WHERE 1 = 1

    <cfif company_name NEQ false>
       AND Sold_to_Party = <cfqueryparam value="#company_name#" cfsqltype="CF_SQL_VARCHAR">
    <cfelse>
        AND Sold_to_Party != <cfqueryparam value="" cfsqltype="CF_SQL_VARCHAR">
    </cfif>
    <cfif form.user_type EQ "rittal">
        AND Sold_to_Party != <cfqueryparam value="Distributor Submission" cfsqltype="CF_SQL_VARCHAR">
    <cfelseif form.user_type EQ "distro">
        AND Sold_to_Party = <cfqueryparam value="Distributor Submission" cfsqltype="CF_SQL_VARCHAR">
    </cfif>
    <cfif status NEQ "false">
        AND Status = <cfqueryparam value="#status#" cfsqltype="CF_SQL_BIT">
    </cfif>
    ORDER BY ID
 </cfquery>

还有一点需要注意的是 SQL 注入(inject)攻击。简而言之，在传递值时始终使用 cfqueryparam。 cfqueryparam 保护您免受 SQL 注入(inject)攻击，并允许数据库引擎在您将不同的值传递给参数时创建和重用执行计划。

如果您不确定要使用哪个 cfsqltype 值，那么这里有一个方便的列表(对于 MySQL): http://cfsearching.blogspot.co.uk/2010/01/cfqueryparam-matrix-for-mysql-5.html