[thelist] ColdFusion -- Now 2 much escape!

[Raymond Camden]

> <cfscript>
> 	function SQLEsc(SQLVar) //ha! you should have seen the other name!
> 	{	return(Replace(SQLVar, "'", "''", "ALL");
> 	}
> </cfscript>
>
> Basically, a C+P of what .jeff contributed.
>
> When called -- @param = #SQLEsc(oStruct.Field)# -- the value is
> returned, as
> expected, with the single quotes escaped by prefacing (or suffixing?) with
> another single quote.
>
> However, this is now a regular variable or something strikingly similar
> enough to the ColdFusion Logic Machine whereby it treats it in
> the same way
> -- yep, it automagically escapes the single quotes. After a
> while, my values
> look like they've been targeted by a machine gun or something:
> "17'''''''''''''''''''''' R/L."

Ah, so you are passing this to the query:
	<CFQUERY ..
		update foo
		set blah = #SQLEsc(ComplexCrap)#
	</CFQUERY>
?  If so, then yes, what you are seeing makes sense... I guess. :)

> "Hmmm," I thought. "Maybe if I just Trim() the string, the same thing will
> happen, giving CF an excuse to escape the values automagically." Hope but
> nope. 8)

Now I'm confused. Going to confirm this.

> So, for the most fun like this I've had in some time, my function now is
> like this, and it works:
>
> <cfscript>
> 	function SQLEsc(SQLVar) //ha! you should have seen the other name!
> 	{	return(SQLVar);
> 	}
> </cfscript>
>
> Am I ready to leave this somewhat albatross solution to do the
> thing I need?
>
> Maybe I could get some help on this question?

In theory, if you want to use your old UDF, simply wrap the output in
PreserveSingleQuotes, and CF should completely ignore single quotes, ie, it
won't try to help you.

-Ray