CODEWITHSUNDEEP
coldfusioncoldfusion-10
Anonymous
Anonymous

Reputation: 89

Updating date with cfquery in coldfusion

Can somebody please suggest what am I doing wrong in the query below:

Data type of imageUpdateDate column is date. I also tried with datetime with no luck.

Any help would be greatly appreciated.

Upvotes: 0

Views: 494

Answers (1)

SOS
SOS

Reputation: 6550

imageUpdateDate ='#now()#'

The query is failing because of the single quotes around #now()#.

The reason is that the string representation of #now()# already contains single quotes:

{ts '2022-03-31 22:35:14'}

By wrapping that value in single quotes, you're creating an invalid sql statement:

  UPDATE  used_listings_v2 
  SET     [image] = 'new_file_name.ext' 
          , imageUpdateDate ='{ts '2022-03-31 22:35:14'}' 
  WHERE   id = '1'

Which when executed, fails with an error:

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '2022'.

Although removing the single quotes, or using getDate() instead, would resolve the error, the fact that single quotes can so easily break the query means that anyone attempting to attack your database with malicious sql can do the exact same thing!. That's dangerous for any application. Especially one running on the EOL'd CF10, which no longer receives security updates. Consider upgrading or switching to Lucee.

Anyway, the correct way to resolve the error is by using cfqueryparam on all of the query variables. Not only does it eliminate the need for using single quotes around parameter values, it also helps protect your database against sql injection. (I don't know your column data types, so modify the cfsqltypes as needed)

<cfquery datasource="...">
  UPDATE  used_listings_v2 
  SET     [image] = <cfqueryparam value="#dbFilename#" cfsqltype="cf_sql_varchar"> 
          , imageUpdateDate = <cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp">
  WHERE   id = <cfqueryparam value="#edit#" cfsqltype="cf_sql_integer">
</cfquery>

*Note: It's a good practice to always scope your variables. For example, use FORM.dbFileName instead of just dbFileNameity updates.

Upvotes: 1

Related Questions