Quoting in ActiveRecord
Quite often I need to take a string from an HTML form, such as “one two three” and search a database field for each of the words contained in the string, eg:
SELECT * FROM table WHERE text ilike '%one%' AND text ilike '%two%' AND text ilike '%three%'
Obviously the strings need quoting, or I am going to be vulnerable to SQL injection attacks. In perl I used to do something like this:
join(" AND ", map {"field ilike " . $dbh->quote("%" . $_ . "%")} qw/one two three/)
I was hoping that ActiveRecord would have a nice way of doing this automatically when passed a string or array of strings, but there doesn’t seem to be one. Instead I came up with this:
@items = Item.find(:all, :conditions => @params["search"].split.map{|x| “item ilike ” + Item.quote(”%” + x + “%”)}.join(” AND “))
The interesting bit broken out looks like this:
"some string seperated by spaces".split.map{|x| "item ilike " + Item.quote("%" + x + "%")}.join(" AND ")
In particular the #quote method of the Item object: Item.quote("%" + x + "%")
The #quote method depends on a PostgreSQL database adaptor being used. The MySQL adapter has a #quote_string method that looks like it does the same thing, but I have not experimented with it.
It would be nice to know if there was a better way of doing this, particularly one that was independent of the database adaptor.
Thanks to leeo on IRC who pointed out that #quote is a method of AbstractConnector (from which both the PG and MySql addapers inherit)