SQL and Single Quote Problem

[aarontra]

I do not feel I a getting consistent results with trying to handle single quotes in sql with cold fusion.

I thought the function PreserveSingleQuotes()
helped with data with single quotes in it.



not sure why the code below would does not work

<cfset UpdateUser2_sql = &quot; update mytable set col1 = '#col1#', col2 = #col2#, col3 = '#col3#' where id = #id# >
<CFQUERY NAME=&quot;UpdateUser2&quot; DATASOURCE=&quot;#REQUEST.DATASOURCE#&quot;>
            #PreserveSingleQuotes(UpdateUser2_sql)#       
        </CFQUERY>

Thank you
Aaron

 

[r937]

you know, i wish somebody would write some decent examples for this function

do a google and the first umpteen hits all have the same lame one-liner description and a link to the macromedia doc, which isn't really all that useful either

so, i gots no additional info on PreserveSingleQuotes for you

when you say "does not work" aaron, what exactly is happening?

instead of CFQUERY, try this --

<CFOUTPUT>
#PreserveSingleQuotes(UpdateUser2_sql)#
</CFOUTPUT>

i expect that perhaps the quotes around the embedded variables are doubling up...


rudy

 

[r937]

bombboy, do your with/without test again, but this time, include the name O'Leary

not with <cfset names = &quot;'Joe', 'Bob', ... ??

but rather, coming in through #form.somefld#

remember that values being inserted in an sql query have to resolve to

... VALUES ( ... , 'O''Leary', ... )

rudy

 

[TruthInSatire]

gotcha thanks.