Solved

Syntax error in UPDATE statement.

Posted on 2007-03-26
8
362 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 36

Expert Comment

by:SidFishes
ID: 18795958
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
ID: 18800429

 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
ID: 18800644
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 39

Accepted Solution

by:
gdemaria earned 125 total points
ID: 18801416

 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
 

Author Comment

by:kenjpete
ID: 18810947
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
ID: 18811358

 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
ID: 18811437
Ok...thanks for the help!

Ken
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 18812936
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction HyperText Transfer Protocol (http://www.ietf.org/rfc/rfc2616.txt) or "HTTP" is the underpinning of internet communication.  As a teacher of web development I have heard many questions, mostly from my younger students who have come to t…
Using Quotation Marks in PHP This question (http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28217211.html) seems to come up a lot for developers who are new to PHP.  And it got me thinking, "How can we explain the rule…
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…
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.

739 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