Rails 3: sql injection free queries

Question

I love new Rail 3!

The new query syntax is so awesome:

users = User.where(:name => 'Bob', :last_name => 'Brown')

But when we need to do something like

SELECT * FROM Users WHERE Age >= const AND Money > const2

We have to use

users = User.where('Age >= ? and money > ?', const, const2)

Which is not very cool. The following query is not safe because of SQL injection:

users = User.where('Age >= #{const} and money > #{const2}')

I like the C#/LINQ version

var users = DB.Where(u => u.Age >= const && u.Money > const2);

Is there a way to do something like that in Rails?

Answer 1

In Rails 3 you can chain these selections together. I'm not up on the specific syntax, but this is a good start: http://railscasts.com/episodes/202-active-record-queries-in-rails-3

The basic concept is that you can chain together scopes or where clauses, etc:

meta-code here:

users = User.where(:age_of_consent).where(:has_credit)

scope :age_of_consent where("age >= ?", 18)
scope :has_credit where("credit > ?", 10)

Answer 2

You might be interested in MetaWhere with which you can write:

users = User.where(:age >= const, :money > const2)

Answer 3

You can pass a hash of named parameters to your query which is an improvement over the anonymous positional parameters.

users = User.where('Age >= ? and money > ?', const, const2)

becomes (is is more similar to the LINQ syntax)

users = User.where('Age >= :const and money > :const2', {:const => const, :const2 => const2})

Answer 4

The new querying with rails isn't vulnerable to SQL injection. Any quotes in the argument are escaped.

Rails 3 AR has gained the delayed execution that LINQ has had for a while. This lets you chain any of the query methods. The only time you have to put 2 or more parts into a where is when you want an OR.

That aside, there are many different ways to do your query.

Users.where('age >= ?', age).where('money > ?', money)
Users.where('age >= ? and money > ?', age, money)

class User < ActiveRecord::Base
  scope :aged, lambda { |age| where('age >= ?', age) }
  scope :enough_money, lambda { |money| where('money > ?', money) }

  scope :can_admit, lambda { |age, money| aged(age).enough_money(money) }
end

Users.aged(18).enough_money(200)
Users.can_admit(18, 200)