Robby on Rails

Insert Hip Quote Here:

“In ancient times, hundreds of years before the dawn of history, an ancient race of people… the Druids. No one knows who they were or what they were doing… “ – Nigel Tufnel, Spinal Tap

Story Time…

Earlier, I was giving a customer of ours, Jared from CommunityWalk.com a quick tutorial on some of the features script/console… which lead to helping him with a SQL query. When I provided him with some working code he was curious about what I had done in the SQL query string that I was passing to find_by_sql.

WARNING

If you have ANY SQL queries that resembles the following, PLEASE READ THE REST OF THIS. :-)

values = params[:search]
RockLegend.find( :all, :conditions => "first_name = '#{values['first_name']}'" )

If you are doing that… then you are opening yourself up to some security problems. Let’s take a few minutes and discuss how you can make this more secure and still keep your code readable. (the best of both worlds!)

The ? Placeholder

RockLegend.find( :all, :conditions => ['first_name = ?', 'Nigel'] )

RockLegend.find( :all, :conditions => ['first_name = ? AND last_name = ?', 'Nigel', 'Tufnel'] )

You can pass it a hash as well.. and as long as you put everything in the same order as the ?s are placed… then all is well.

My only real problem with this approach is that it requires you to keep things in a specific sequential order… and who wants to keep track of that? So, I would like to recommend that you use named placeholders. Aside from that, it looks magical and I don’t like magical-looking code. I like easy to read code. :-)

Named Placeholders

If you already use these… you know how useful they can be in your SQL queries. If you haven’t seen them before… it’s because the Rails docs don’t really mention it and is something that comes from the underlying database library in Ruby. So what is so great about these?

RockLegend.find( :all, :conditions => ['first_name = :first_name', { :first_name => 'Nigel' } ] )

We are passing a hash with a matching key to the conditions option. Pretty neat, right? In this case with just one placeholder we just increased the amount of code to do the same thing. So, it might always be the best solution… but it is easier to read.

Let’s try another with multiple keys in our hash… infact, we’ll build the hash prior to calling find.

values = { :first_name => 'Nigel', :last_name => 'Tufnel'}
RockLegend.find( :all, :conditions => ['first_name = :first_name AND last_name = :last_name', values ] )

It will happily match the hash keys to the named placeholders in the conditions string. Again, nothing terribly exciting…but it is easier to read.

Who cares about order? Not named placeholders!

Okay, let’s mix things up a bit…

values = { :last_name => 'Tufnel', :first_name => 'Nigel' }
RockLegend.find( :all, :conditions => ['first_name = :first_name AND last_name = :last_name', values ] )

The hash keys were not added in the same order… but it still works!

Okay, now for one last quick example (it’s late and I am tired…).

I have a search mechanism on a site that allows you to search for a string of text across multiple fields. So, I have one string… but several fields to compare against.

conditions = "role = :role AND (first_name ~* :str OR last_name ~* :str OR nick_name ~* :str)"

Note: this string is using PostgreSQL regular expressions... (~*).

Here is a hash that with that matches the keys, :str and :role

values = { :str => '^(Nigel|Tufnel)$', :role => 'Guitar' }

If you look above, you’ll see that the conditions string contains four named placeholders… but the hash only has two keys. With the ? placeholder, we would have to pass the same vaule three times… which isn’t any fun to read or maintain. ;-/

So, with our new friends, named parameters, we can call find (or any find-like method) using this technique for placeholders.

RockLegend.find( :all, :conditions => [ conditions, values ] )

...and hopefully this is useful to you. :-)

Have fun!