Tackling Null Values while Inserting data in data base

Question

I have the following cfquery:

<cfquery name="CQuery" datasource="XX.X.X.XXX">
        INSERT INTO DatabaseName 
            (PhoneNumber_vch,
             Company_vch,
             date_dt)

         VALUES(#PhoneNumber#,
            #Company#,
            #Date# )

    </cfquery>

There are null values in case Company name doesnt' exist and I believe becasue of that Iam getting the following error:

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ','.

The comma , pointed in the error message is after #Company# field. Please let me know is it's because of null values and best approach to fix it?

The values in the PhoneNumber, company and Date are extracted from a XML SOAP response with proper usage of trim function as discussed in my previous post.

Using cfif in coldfusion

Thanks

Switching to `cfqueryparam` is definitely the way to go. The cfsqltypes vary depending on the data types of your columns. For dates, use `cf_sql_timestamp` when dealing with a date **and** time, or `cf_sql_date` when dealing with a date *only*. — Leigh, Oct 30 '13 at 20:29

score 15 · Accepted Answer · edited Oct 30 '13 at 20:24

15

If you use CFQueryParam like you should on any database SQL that accepts dynamic parameters you can kill two birds with one stone. First and most important, prevent SQL Injection Attacks and second you can use the attribute of the null="" to insert a NULL value into your record.

 <cfquery name="CQuery" datasource="XX.X.X.XXX">
      INSERT INTO DatabaseName (PhoneNumber_vch, Company_vch, date_dt)
      VALUES(
           <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(PhoneNumber)#" null="#NOT len(trim(PhoneNumber))#" />
           ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(Company)#" null="#NOT len(trim(Company))#" />
           ,<cfqueryparam cfsqltype="cf_sql_timestamp" value="#Date#" null="#NOT len(trim(Date))#" />
     )
</cfquery>

edited Oct 30 '13 at 20:24

Leigh

28,765
10
55
103

answered Oct 30 '13 at 20:21

BDavis

196
2

2

@Jack - Keep in mind a `null` is different than an empty string "". Using `cfqueryparam` you could simply insert the empty string instead of a `null`. (At least for the `varchar` columns). It all depends on your goal, and whether the columns allow nulls. – Leigh Oct 30 '13 at 20:37
1

For the timestamp field, I'd change the null attribute to use isDate() on the date variable instead of len(). – Dan Bracuk Oct 30 '13 at 21:37

James A Mohler · Answer 2 · 2013-10-30T23:57:05.833

You will want to use <cfqueryparam> to take care of nulls (and injection attacks)

Try

<cfquery name="CQuery" datasource="XX.X.X.XXX">
    INSERT INTO DatabaseName 
        (PhoneNumber_vch,
         Company_vch,
         date_dt)

     VALUES(
        <cfqueryparam value = "#PhoneNumber#" cfsqltype = "CF_SQL_VARCHAR">,
        <cfqueryparam value = "#Company#" cfsqltype = "CF_SQL_VARCHAR" 
          null              = "#IIF(Company EQ "", 1, 0)#">,
        <cfqueryparam value = "#Date#" cfsqltype = "CF_SQL_TimeStamp"
          null              = "#IIF(Date EQ "", 1, 0)#" >
        )

</cfquery>

Also see: cfqueryparam

You probably want a null attribute on the date variable as well. — Dan Bracuk, Oct 30 '13 at 22:41

score 3 · Answer 3 · answered Oct 30 '13 at 20:19

You either need to qualify your varchar entries (surround all varchar entries with single quotes, or, better would be change them to cfqueryparams;

<cfquery name="CQuery" datasource="XX.X.X.XXX">
        INSERT INTO DatabaseName 
            (PhoneNumber_vch,
             Company_vch,
             date_dt)

         VALUES(<cfqueryparam value="#PhoneNumber#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#Company#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#Date#" cfsqltype="CF_SQL_TIMESTAMP"> )

    </cfquery>

Tackling Null Values while Inserting data in data base

3 Answers3