Solved

Syntax error in UPDATE statement.

Posted on 2007-03-26
8
359 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
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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 Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What is the cheapest way to learn Websphere MQ? 2 101
Set up notificataion or alert from a website 2 73
cookies analysis tools 2 100
Using cfstoredproc to return query data 2 30
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…

820 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