SQL UPDATE Query Not Working:

Setting up the variables to be used in the query:

<cfset fid=#URL.id#>
<cfset fuser="#URL.user#">
<cfset fname="#URL.name#">
<cfset fgnum=#URL.gnum#>

Code (markup):

This is my query:

<cfquery datasource="myDataSource" name="edit">
     update list
          set user=<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#fuser#" />,
               name=<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#fname#" />,
                    group = #fgnum#
                         where id = #fid#
</cfquery>

Code (markup):

This fails with:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

The query appears to be:
update list set user= (param 1) , name= (param 2) , group = 6 where id = 29

If I take out the last SET (group = #fgnum#) then it works flawlessly. If I ad it in, in any way shape or form, it errors. I 'want' to use <cfqueryparam cfsqltype="CF_SQL_INTEGER" value=#fgnum# />.

The DATA types match. The database I am using has GROUP set as an integer.

Anyone know why this is failing?

 

Geeze I hate undocumented reserved words. Once again, it would appear I was using one and 'group' is reserved by either Access or ColdFusion. I'm betting Access.

Sigh....

 

It is reserved as part of SQL used in "GROUP BY"

Just do either [GROUP] for MSSQL or `GROUP` in MySql. And what is the deal with all the crazy # signage?

Why not just:

<cfset fid = URL.id />
<cfset fuser = URL.user />
<cfset fname = URL.name />
<cfset fgnum = URL.gnum />

Code (markup):

Just another observation. I noticed that you are not cfqueryparaming all of your inputs. To demonstrate the danger of that, do you know what happens when someone does something like this?

your.cfm?user=1&name=joe&gnum=1&fid=1%20OR%201=1

or worse....

your.cfm?user=1&name=joe&gnum=1&fid=1;drop%20table%20list

*Always* add that layer of protection in your code! SQL injection attacks are no fun.