Is there some process inside the CF engine which is automatically converting an apostrophe into &pos; without our input? Is CFQUERYPARAM doing this? Is the Global Script Protection doing this? Does a CF Function do this? OR is the SQL Server doing this? Legacy CF11 code, O'Malley in the string becomes O'Malley in the table. Can't find where. Sad.

You may have some sort of interceptor or tool which is pre-emptively looping over your form or url scopes and attempting to "sanitize" data

Yep, that's what I thought. But I can't find any hints - in the CF part anyway. It's a function argument that has an apostrophe when I dump it at the top of the function, and then ' when it gets into the table.

I'd step through and see where it changes then

Nope, I dump it right before the cfquery insert. So the only suspect I have is the cfqueryparam

How are you viewing the data? Are you looking at it directly in the DB or via some select that outputs it?

dBeaver

I assume dbeaver is showing you what's really there. Do you have SSMS installed?

I should note that it happens before the INSERT, because this whole thing started with a truncate error

You can run a trace on your DB while performing the insert to see how the data is actually coming across

the string grew too long due to the &apos, so the SQL choked

Can you show the cfquery that performs the insert?

Lemme see...

Which column?

xml file gets parsed... I cfdump the node right before the insert, and it's got ' then the insert complains that "'" is too long

I also assume you removed all the

<

and

>

from that code?

UIDDesc

Can you dump

left(trim(xmlVendor.VENDOR_SITE_CODE.xmlText),20)

directly

That's the one. It dumps as ARIANA L. D'AGOSTINO

It's possible the error message is simply being encoded

When I shorten it to ARIANA L. D'AGO, then the DB successfully saves it as ARIANA L. D'AGO

are you using encodeForHtml somewhere before it, or have it within a cfoutput with the attribute encodefor="html" ?

Hey, could the DBA have put a trigger on this thing?

Are there any INSERT triggers on the DB table?

looks like 2016 for the attribute, so no

Don't see any html encoding here. This is pretty simple 10+ year old stuff I'm stuck with

yeah, just found that too, so shouldn't apply to CF11

The main takeaway is that CF is not "helping me" by doing this by some grand design

The two suggested steps left to explore are • run a trace on the DB to capture the exact text being passed in • look for triggers on the table

Yes, thanks. Similar jynx again. I'll go resurrect the DBA with some voodoo or see if they have a new one yet.

what if you write the field value to a text file before the DB insert, just to make sure its a ' before it gets to cfquery

This just in, I'm an idiot. As you were...

@alholden you are not going to tell us the mistake?

I was adding an element of suspense... It was only after obtaining an actual copy of the source XML from the 3rd party that I could look at it in plain text. And of course, as it's probably in the xml spec.. the value was indeed encoded (&pos;) all along at the source. I was just unable to see that by using any of my go-to tools, and assumed the parser was decoding it back again because that's the only version I saw displayed to me.

@alholden You can encode an apostrophe as

'

in the XML, but once parsed, it won't be there. The parsing will decode that back into a

'

myXML_encoded = "<root>O&apos;Reilly</root>";

The only way the actual

XMLText

of a node would still have the

'

is if the original source XML was double encoded like so:

myXML_double_encoded = "<root>O&apos;Reilly</root>";

This example shows the raw contexts of the parsed xml text in each case https://trycf.com/gist/b30908dff1d96d5295a44465c9b12b52/acf11?theme=monokai

The source from the 3rd party had this field encoded once - so example 1 I was going to haul out a library like Apache text to decode these things before any DB work, but then I realized this 1 problem field is a proper noun (making apostrophes the only likely candidate), and I realized I'm lazy. So this has pretty much fixed it so far.

<cfqueryparam _cfsqltype_="cf_sql_nvarchar" _value_="#*left*(*trim*( *replace*(xmlVendor.VENDOR_SITE_CODE.xmlText,"&apos;","'","all") ),20)#" />

(the target column is a nVarchar) The queryparam seems to handle the apostrophe as part of the INSERT for me.

Hmm, a single encoding should not be a problem

I thought that HTML based encoding was just all about %hex number%

For the same reason you don't have to manually decode things like

%25

in a query string. CF/the servlet automatically decode those for you when they parse it.

It's 2 encodings in 1! Now how much would you pay!!

But all of that is beside the point. The XML Parsing step is responsible for decoding all of that. By the time you receive the parsed XML, all that should be gone

I don't know why it wasn't gone. It was a single

'

when I opened it in Notepad, and later when it got inserted.

Only if you were dealing with the raw XML string would you need to deal with that

xmlVendor

in the snip above is the parsed object. It's a mystery.

I think you still have something funky going on if you ask me. The CF11 on tryCF doesn't exhibit the behavior you're seeing.

And in a weird way we've circled back to my OP

11.0.19+314546

is the latest patch level

Sorry, gotta run. Another client is getting an "IoError 2032" when trying to generate any of their PDFs via cfreport... now that's wierd.