ActiveRecord Queries – Drop the Question Marks

July 30, 2012 • Damien White

You learn something new everyday! Of course it’s in the documentation, but it’s easy to overlook features.

Background

As you may or may not know, ActiveRecord lets you specify conditions in a multitude of ways. Take the first example from the docs:

class User < ActiveRecord::Base
  def self.authenticate_unsafely(user_name, password)
    where("user_name = '#{user_name}' AND password = '#{password}'").first
  end
end

authenticate_unsafely is an aptly named method. Do you see the problem? Yup, you’re opening yourself up for SQL injection. So that’s NOT the way to do things. Instead ActiveRecord allows you to use placeholders like the second example from the docs:

class User < ActiveRecord::Base
  def self.authenticate_safely(user_name, password)
    where("user_name = ? AND password = ?", user_name, password).first
  end
end

Using that syntax, the conditions are sanitized so when the query is sent to your database, no nasty injection will occur.

As an aside, there’s an even easier way to perform a sanitized query like the last example:

class User < ActiveRecord::Base
  def self.authenticate_safely_simply(user_name, password)
    where(:user_name =&gt; user_name, :password => password).first
  end
end

That’s a nice shortcut for when you have a bunch of AND conditions.

My Problem

I was writing a WHERE clause that looked for a value in multiple columns using an OR instead of an AND. My first attempt looked something like this:

Book.where("isbn = ? OR ean = ? OR asin = ?", @search_term, @search_term, @search_term).first

Yuck. Not very DRY. I figured there had to be a better way, and I was correct.

Named Bind Variables

It turns out that ActiveRecord will allow you to use variables in your conditions. This turns my ugly query from above into this little gem:

Book.where("isbn = :term OR ean = :term OR asin = :term", :term => @search_term).first

W00t! I wasn’t aware that named bind variables existed. Of course my little example is DRY now, but how often do you write a query like mine? Not often (though I did come across this example while writing this post). As I’m sure you guessed, there are better uses for named conditions. Take a really long query (also from the docs), e.g.:

Company.where(
  "id = :id AND name = :name AND division = :division AND created_at &gt; :accounting_date",
  { :id => 3, :name => "37signals", :division => "First", :accounting_date => '2005-01-01' }
).first

Imagine that using the question mark syntax. Sure you could do it, but with the variables in the query, it is better for readability.

Conclusion

I figured that if I just discovered this, there must be others that have done the same, and I was right! There’s so much stuff you can do; you can’t know everything. Thankfully there are the ActiveRecord API Docs, but of course finding things in documentation isn’t always the easiest. I hope this blog post will help guide others to the docs.

Posted in ruby, ruby on rails and tagged with ActiveRecord, Rails, Ruby

Damien White

I am a software architect with over 16 years of experience. I simply love coding! I have a driving passion for computers and software development, and a thirst for knowledge that just cannot be quenched. I'm happy to share what I know in my quest to learn as much as possible. I focus most of my time on web development using Ruby on Rails, Ember.js, and ASP.NET MVC.

comments powered by Disqus