mysql - ColdFusion 参数化查询

Question

我有一个查询，我运行它来填充我试图参数化的 CFChart:

<cfquery name="total" datasource="#datasource#">
    SELECT *
    FROM   closed_tickets
    WHERE  MONTH(closed_date) = #month# 
    AND    YEAR(closed_date) = #dateFormat(theMonth,"yyyy")# 
    AND    technician_id = #techID#
 </cfquery>

这是我尝试过的:

<!---Open tickets from chosen year where technician is active --->
<cfquery name="total" datasource="#datasource#">          
    SELECT *
    FROM   closed_tickets
    WHERE  MONTH(closed_date) = <CFQUERYPARAM Value="#month#"> 
    AND    YEAR(closed_date) = #dateFormat(theMonth,"yyyy")#" cfsqltype="CF_SQL_TIMESTAMP"> 
    AND    technician_id = <CFQUERYPARAM Value="#techID#">
</cfquery>

当我将查询更改为此时，它以某种方式破坏了我的 CFChart。我没有在屏幕上看到任何 CFErrors，但我的 CFChart 是空白的。

我已将其缩小到与我的查询相关:

#dateFormat(theMonth,"yyyy")#" cfsqltype="CF_SQL_TIMESTAMP"

当我删除查询的这个参数化部分并放入

#dateFormat(theMonth,"yyyy")#

有效。

任何人都可以阐明这一点吗？

Answer 1

I don't get any CFErrors on the screen but my CFChart is blank.

暂时忽略 correct approach，发生这种情况的原因是您使用了不正确的 cfsqltype 作为参数。因此，您实际上向数据库发送了与您想象的不同的值(并因此执行了不同的比较)。因此，查询无法找到任何匹配的记录。这就是为什么您的图表是空白的。

通过使用 cf_sql_timestamp，您可以将“值”转换为完整的日期/时间对象。然而，YEAR() 只返回一个四位数。所以你在比较苹果和橘子。从概念上讲，您的查询实际上是这样做的:

  WHERE  2014 = {ts '2009-02-13 23:31:30'}

它不会抛出错误的原因是日期/时间值在内部存储为数字。所以你实际上是在比较一个小数字(即年份)和一个非常大的数字(即日期/时间)。显然日期值会大得多，所以它几乎永远不会与年份数字匹配。同样，概念上您的查询是这样做的:

 WHERE 2014 = 1234567890

由于 cfsqltype 是可选的，很多人认为它不是很重要 - 但它确实很重要。

• 验证:除了其他好处外，cfqueryparam 根据cfsqltype(日期、日期和时间、数字、等等)。这发生在 将 sql 发送到数据库之前。因此，如果输入无效，您不会浪费一次数据库调用。如果您省略 cfsqltype，或者只使用默认的 ie 字符串，那么您将失去额外的验证。

• 准确性 选择正确的 cfsqltype 可确保您将正确的值发送到数据库。如上所示，使用错误的类型会导致 CF 向数据库发送错误的值。

  cfsqltype 还确保以明确的格式将值提交给数据库，数据库将按照您期望的方式进行解释。从技术上讲，您可以将所有内容发送到数据库一个字符串。但是，这会强制数据库执行 implicit conversion(通常是不希望的)。

  通过隐式转换，字符串的解释完全由数据库决定——它可能并不总能给出您期望的答案。将日期作为字符串而不是日期对象提交就是一个典型的例子。当前数据库将如何解释像“05/04/2014”这样的日期字符串？ 4 月 5 日还是 5 月 4 日？这取决于。更改数据库或数据库设置，结果可能完全不同。

确保一致结果的唯一方法是指定适当的 cfsqltype。它应该匹配比较列/函数的数据类型，或者至少是等效类型。对于 YEAR()，它返回一个四位数。所以你应该使用 cf_sql_integer，作为 Adrian mentioned the comments 。这同样适用于您的 MONTH() 比较。

 WHERE Year(ColumnName) = <cfqueryparam value="2014" cfsqltye="CF_SQL_INTEGER">
 AND   Month(ColumnName) = <cfqueryparam value="11" cfsqltye="CF_SQL_INTEGER"> 

说了这么多，Dan's suggestion 是执行日期比较的更好方法。 That paradigm 对索引更友好，并且无论您的目标列是否包含日期(仅)或日期和时间都有效。请注意在他的示例中使用了 cf_sql_date。

• cf_sql_timestamp - 发送日期和时间
• cf_sql_date - 仅发送日期。时间值被截断