Double Apostrophe in SQL?

[csgaraglino]

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?

[Steve Sommers · Accepted solution]

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>

[WolfShade]

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,

^_^

[csgaraglino]

Perfect!