The correct way to avoid %% when creating LIKE queries in Rails 3 / ActiveRecord

Question

I want to map the url field to the url prefix (which may contain percent signs), for example. .where("url LIKE ?", "#{some_url}%") . What is the very Rails path?

Answer 1

If I understand correctly, you are worried about the "%" appearing inside some_url and correctly; you should also worry about the built-in underscores ("_"), they are the LIKE version of ".". in regular expression. I don’t think there is any specific Rails way, so you will stay with gsub :

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

There is no need for string interpolation. You need to double the backslash to avoid their value from the database parser, so that the LIKE parser sees a simple "\%" and knows to ignore the sign with the escaped percentage.

You should check your logs to make sure that two backslashes pass. I get confusing results from checking things in irb using five (!) It gets the correct output, but I don't see the point in it; if anyone sees the point in five of them, an explanatory comment will be appreciated.

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

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

I also switched to the gsub block form to make it less annoying.

This is the standard syntax for SQL92, so it will work in any database that supports it, including PostgreSQL, MySQL, and SQLite.

Embedding one language inside another is always a nightmare cudd, and you cannot do it. There will always be ugly pieces that you just need to smile and carry.

Answer 2

Rails version 4.2.x has an active write method called sanitize_sql_like . So, you can make a search scope in your model, for example:

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

and call the scope as:

 Account.search('Test_%') 

The resulting sql escaped string:

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

More details here: http://edgeapi.rubyonrails.org/classes/ActiveRecord/Sanitization/ClassMethods.html

Answer 3

https://gist.github.com/3656283

Using this code

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

correctly escapes % between "sample" and "code" and corresponds to "AAAsample% codeBBB", but not for "AAAsampleBBBcodecodeCC" by default MySQL, PostgreSQL and SQLite3.

Answer 4

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