Does RoR + Oracle use bind variables?

Rails
1

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

2

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

3

Thanks David,

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

Thanks again,

Chris

4

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. :slight_smile:

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

5

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.

6

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.