Skip to main content
K
kkcchan
Participant
March 30, 2020
Question

Delimit field name for cfinsert, cfupdate

  • March 30, 2020
  • 1 reply
  • 502 views

Hi there,

I have a table with a field name called currency.  Currency is a keyword and normally I have to delimit the field name in a query, eg update table set [currency] = 'USD'

 

When I use cfinsert with the currency field, I get query syntax error.  I wonder if I there is a way I can tell cfinsert to delimit the field?

 

Right now, I just left the currecy field out of cfinsert and run another update afterwards.

 

I am using CF18

 

Thanks

    This topic has been closed for replies.

    1 reply

    WolfShade
    WolfShade
    Legend
    March 30, 2020

    Hi, kkcchan,

     

    A perfect example of why to not use keywords as column names, function names, etc.  {shrug}

     

    I've heard both sides of the arguments concerning cfinsert and cfupdate security.  Personally, I do not use them.  Your most likely workaround for this is to create the query manually and not use cfinsert/cfupdate.

     

    In the future, when designing a database, it's best to avoid keywords and either shorten them or somehow abbreviate them.  So, "Currency" could be "crncy", or some such.  Even with delimiting them, it's not what I would consider "best practice."

     

    V/r,

     

    ^ _ ^

      D
      Community Expert
      Dave WattsCommunity Expert
      Community Expert
      March 30, 2020

      I'm going to second Wolfshade's recommendation not to use keywords. This is always going to make things more confusing. Instead of shortening them, though, you might want to make them longer! For example, when you say "currency", you could make it "currency_used" or something that better describes the field's purpose.

       

      I would also recommend that you take this opportunity to ditch CFINSERT and CFUPDATE. These might seem to save you a little bit of time, but you're better off just writing the SQL yourself. You've discovered one of the many reasons for that.

       

      Dave Watts, Eidolon LLC

      Dave Watts, Eidolon LLC
        Terms & ConditionsAccessibility statement
        Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices