Update查询不使用CFQUERYPARAM更新整型字段

Question

我有一个简单的update查询，只涉及一个表。我第一次在没有使用CFQUERYPARAM的情况下编写这段代码，并且在整数字段(zip、加4等)为空时不断收到错误。因此，我使用CFQUERYPARAM重写，这样空值就不会产生错误。现在，当我在整型字段中输入内容时，数据不会被保存。

我遗漏了什么？

谢谢

数据仓库

<cfquery name="updt_person" datasource="#application.datasource#">
  UPDATE tblperson 
  SET 
    firstname = '#form.firstname#', 
    lastname = '#form.lastname#', 
    address_line_1 = '#form.address_line_1#', 
    address_line_2 = '#form.address_line_2#', 
    city = '#form.city#', 
    stateid = #form.stateid#, 
    zip = <cfqueryparam value = "#form.zip#" cfsqltype = "CF_SQL_INTEGER" null = "yes">, 
    plus4 = <cfqueryparam value = "#form.plus4#" cfsqltype = "CF_SQL_INTEGER" null = "yes">, 
    area_code = <cfqueryparam value = "#form.area_code#" cfsqltype = "CF_SQL_INTEGER" null = "yes">, 
    prefix = <cfqueryparam value = "#form.prefix#" cfsqltype = "CF_SQL_INTEGER" null = "yes">, 
    suffix = <cfqueryparam value = "#form.suffix#" cfsqltype = "CF_SQL_INTEGER" null = "yes"> 
  WHERE personid = #get_personid.personid#
</cfquery>

Answer 1

首先要做的是。当您在查询中使用cfqueryparam时，请对所有用户输入使用它。字段SQL应该在cfqueryparam中，以防止#form.firstname#, #form.lastname#, etc注入。

您在这里面临的问题是对cfqueryparam标记的NULL属性的错误使用。

null参数应该是一个能产生true或false的表达式。如果直接提供yes作为值，则结果如下所示。

suffix = NULL

现在，让我们看看如何使用null属性。

<cfqueryparam
  value = "#form.suffix#"
  cfsqltype = "CF_SQL_INTEGER"
  null = "#len(trim(form.suffix)) EQ 0#"
> 

如果form.suffix为空，上面的代码将确保将NULL作为列值传递。您可以根据您的应用程序逻辑更改此验证。

此外，较新版本的(CF 11+)不需要在type属性中使用CF_SQL_前缀。

所以最终的查询应该是这样的。

<cfquery name="updt_person" datasource="#application.datasource#">
  UPDATE tblperson 
  SET 
    firstname = <cfqueryparam value = "#form.firstname#" cfsqltype = "VARCHAR">, 
    lastname = <cfqueryparam value = "#form.lastname#" cfsqltype = "VARCHAR">, 
    address_line_1 = <cfqueryparam value = "#form.address_line_1#" cfsqltype = "VARCHAR">, 
    address_line_2 = <cfqueryparam value = "#form.address_line_2#" cfsqltype = "VARCHAR">, 
    city = <cfqueryparam value = "#form.city#" cfsqltype = "VARCHAR">, 
    stateid = <cfqueryparam value = "#form.stateid#" cfsqltype = "VARCHAR">, 
    zip = <cfqueryparam value = "#form.zip#" cfsqltype = "INTEGER" null = "#len(trim(form.zip)) EQ 0#">, 
    plus4 = <cfqueryparam value = "#form.plus4#" cfsqltype = "INTEGER" null = "#len(trim(form.plus4)) EQ 0#">, 
    area_code = <cfqueryparam value = "#form.area_code#" cfsqltype = "INTEGER" null = "#len(trim(form.area_code)) EQ 0#">, 
    prefix = <cfqueryparam value = "#form.prefix#" cfsqltype = "INTEGER" null = "#len(trim(form.prefix)) EQ 0#">, 
    suffix = <cfqueryparam value = "#form.suffix#" cfsqltype = "INTEGER" null = "#len(trim(form.suffix)) EQ 0#"> 
  WHERE personid = <cfqueryparam value = "#get_personid.personid#" cfsqltype = "INTEGER">
</cfquery>