Double Apostrophe in SQL?

Contributor ,
Mar 28, 2017 Mar 28, 2017

Copy link to clipboard

Copied

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?

Views

426

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

Advocate , Mar 28, 2017 Mar 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] = ...

Likes

Translate

Translate
Advocate ,
Mar 28, 2017 Mar 28, 2017

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Mar 28, 2017 Mar 28, 2017

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Mar 30, 2017 Mar 30, 2017

Copy link to clipboard

Copied

LATEST

Perfect!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines