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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s