asked on

Syntax error in UPDATE statement

when I try to submit the query below I am getting a syntax error. It shows the error is on the Where clause line.

FORM.SiteTranslationTestID is numeric

what am I doing wrong?

<cfquery name="MttestUpdate" datasource="#application.DS#" username="#application.dbuser#" password="#application.dbpass#">
UPDATE tblTranslationTestSite
SET	TranslationTestReference	=	'#form.JobAdReference#',
	TranslationTest	=		'#form.TranslationTest#',
	Instructions	=	'#form.TranslationTestInstructions#',
WHERE SiteTranslationTestID = #FORM.SiteTranslationTestID#
</cfquery>

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

aka:

<cfquery name="MttestUpdate" datasource="#application.DS#" username="#application.dbuser#" password="#application.dbpass#">
UPDATE tblTranslationTestSite
SET      TranslationTestReference      =      '#form.JobAdReference#'
  ,    TranslationTest      =            '#form.TranslationTest#'
  ,    Instructions      =      '#form.TranslationTestInstructions#'
WHERE SiteTranslationTestID = #FORM.SiteTranslationTestID#
</cfquery>

Open in new window

Shawn

ASKER

can't believe it. I've been staring at it for an hour!

thanks angelIII: :-D

_agx_

> WHERE SiteTranslationTestID = #FORM.SiteTranslationTestID#

You should also consider using cfqueryparam on all values to help avoid sql injection. For example:

WHERE SiteTranslationTestID = <cfqueryparam value="#FORM.SiteTranslationTestID#" cfsqltype="cf_sql_integer">

Shawn

ASKER

good point agx,
I'm not very familiar with sql injection but am aware I have to go through my whole site to close any possible leaks. Any advice on this or helpful links?...I could open another question for this one. Let me know.
thanks,
Shawn

_agx_

I would start by using a tool like the ones listed here to look for queries that are not using cfqueryparam. IIRC, they do not update the code but do show you vulnerable queries:
http://www.coldfusionjedi.com/index.cfm/2008/7/29/What-Folks-arent-using-cfqueryparam

Shawn

ASKER

thanks again. looks like a great place to get started. :)

_agx_

Welcome!