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
kenjpeteAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SidFishesCommented:
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">

0
gdemariaCommented:

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

0
kenjpeteAuthor Commented:
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
0
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

gdemariaCommented:

 I am not a fan of cfqueryparam, i read it can cause errors depending on datatypes and empty values, etc.

 I use the straight and simple approach...

 first = <cfif len(form.first)>'#form.first#'<cfelse>NULL</cfif>

and for numbers..

 age = <cfif len(form.age)>#form.age#<cfelse>NULL</cfif>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kenjpeteAuthor Commented:
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?
0
gdemariaCommented:

 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...
0
kenjpeteAuthor Commented:
Ok...thanks for the help!

Ken
0
SidFishesCommented:
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 http://www.experts-exchange.com/Database/MySQL/Q_22479194.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Applications

From novice to tech pro — start learning today.