Solved

Syntax error in UPDATE statement.

Posted on 2007-03-26
8
344 Views
Last Modified: 2010-04-06
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
0
Comment
Question by:kenjpete
  • 3
  • 3
  • 2
8 Comments
 
LVL 36

Expert Comment

by:SidFishes
Comment Utility
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
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility

 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
 

Author Comment

by:kenjpete
Comment Utility
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
 
LVL 39

Accepted Solution

by:
gdemaria earned 125 total points
Comment Utility

 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:kenjpete
Comment Utility
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
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility

 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
 

Author Comment

by:kenjpete
Comment Utility
Ok...thanks for the help!

Ken
0
 
LVL 36

Expert Comment

by:SidFishes
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

New Relic: Our company recently started researching several products to figure out what were the best ways for us to increase our web page speed and to quickly identify performance problems that we may be having. One of the products we evaluated wa…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now