Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Tackling Null Values while Inserting data in data base

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

like image 504
Jack Avatar asked Oct 30 '13 19:10

Jack


3 Answers

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>
like image 55
BDavis Avatar answered Sep 23 '22 01:09

BDavis


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

like image 29
James A Mohler Avatar answered Sep 21 '22 01:09

James A Mohler


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>
like image 38
Marius Avatar answered Sep 23 '22 01:09

Marius