I've actually found that the same thing happens when I use evaluate()
inside the CFQUERY block... it throws the exact same (Oracle syntax) error
and the generated SQL is not escaped properly. This isn't in a Mach-II
app, this is a pretty standard "straight" ColdFusion app. I know people
are probably going to scream about using evaluate(), but for this app
there are some very dynamic forms being built by ColdFusion we have to use
evaluate, there's no other way around it really. I'm waiting for a MM
support rep to get back to me so I can try to provide them with a
reproducible test case.
Can you explain what you mean by "packages" with respect to Oracle?
I'll definitely start to play with CFQUERYPARAM more now that the benefits
are clear to me... but I still want to track down the underlying cause of
this problem too.
-Cliff
On Tue, April 26, 2005 11:41 am, Kleanthis Economou said:
> Hey Cliff;
>
> first of all to me this seems rather odd. As far as I know, CF escapes
> single-quotes contained in a variable automatically (which is why
> PreserveSingleQuotes() exists).
>
> Have you tried the same code, with the same value in the variable
> without going through the MachII machinery? It may make no difference
> but it would be interesting to see if it does.
>
> Since you are using Oracle now, I would highly recommend using packages
> as a facade to your data. Not only it's faster, but it's also secure
> and allows you to completely remove SQL from your application which has
> a ton of benefits in turn.
>
> As a second best alternative, use bind variables in your queries (i.e.
> use <cfqueryparam>). You will see performance boosts - particularly in
> frequently used queries since Oracle caches the execution plan (in a
> nutshell).
>
> Obviously Oracle tunning is not for the faint at heart, but those are a
> couple of things that are pretty straight forward and it's a sin not to
> use since you (or your client) has invested in Oracle.
>
> Kleanthis
>
>
> On Apr 26, 2005, at 11:07, Cliff Meyers wrote:
>
>> I experienced some unexpected behavior today while I troubleshooting an
>> error in one of my first Mach-II apps. I was using a "bean" CFC to
>> store
>> data from a form and then insert it into an Oracle database. Inside my
>> CFQUERY block, I was using a method such as this...
>>
>> event.getArg("SurveyForm").getComment1()
>>
>> to insert text into the DB. I found out that when outputting the CFC
>> inside CFQUERY (as shown above, wrapped in ## naturally) that the
>> single
>> quotes in the text to be inserted weren't being escaped... thus causing
>> the Oracle DB syntax error.
>>
>> Has anyone else run into this, or have any ideas of what might be going
>> wrong? In the future to be safe I suppose I might just dump all the
>> arguments into a structure and then put those into the CFQUERY block,
>> but
>> I was a little surprised that this happened. Thanks!
>>
>>
>> -Cliff
>
>
>
> ----------------------------------------------------------
> You are subscribed to cfcdev. To unsubscribe, send an email to
> [email protected] with the words 'unsubscribe cfcdev' as the subject of
> the email.
>
> CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting
> (www.cfxhosting.com).
>
> An archive of the CFCDev list is available at
> www.mail-archive.com/[email protected]
>
>
>
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email to
[email protected] with the words 'unsubscribe cfcdev' as the subject of the
email.
CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting
(www.cfxhosting.com).
An archive of the CFCDev list is available at
www.mail-archive.com/[email protected]
