Tutorial :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?


Solution:1

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)  


Solution:2

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

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


Solution:3

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)  


Solution:4

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

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »