更新查询未使用CFQUERYPARAM更新整数字段

Question

我有一个简单的更新查询，只涉及一个表。我第一次写这篇文章时没有使用CFQUERYPARAM，并且在整数字段(zip、plus 4等(为null时不断出现错误。因此，我使用CFQUERYPARAM进行了重写，这样null值就不会产生错误。现在，当我在整数字段中输入一些内容时，数据不会被保存。

我错过了什么？

感谢

DW-

<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时，请将其用于所有用户输入。字段#form.firstname#, #form.lastname#, etc都应该在cfqueryparam中，以防止SQL注入。

您在这里面临的问题是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>