Forum: Ruby on Rails Does RoR + Oracle use bind variables?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Chris (Guest)
on 2006-04-19 18:45
Does ROR + Oracle use bind variables?

Using :conditions in a find statement gives the appearance that bind
variables might be used :

header = "test header"
Posts.find(:all,:conditions=>["header = ?",header])

i.e. Does AR just pass the whole string to oracle or the SQL and the
parameters?

I'm in an oracle shop and my boss wants to know, i'd be very greatful if
you could tell me,

Thanks,
Chris
David R. (Guest)
on 2006-04-19 19:00
(Received via mailing list)
Hi, Chris.

That form of the :conditions parameter causes the value of "header"
to be sanitized (to prevent, for example, a SQL injection attack)
before it replaces the question mark. The resulting condition is what
gets built into the query that gets executed, without bind variables.
If you're running against an Oracle database for development, you can
see the exact query in RAILS_ROOT/log/development.log.

There's a good explanation of this form of the :conditions parameter,
as well another one that uses named variables instead of question
marks, at http://api.rubyonrails.com. Look for the find() method in
the lower left frame, click on its link, and scroll down in the main
frame to the section on "Conditions".

Hope this helps,
David
Chris (Guest)
on 2006-04-19 19:06
Thanks David,

Does this mean that you cannot use Bind variables with Oracle + ROR?

Thanks again,

Chris
Chris (Guest)
on 2006-04-19 19:10
If you can't use true oracle bind variables, and can only use dynamic
SQL, then queries cannot be as optimized as they could be.  With oracle
this means that no matter how many CPUs you add, you will still be
hitting it hard.
David R. (Guest)
on 2006-04-19 19:55
(Received via mailing list)
You can always use find_by_sql and provide the exact SQL yourself.
That would make your code less portable, but more performant. Looks
like we'll always have that tradeoff. :-)

Can you do me a favor and trap one of these queries in your dev log?
I want to make sure that what I told you is true. I've been doing
some research (meet my assistant, Mr. Google), and I'm finding some
discussions from the end of 2004 that imply that the functionality
you want could be in place in the Oracle adapter. I'll try to track
it down from my end as well.

David
Tom M. (Guest)
on 2006-04-19 22:00
(Received via mailing list)
This isn't completely true.

Since Oracle 9ish timeframe, Oracle has the option to
automatically convert common queries into placeholder
based queries.

Placeholders *would* be nice, and I'm sure we'll get
there eventually.

Whenever this comes up, I hear people speaking of
SQL injection. I wonder if they know that using
true placeholders, SQL injection is not an issue?

--
-- Tom M.
This topic is locked and can not be replied to.