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?
4 Answers
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
5 Comments
Nikola M.
In my opinion, this is the most elegant way to solve this issue
Surya
Guess from rails 5.* sanitize_sql_like is not supported Any other alternative?
phlegx
It is supported on Rails 5.x. See here github.com/rails/rails/blob/v5.2.1.1/activerecord/lib/…
Teoulas
FYI these are protected/private methods on 5.0 and 5.1. They're only public on 5.2.
stwr667
@Teoulas is right. Rails 5.0 and 5.1, use this to call them:
ActiveRecord::Base.send(:sanitize_sql_like, "Test_%")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.
9 Comments
smathy
Btw, if (I don't know) you need to get double-backslashes into those strings, the above won't do it. `'\\' => ` in Ruby (and most languages).
mu is too short
@Jason: Five escapes produces the right thing in
irb, thanks for catching that. I'm guessing that it goes through as three tokens "\\", "\\", and "\%" which, after escape processing, ends up as "\\%" as desired. I usually just keep adding them until it works then reverse engineer the justification. Does anyone know of a quote operator that really does produce literal results without any processing or big pile of mess?smathy
Something else you could do is to add an
ESCAPE clause, like this: .where( "url LIKE ? ESCAPE '!'", some_url.gsub('%', '!%').gsub('_', '!_')mu is too short
@Jason: That's genius. I wish I could give you points for a comment or just transfer ownership of the answer to you.
Lucas Wiman
You could also use capturing groups instead of the block syntax (shorter and probably faster):
.where("url LIKE ? ESCAPE '!'", some_url.gsub(/([!%_])/, '!\1') |
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.
Comments
Post.where('url like ?', "%#{some_url + '%'}%)
10 Comments
Rein Henrichs
@Sam Very close.
Post.where('url like ?', "%#{some_url}%")
Sam 山
You code just makes a like condition match other entries. @Costa-Shaprio is looking for a way to do what your doing plus having
% or at least that is how I'm reading it ie 'which may contain percent signs'mu is too short
@Rein: No. That changes the nature of the match ("begins with" becomes "anywhere in") and does nothing to account for an embedded "%" in
some_url let alone underscores.Rein Henrichs
@mu Did you check to see if ActiveRecord's sql sanitization already escapes %s before you commented?
mu is too short
@Rein: How would AR tell the difference between an intentional
%x%x% and an accidental "%#{a}%" when a just happens to be an unescaped 'x%x'? The first (intentional) case wants "anything x anything x anything" whereas the second would want "anything x%x anything" but get "anything x anything x anything" because no one escaped it. By the time AR sees the argument for LIKE, it just has a string that has embedded SQL syntax and it is too late to infer the programmer's intent. |
X%for some fixedX). There are some notes on this over here: stackoverflow.com/questions/1566717/…%X%patterns though, that'll almost certainly hand you a table scan. Sorry that I don't have an authoritative reference but you could probably grok a query plan and see what happens.