Skip to main content
csgaraglino
csgaraglino
Known Participant
March 28, 2017
Answered

Double Apostrophe in SQL?

  • March 28, 2017
  • 3 replies
  • 800 views

I have a form fields that allows admins to update the headers & Footers of their site and for the most part it works awesome.

Where is falls apart is anytime there is a double apostrophe with no space.

Here is the stock Tag Manager:

<!-- Google Tag Manager -->

<script>(function(w,d,s,l,i){w=w||[];w.push({'gtm.start':

new Date().getTime(),event:'gtm.js'});var f=d.getElementsByTagName(s)[0],

j=d.createElement(s),dl=l!='dataLayer'?'&l='+l:'';j.async=true;j.src=

'https://www.googletagmanager.com/gtm.js?id='+i+dl;f.parentNode.insertBefore(j,f);

})(window,document,'script','dataLayer','GTM-WN42FJV');</script>

<!-- End Google Tag Manager -->

On this line:

j=d.createElement(s),dl=l!='dataLayer'?'&l='+l:'';j.async=true;j.src=

Google has:

+l:'';j.async

The 2 apostrophes, when saved in SQL come back as 1 - somewhere in the SQL process one of them is being stripped?

This only happens when there are 2 apostrophes in a row with nothing between them.

How do I fix this?

    This topic has been closed for replies.
    Correct answer Steve Sommers

    I would need to see the query tag/call that you are making. I'm guessing you're doing something like:

    <cfquery datasource="#application.ds#">

      update [sometable]

      set [somecolumn] = '#form.value#'

      where [someothercolumn] = 123

    </cfquery>

    If this is the case, changing you query to something like the following should do the trick:

    <cfquery datasource="#application.ds#">

      update [sometable]

      set [somecolumn] = <cfqueryparam value='#form.value#' cfsqltype='CF_SQL_VARCHAR' />

      where [someothercolumn] = <cfqueryparam value='123' cfsqltype='CF_SQL_INTEGER' />

    </cfquery>

    3 replies

    csgaraglino
    csgaraglinoAuthor
    Known Participant
    March 30, 2017

    Perfect!

    WolfShade
    WolfShade
    Legend
    March 28, 2017

    That's because SQL uses the apostrophe to delimit string values. 

    WHERE uname = 'john'

    If an apostrophe needs to be part of the value of the string, then it needs to be escaped.

    WHERE businessname = 'Jack''s Pizza'

    CF will, I believe, automatically escape the apostrophe before it gets to the database.

    HTH,

    ^_^

    S
    Steve SommersCorrect answer
    Legend
    March 28, 2017

    I would need to see the query tag/call that you are making. I'm guessing you're doing something like:

    <cfquery datasource="#application.ds#">

      update [sometable]

      set [somecolumn] = '#form.value#'

      where [someothercolumn] = 123

    </cfquery>

    If this is the case, changing you query to something like the following should do the trick:

    <cfquery datasource="#application.ds#">

      update [sometable]

      set [somecolumn] = <cfqueryparam value='#form.value#' cfsqltype='CF_SQL_VARCHAR' />

      where [someothercolumn] = <cfqueryparam value='123' cfsqltype='CF_SQL_INTEGER' />

    </cfquery>

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