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

Question

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

Answer 1

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