I remember running into this when I first starting developing with ColdFusion. I
remember mentioning it to someone in the field and I got a kind of "CF automatically
escapes single quotes in an SQL query, so you shouldn't have to" type answer. Of
course that is true, but I was using Evaluate() on some of my query values so I
assumed that CF was not touching the value inside the function in regards to escaping
single quotes.
Thanks for proving I wasn't crazy, Mike!
Also, this is the kind of situation where a UDF would be the perfect solution.
-Andy
> -----Original Message-----
> From: Michael Dinowitz [mailto:[EMAIL PROTECTED]]
> Sent: Friday, April 27, 2001 2:53 PM
> To: CF-Talk
> Subject: Re: evaluate(), single ', a SQL insert
>
>
> I just wrote a paper on that bug. Basically, the escaping of single quotes
> is done in the evaluation of the variable within CFQUERY. The execute
> function stops that from happening on all versions of CF from 4 on up
> (didn't test earlier). If you have to use an evaluate, do a
> replace on it to
> escape the single quotes.
>
>
> >
> > Hey everyone;
> > Again - my mind is mush this week (hurray for sleeping in on a
> Saturday).
> Anyways -
> >
> > The problem is the SQL chokes because of ' characters in a form
> field I'm
> passing to it. Normally I can work with this, but because I'm doing a call
> against a dynamically generated variable, it keeps hacking on me.
> >
> > Here's code:
> >
> > <CFOUTPUT query="Questions_List">
> > <CFSET findmatch = "num#questions_list.QuestionID#">
> > <CFSET findvalue = "form.num#questions_list.QuestionID#">
> >
> > <CFIF #isdefined(findmatch)#>
> > <CFquery Datasource="#AppPDS#">
> > Insert INTO QuestionResponse
> > (ConsultantID, QuestionID, Response, QuestionnaireResponseID)
> > Values
> > ('#szCandidateID#', '#Questions_List.QuestionID#',
> '#evaluate(findvalue)#', '#New_ID.newnumber#')
> > </cfquery>
> > </cfif>
> >
> > </cfoutput>
> >
> > As you can see, I'm doing a dynamic check against a variety of
> form.fields
> that may or may not be returned. So when inserting into the
> Response field,
> I have(?) to run an evaluate. This evaluate returns the data (text) that
> sometimes includes the ' character. When it does, the insert
> statement hacks
> and dies.
> >
> > How can I adjust the SQL (or something else) to get around this problem?
> Any suggestions? I totally appreciate any and all assistance on
> this issue.
> >
> > Stephen R. Cassady
> >
> >
> > Archives: http://www.mail-archive.com/[email protected]/
> > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
> >
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
