Why is Sanitizing SQL Fragments so Highly Muddled?

[Tyler DeWitt]

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.

[Frederick Cheung]

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

[mike2r]

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).  

[Frederick Cheung]

On Wednesday, May 7, 2014 1:02:36 AM UTC+1, mike2r wrote:

On Monday, May 5, 2014 3:08:17 PM UTC-4, Tyler DeWitt wrote:

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).  

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

[mike2r]

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)

[Frederick Cheung]

On Wednesday, May 7, 2014 10:10:25 PM UTC+1, mike2r wrote:

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)

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