Update query not updating integer fields using CFQUERYPARAM

Question

I have a simple update query, only one table involved. I first wrote this without using CFQUERYPARAM and kept getting errors when the integer fields (zip,plus 4, etc) were null. So, I rewrote using CFQUERYPARAM so that the null values didn't produce the errors. Now, when I enter something into the integer fields, the data does not get saved.

What am I missing?

Thanks

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>

rrk rrk · Accepted Answer · 2020-04-07T15:56:47

First thing first. Please use cfqueryparam, to all user inputs when you use it in a query. The fields #form.firstname#, #form.lastname#, etc all should be in a cfqueryparam to prevent SQL Injection.

The issue you are facing here is the wrong use of NULL attribute of the cfqueryparam tag.

The null param should be an expression which results true or false. If you provide yes as the value directly, then the result becomes like this.

suffix = NULL

Now, let us see how to use null attribute.

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

The above will make sure NULL is passed as the column value if the form.suffix is blank. You can change this validation based on your application logic.

Also, newer versions (CF 11+) does not require the CF_SQL_ prefix in the type attribute.

So the final query should look something like this.

<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>

Update query not updating integer fields using CFQUERYPARAM

1 Answers