a proper way to escape %% when building LIKE queries in Rails 3 / ActiveRecord

I want to match a url field against a url prefix (which may contain percent signs), e.g. .where("url LIKE ?"开发者_StackOverflow中文版, "#{some_url}%"). What's the most Rails way?

From Rails version 4.2.x there is an active record method called sanitize_sql_like. So, you can do in your model a search scope like:

scope :search, -> search { where('"accounts"."name" LIKE ?', "#{sanitize_sql_like(search)}%") }

and call the scope like:

Account.search('Test_%')

The resulting escaped sql string is:

SELECT "accounts".* FROM "accounts" WHERE ("accounts"."name" LIKE 'Test\_\%%')

Read more here: http://edgeapi.rubyonrails.org/classes/ActiveRecord/Sanitization/ClassMethods.html

If I understand correctly, you're worried about "%" appearing inside some_url and rightly so; you should also be worried about embedded underscores ("_") too, they're the LIKE version of "." in a regex. I don't think there is any Rails-specific way of doing this so you're left with gsub:

.where('url like ?', some_url.gsub('%', '\\\\\%').gsub('_', '\\\\\_') + '%')

There's no need for string interpolation here either. You need to double the backslashes to escape their meaning from the database's string parser so that the LIKE parser will see simple "\%" and know to ignore the escaped percent sign.

You should check your logs to make sure the two backslashes get through. I'm getting confusing results from checking things in irb, using five (!) gets the right output but I don't see the sense in it; if anyone does see the sense in five of them, an explanatory comment would be appreciated.

UPDATE: Jason King has kindly offered a simplification for the nightmare of escaped escape characters. This lets you specify a temporary escape character so you can do things like this:

.where("url LIKE ? ESCAPE '!'", some_url.gsub(/[!%_]/) { |x| '!' + x })

I've also switched to the block form of gsub to make it a bit less nasty.

This is standard SQL92 syntax, so will work in any DB that supports that, including PostgreSQL, MySQL and SQLite.

Embedding one language inside another is always a bit of a nightmarish kludge and there's not that much you can do about it. There will always be ugly little bits that you just have to grin and bear.

https://gist.github.com/3656283

With this code,

Item.where(Item.arel_table[:name].matches("%sample!%code%"))

correctly escapes % between "sample" and "code", and matches "AAAsample%codeBBB" but does not for "AAAsampleBBBcodeCCC" on MySQL, PostgreSQL and SQLite3 at least.

Post.where('url like ?', "%#{some_url + '%'}%)