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
Many of you are probably familiar with this approach…
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?
Let’s first replace the above code with named parameters…
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.
Here is a string that I will pass to the find method.
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!
