Update error using ## [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'WHERE'

Question

Here is my code:

    <cfdump var="#addEnt#" >

    <!-- ADD -->
    <cfquery name="add" datasource="testdatasource" dbtype="OLEDB">
        UPDATE tblrequests
        SET
        lastname='#ucase(form.lastname)#', 
        firstname='#ucase(form.firstname)#', 
        middlei='#ucase(form.middlei)#',
        title='#form.title#', 
        eod='#dateformat(form.eod,'m-d-yyyy')#',
        dutystation='#form.dutystation#', 
        requestsnetwork=<cfif parameterexists(form.requestsnetwork)>1<cfelse>0</cfif>, 
        affiliation='#form.affiliation#',
        commentssupvreq='#form.commentssupvreq#', 
        requestdelete=<cfif form.requestdelete IS NOT ''>'#dateformat(form.requestdelete,'m-d-yyyy')#',<cfelse>Null,</cfif>
        commentssupvdelete='#form.commentssupvdelete#',
        commentssupvedit='#form.commentssupvedit#',
        dateemailrequested=<cfif form.dateemailrequested IS NOT ''>'#dateformat(form.dateemailrequested,'m-d-yyyy')#',<cfelse>Null,</cfif>
        commentsit='#form.commentsit#',
        bgcomplete=<cfif form.bgcomplete IS NOT ''>'#dateformat(form.bgcomplete,'m-d-yyyy')#',<cfelse>Null,</cfif>
        dategroupscreated=<cfif form.dategroupscreated IS NOT ''>'#dateformat(form.dategroupscreated,'m-d-yyyy')#',<cfelse>Null,</cfif>
        WHERE recnumber = #addEnt#      
    </cfquery>

When I submit the form I get an error:

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'WHERE'.

My cfdump displays the correct addent number from sql, but using #addEnt# in the sql statement is not working. Other pages in my applications ## for SQL queries and they work fine.

You have a comma with your last SET statement. Also you should really look into using `cfqueryparam` to reduce your risk of SQL injection. Right now your query is very vulnerable to SQL injection attacks. — Matt Busche, Dec 28 '15 at 15:32
Without the query parameters, your query will crash when someone named O'Hara submits the form. — Dan Bracuk, Dec 28 '15 at 22:53

score 4 · Accepted Answer · answered Dec 28 '15 at 15:32

4

The last line in your set statements has a comma at the end, which is where the SQL will be complaining

answered Dec 28 '15 at 15:32

Jedihomer Townend

400
1
9

Had to delete both , Thanks! – Some Dude Dec 28 '15 at 15:39
3

My vote put your reputation over 100. You owe me a beer. – Dan Bracuk Dec 28 '15 at 22:50
@DanBracuk anytime you come to north west montana let me know. – Some Dude Sep 15 '16 at 14:19

Leigh · Answer 2 · 2015-12-29T03:45:36.370

(Too long for comments)

As suggested in the comments, there are several improvements you could make to the query: one of the biggest, being the addition of cfqueryparam. It provides several advantages, such as:

CFQueryparam, or bind variables, help improve performance by encouraging databases to reuse query execution plans, rather than generating a new one each time (which is a costly operation).
Bind variables also prevent the execution of client supplied values as SQL commands, which has the happy side effect of preventing common forms of sql injection.
CF's implementation of bind variables also provides an extra layer of validation, by type checking input values, before the query is ever executed. So when invalid parameters are detected, it saves a wasted trip to the database.

A few other tips for improving the query

Although it makes no syntactical difference, consider placing commas at the beginning of each line, rather than at the end. This makes it easier to spot extra or missing commas:
```
UPDATE SomeTable
SET    ColumnA   = 'xxxx'
       , ColumnB = 'yyyy'
       , ColumnC = 'zzzzz'
       , ColumnD = 'xxxx'
       , ColumnE = 'yyyy'
WHERE ....
```
It looks like your query is populating several datetime columns. When working with datetime columns, it best to use date objects, not strings. Date strings are ambiguous, and can be interpreted differently than you might expect, depending on the database or settings. To insert a date only, use <cfqueryparam cfsqltype="cf_sql_date" ...>, for both date and time use <cfqueryparam cfsqltype="cf_sql_timestamp" ...>. Obviously, always validate the date strings first.
Consider using cfqueryparam's null attribute. It can be quite handy when inserting null values conditionally. (See example below)
As an aside, ParameterExists was deprecated a while ago and replaced with IsDefined, or preferably StructKeyExists. In this case, another alternative to a CFIF is to declare a default with cfparam, so the form field in question always exists.

Putting it all together, your final query might look something like this. I guessed about the column data types, so adjust as needed.

UPDATE tblrequests
SET    lastname             = <cfqueryparam value="#ucase(form.lastname)#" cfsqltype="cf_sql_varchar">
      , firstname           = <cfqueryparam value="#ucase(form.firstname)#" cfsqltype="cf_sql_varchar">
      ,  middlei            = <cfqueryparam value="#ucase(form.middlei)#" cfsqltype="cf_sql_varchar">
      ,  title              = <cfqueryparam value="#form.title#" cfsqltype="cf_sql_varchar">
      ,  eod                = <cfqueryparam value="#form.eod#" cfsqltype="cf_sql_date">
      ,  dutystation        = <cfqueryparam value="#form.dutyStation#" cfsqltype="cf_sql_varchar">
      ,  requestsnetwork    = <cfqueryparam value="#form.requestsNetwork#" cfsqltype="cf_sql_bit">
      ,  affiliation        = <cfqueryparam value="#form.affiliation#" cfsqltype="cf_sql_varchar">
      ,  commentssupvreq    = <cfqueryparam value="#form.commentsSupvReq#" cfsqltype="cf_sql_varchar">
      ,  requestdelete      = <cfqueryparam value="#form.requestDelete#" cfsqltype="cf_sql_date" null="#not isDate(form.requestDelete)#">
      ,  commentssupvdelete = <cfqueryparam value="#form.commentssupvdelete#" cfsqltype="cf_sql_varchar">
      ,  commentssupvedit   = <cfqueryparam value="#form.commentssupvedit#" cfsqltype="cf_sql_varchar">
      ,  dateemailrequested = <cfqueryparam value="#form.dateEmailRequested#" cfsqltype="cf_sql_date" null="#not isDate(form.dateEmailRequested)#">
      ,  commentsit         = <cfqueryparam value="#form.commentsit#" cfsqltype="cf_sql_varchar">
      ,  bgcomplete         = <cfqueryparam value="#form.bgComplete#" cfsqltype="cf_sql_date" null="#not isDate(form.bgComplete)#">
      ,  dategroupscreated  = <cfqueryparam value="#form.dateGroupsCreated#" cfsqltype="cf_sql_date" null="#not isDate(form.dateGroupsCreated)#">
WHERE recnumber = <cfqueryparam value="#addEnt#" cfsqltype="cf_sql_integer">

Yes! This. All this. Very nicely written. – beloitdavisja Dec 29 '15 at 05:34 — beloitdavisja, Dec 29 '15 at 05:34

Update error using ## [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'WHERE'

2 Answers2