Yea this is a really wierd bug that I have encountered also, basically what
I see happening is.. when you supply your variable to the UDF - inside a
cfquery block CF escapes quotes in the variable before handing to the UDF,
and then it takes the value your UDF returns and escapes the quotes again.
So your one quote becomes '' when going into the UDF and then '''' coming
out of the UDF :-o
Two ways to fix it, what I did was unescape the single quotes in the value
my UDF gets, so I have something like
function myUDF(someValue) {
someValue = Replace(someValue, "''", "'", "ALL");
... do stuff ...
return someValue;
}
or I think you could also use preserve single quotes when passing the value
to the UDF, something like...
<CFQUERY ...>
SOME SQL STUFF
#myUDF(preserveSingleQuotes(yourVariable))#
</CFQUERY>
which should stop the escapement of single quotes when passing yourVariable
into myUDF, the single quotes will still be escaped coming out, but that's
what you'd expect, if you didn't want that either you could do
#preserveSingleQuotes(myUDF(preserveSingleQuotes(yourVariable)))#
Whatever way you look at it it's a very wierd thing, it's one thing to
escape values being spit out to the database, it's another to escape values
when feeding them to a UDF.
----- Original Message -----
From: "Sean Daniels" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, March 22, 2002 5:06 AM
Subject: CFSCRIPT & Preservesinglequotes()
> I have written a seemingly simple UDF for cleaning up data before an
insert
> or update statement (it trims the string, inserts null when necessary,
wraps
> string in single quotes if it's a char field):
>
>
> <cfscript>
> function checknull(ii,isint) {
> if (isint) {
> return iif(len(trim(ii)),de(trim(ii)),de("null")); }
> else {
> return
> iif(len(trim(ii)),de(chr(39)&trim(ii)&chr(39)),de("null")); }
> }
> </cfscript>
>
> Basically, my insert statement looks like:
>
> Insert into tablename (intfield,charfield)
> Values (#checknull(intfield,1)#,#checknull(charfield,0)#)
>
> It works great except for when the charfield contains apostrophes. It
> escapes them all and the result is that twice as many apostrophes get
> inserted. I know that somewhere in here I should probably be using
> preservesinglequotes() but everywhere I attempt to use it in the UDF I get
a
> JIT compilation error. I also get a JIT if I put the preservesinglequotes
> function in the calling template
> [preservesinglequotes(checknull(charfield,0))]
>
> Any suggestions? Please? Unfortunately I used this function all through an
> app before finding the bug, so I would be psyched to make repairs to the
> function itself not all the code that uses it.
>
> - Sean
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~
> Sean Daniels
> Director, Engineering
> Marketplace Technologies, Inc
> (T): 207.363.7374
> (C): 207.332.6340
> (F): 240.269.6319
> ~~~~~~~~~~~~~~~~~~~~~~~~~~
> http://www.dealforce.com
> http://www.mergernetwork.com
>
>
>
>
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
