Link to home
Start Free TrialLog in
Avatar of kenjpete
kenjpete

asked on

Syntax error in UPDATE statement.

I am using a developer's version of CF7.0.2 on WinXP, and I am getting a syntax error everytime I try to run a SQL update on my Profile.cfm page. I have looked over every line of this code and cannot see where the error is? Can anyone lead me in the right direction? Here is the specific error message:
*****************************
 Error Executing Database Query.
Syntax error in UPDATE statement.
 
The error occurred in C:\CTE\profile.cfm: line 114
Called from C:\CTE\profile.cfm: line 8
Called from C:\CTE\profile.cfm: line 1
Called from C:\CTE\profile.cfm: line 114
Called from C:\CTE\profile.cfm: line 8
Called from C:\CTE\profile.cfm: line 1

112 :             </cfif>
113 :       WHERE StudentID=
114 :             <cfqueryparam value=#FORM.StudentID# cfsqltype="cf_sql_numeric">
115 :               </cfquery>
116 :               <cflocation url="update_thanks.cfm">
************************************

Here is the code. Lines 112 thru 116 are at the bottom.

<!--- Profile Query --->
<cfquery name="rs_profile" datasource="ctesurvey_dsn">
SELECT *
FROM data
WHERE Username = <cfqueryparam value="#Session.Auth.Username#" cfsqltype="cf_sql_char" maxlength="50">
</cfquery>

<!--- Update Query --->
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ "form1">
  <cfquery datasource="ctesurvey_dsn">
    UPDATE data
     SET First=
            <cfif IsDefined("FORM.First") AND #FORM.First# NEQ "">
              <cfqueryparam value="#FORM.First#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
     , Last=
            <cfif IsDefined("FORM.Last") AND #FORM.Last# NEQ "">
              <cfqueryparam value="#FORM.Last#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
     , Street=
            <cfif IsDefined("FORM.Street") AND #FORM.Street# NEQ "">
              <cfqueryparam value="#FORM.Street#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
     , City=
            <cfif IsDefined("FORM.City") AND #FORM.City# NEQ "">
              <cfqueryparam value="#FORM.City#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
     , State=
            <cfif IsDefined("FORM.State") AND #FORM.State# NEQ "">
              <cfqueryparam value="#FORM.State#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
     , Zip=
            <cfif IsDefined("FORM.Zip") AND #FORM.Zip# NEQ "">
              <cfqueryparam value="#FORM.Zip#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
    , Gender=
            <cfif IsDefined("FORM.Gender") AND #FORM.Gender# NEQ "">
              <cfqueryparam value="#FORM.Gender#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
    , Year=
            <cfif IsDefined("FORM.Year") AND #FORM.Year# NEQ "">
              <cfqueryparam value="#FORM.Year#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
      , School=
            <cfif IsDefined("FORM.School") AND #FORM.School# NEQ "">
              <cfqueryparam value="#FORM.School#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
      , Program=
            <cfif IsDefined("FORM.Program") AND #FORM.Program# NEQ "">
              <cfqueryparam value="#FORM.Program#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
      , Teacher=
            <cfif IsDefined("FORM.Teacher") AND #FORM.Teacher# NEQ "">
              <cfqueryparam value="#FORM.Teacher#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
      , Email=
            <cfif IsDefined("FORM.Email") AND #FORM.Email# NEQ "">
              <cfqueryparam value="#FORM.Email#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
      , Phone=
            <cfif IsDefined("FORM.Phone") AND #FORM.Phone# NEQ "">
              <cfqueryparam value="#FORM.Phone#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
      , CellPhone=
            <cfif IsDefined("FORM.CellPhone") AND #FORM.CellPhone# NEQ "">
              <cfqueryparam value="#FORM.CellPhone#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
      , Preference=
            <cfif IsDefined("FORM.Preference") AND #FORM.Preference# NEQ "">
              <cfqueryparam value="#FORM.Preference#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
      , Username=
            <cfif IsDefined("FORM.Username") AND #FORM.Username# NEQ "">
              <cfqueryparam value="#FORM.Username#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
      , Pcode=
            <cfif IsDefined("FORM.Pcode") AND #FORM.Pcode# NEQ "">
              <cfqueryparam value="#FORM.Pcode#" cfsqltype="cf_sql_varchar" maxlength="50">
              <cfelse>
              ''
            </cfif>
      WHERE StudentID=
            <cfqueryparam value=#FORM.StudentID# cfsqltype="cf_sql_numeric">
 </cfquery>
  <cflocation url="update_thanks.cfm">
</cfif>
**********************************
thanks,
   Ken
Avatar of SidFishes
SidFishes
Flag of Canada image

I believe

cfqueryparam attributes must all be in quotes

 <cfqueryparam value=#FORM.StudentID# cfsqltype="cf_sql_numeric">

sb

 <cfqueryparam value="#FORM.StudentID#" cfsqltype="cf_sql_numeric">


 A couple unsolicited comments on the insert, if you're using SQL Server, there is a distinct difference between inserting '' as a value and inserting a NULL into a column.   If you later fetch where FIRST is NULL it will NOT match a '' value in the field.  Then you have to wrap it in this nasty little isNull() function that will kill your index.

 So, I try to stick with always using NULL instead of blank.

       <cfif IsDefined("FORM.First") AND #FORM.First# NEQ "">
              <cfqueryparam value="#FORM.First#" cfsqltype="cf_sql_varchar" maxlength="50">
       <cfelse>
              NULL
       </cfif>

But if you want to shorten your code a bit, you can use the NULL parameter of cfqueryparam, this will do it for you.

 First = <cfqueryparam value="#form.first#" NULL="#NOT len(form.first)#" maxlength="50" cfsqltype="cf_sql_varchar">
,Last = <cfqueryparam value="#form.last#" NULL="#NOT len(form.last)#" maxlength="50" cfsqltype="cf_sql_varchar">

Avatar of kenjpete
kenjpete

ASKER

Originally I had quotes around the cfqueryparam attribute, and then I thought that could be the problem since that attribute is numeric (StudentID). I've tried it with and without the quotes and it still throws the same error. I will see if adding the NULL="#NOT len(form.first)#" statement will work.


Ken
ASKER CERTIFIED SOLUTION
Avatar of gdemaria
gdemaria
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Removing the cfqueryparam did the trick. Thanks to all for your input. The only concern I have at this point is I was under the impression that using cfqueryparam helps prevent SQL injections? If thats true then this code is more at risk?

 The difficult part of cfqueryparam is that it creates bind variables of the data type you specify and then ensures that the value you pass in is of that datatype, if its not, it throws an error.   It doesn't like NULLs and you have to tell it if you are passing a null value in order to null the column.    So that's probably why we're having trouble here.

 We can go a long way on your own to prevent injections.  If its a numer value, simply wrap it in val( ), that will do it.   val will drop any non-numeric characters from the value.

 placing a string in single quotes will help that because the injection will simply get saved into the database (if it fits).. like this..

 for first name the user enters   [ John; delete from users; ]

 IN your sql, it will say...

 update users
   set first = 'john; delete from users;'
 etc...

 so, its not so hard to prevent injections...
Ok...thanks for the help!

Ken
fwiw...

http://www.adobe.com/devnet/coldfusion/articles/cfqueryparam.html

and

"Macromedia recommends that you use the cfqueryparam tag within every cfquery tag, to help secure your databases from unauthorized users. For more information, see:

    * Security Bulletin ASB99-04, "Multiple SQL Statements in Dynamic Queries," at
      www.macromedia.com/devnet/security/security_zone/asb99-04.html.
    * Accessing and Retrieving Data in Developing ColdFusion MX Applications
"

gd...please see https://www.experts-exchange.com/questions/22479194/sql-injection-quotes-and-cfqueryparam.html