Forum: Ruby on Rails Why is Sanitizing SQL Fragments so Highly Muddled?

F5eae8591c66d4f8aeb4c2533f231e38?d=identicon&s=25 Tyler DeWitt (Guest)
on 2014-05-06 08:14
(Received via mailing list)
I've found there are some times where jumping straight to the database
is
easier than going through ActiveRecord (for instance: odd calculations
being performed or large sets of data being returned that don't need to
be
instantiated into AR models).  I can't find an easy or accepted way to
bind
variables in a custom SQL clause.  sanitize_sql_array is protected, so
you
would have to call it with send(), which just feels dirty.
 ActiveRecord::Base.connection.quote() doesn't play well with dates.

Am I missing a function that let's me use placeholders like the
ActiveRecord where() with my plain SQL clauses?

If not, has this been a specific design to make sanitizing SQL fragments
difficult? Perhaps to discourage it? Or is it due to the different
database
drivers?

Rails is usually straight forward and intuitive, I've just found with
SQL
fragments I have to pull teeth to kinda get it to work.
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2014-05-06 17:25
(Received via mailing list)
On Monday, May 5, 2014 8:08:17 PM UTC+1, Tyler DeWitt wrote:
>
> I've found there are some times where jumping straight to the database is
> easier than going through ActiveRecord (for instance: odd calculations
> being performed or large sets of data being returned that don't need to be
> instantiated into AR models).  I can't find an easy or accepted way to bind
> variables in a custom SQL clause.  sanitize_sql_array is protected, so you
> would have to call it with send(), which just feels dirty.
>  ActiveRecord::Base.connection.quote() doesn't play well with dates.
>
>
Am I missing a function that let's me use placeholders like the
> ActiveRecord where() with my plain SQL clauses?
>
> If not, has this been a specific design to make sanitizing SQL fragments
> difficult? Perhaps to discourage it? Or is it due to the different database
> drivers?
>
> Rails is usually straight forward and intuitive, I've just found with SQL
> fragments I have to pull teeth to kinda get it to work.
>

sanitize_sql was made protected a while back (I think rails 2) if my
memory
is correct. I think the position was to make it awkward to use in
controllers etc. since that  was what people were doing

Fred
F50d3b02eee623a2172b58c09fe31c2c?d=identicon&s=25 mike2r (Guest)
on 2014-05-07 02:03
(Received via mailing list)
On Monday, May 5, 2014 3:08:17 PM UTC-4, Tyler DeWitt wrote:
> ActiveRecord where() with my plain SQL clauses?
>
> If not, has this been a specific design to make sanitizing SQL fragments
> difficult? Perhaps to discourage it? Or is it due to the different database
> drivers?
>
> Rails is usually straight forward and intuitive, I've just found with SQL
> fragments I have to pull teeth to kinda get it to work.
>

Jumping straight to the database is discouraged in Rails and it's
something
I've only rarely done or seen a need to do.  The main reason is that one
of
the goals of ActiveRecord is to make your application database agnostic.
 You can use your application with MySQL, PostgreSQL, etc. and not have
to
worry about knowing the specific ins and outs of each database, it just
works.  I would argue that has gotten a little muddy as 4.1 has a few
features that are specific to PostgreSQL.  At any rate, writing SQL
fragments is discouraged because you potentially now make things
specific
to one database management system (or even worse, a specific version of
a
database management system) rather than relying on ActiveRecord to
handle
the specifics.  You've started to stray from the conventions and,
therefore, start to lose the efficiencies and make it more difficult to
support the application (particularly if another developer inherits the
application).
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2014-05-07 10:44
(Received via mailing list)
On Wednesday, May 7, 2014 1:02:36 AM UTC+1, mike2r wrote:
> not have to worry about knowing the specific ins and outs of each database,
> it just works.  I would argue that has gotten a little muddy as 4.1 has a
> few features that are specific to PostgreSQL.  At any rate, writing SQL
> fragments is discouraged because you potentially now make things specific
> to one database management system (or even worse, a specific version of a
> database management system) rather than relying on ActiveRecord to handle
> the specifics.  You've started to stray from the conventions and,
> therefore, start to lose the efficiencies and make it more difficult to
> support the application (particularly if another developer inherits the
> application).
>

I think database agnosticism is a bit of a pipe dream at the application
level, unless all you are ever doing is updating records one at a time
or
running very simple queries. Differences in query execution or odd side
corners that you hadn't realised you rely on (eg typecasting behaviour:
in
mysql "select 0 = 'z'" returns 1, on other databases it returns 0) will
bite you.

Active Record can only express a subset of queries so SQL fragments are
inevitable, unless you never need a NOT, OR, LIKE etc. condition
(although
rails 4.1 does handle not for you)

Fred
F50d3b02eee623a2172b58c09fe31c2c?d=identicon&s=25 mike2r (Guest)
on 2014-05-07 23:11
(Received via mailing list)
On Wednesday, May 7, 2014 4:42:15 AM UTC-4, Frederick Cheung wrote:
>>> Jumping straight to the database is discouraged in Rails and it's
>> therefore, start to lose the efficiencies and make it more difficult to
>
> Active Record can only express a subset of queries so SQL fragments are
> inevitable, unless you never need a NOT, OR, LIKE etc. condition (although
> rails 4.1 does handle not for you)
>
> Fred
>
>
I'm not sure I follow you, but I will admit this isn't my strongest area
of
knowledge.  Most OR or LIKE conditions I can handle in a where
statement.
For example:

User.where("name like :name OR department=:dept", name: "A%", dept: 101)

would produce all users whose name begins with A or are in department
101.
 You may have examples that can't be used in where, the only one I've
run
into is NOT, which, as you said, was added in Rails 4.1.  I have had
some
other database specific (postgreSQL) situations where I've used direct
SQL
statements, but it's rare.

I can think of another area which would bite you regularly, which are
booleans.  Some databases use 1 and 0, some T and F, some Y and N.
Rails
abstracts this away. For example, the following would work regardless of
database used:

Order.where('approved=?', true)
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2014-05-07 23:14
(Received via mailing list)
On Wednesday, May 7, 2014 10:10:25 PM UTC+1, mike2r wrote:
>> inevitable, unless you never need a NOT, OR, LIKE etc. condition (although
>> rails 4.1 does handle not for you)
>>
>>
>> I'm not sure I follow you, but I will admit this isn't my strongest area
> of knowledge.  Most OR or LIKE conditions I can handle in a where
> statement. For example:
>
> User.where("name like :name OR department=:dept", name: "A%", dept: 101)
>
>
We're talking semantics then - I would call what's in your call to where
a
sql fragment.

Fred
Please log in before posting. Registration is free and takes only a minute.
Existing account

NEW: Do you have a Google/GoogleMail, Yahoo or Facebook account? No registration required!
Log in with Google account | Log in with Yahoo account | Log in with Facebook account
No account? Register here.