125

I want to do something like:

SELECT * FROM USER WHERE NAME LIKE '%Smith%';

My attempt in Arel:

# params[:query] = 'Smith'
User.where("name like '%?%'", params[:query]).to_sql

However, this becomes:

SELECT * FROM USER WHERE NAME LIKE '%'Smith'%';

Arel wraps the query string 'Smith' correctly, but because this is a LIKE statement it doesnt work.

How does one do a LIKE query in Arel?

P.S. Bonus--I am actually trying to scan two fields on the table, both name and description, to see if there are any matches to the query. How would that work?

Pedro Rolo
  • 28,273
  • 12
  • 60
  • 94
filsa
  • 1,646
  • 3
  • 15
  • 16

4 Answers4

291

This is how you perform a like query in arel:

users = User.arel_table
User.where(users[:name].matches("%#{user_name}%"))

PS:

users = User.arel_table
query_string = "%#{params[query]}%"
param_matches_string =  ->(param){ 
  users[param].matches(query_string) 
} 
User.where(param_matches_string.(:name)\
                       .or(param_matches_string.(:description)))
Pedro Rolo
  • 28,273
  • 12
  • 60
  • 94
  • 1
    That's correct. Either approach should work. But this one uses the Arel API more directly. This is important if you want to use the full power of Arel, e.g. to construct complex queries. – rlkw1024 Oct 18 '11 at 18:29
  • 10
    Unlike using `where("name like ?", ...)`, this approach is more portable across different databases. For example, it would result in `ILIKE` being used in a query against a Postgres db. – dkobozev Nov 02 '11 at 21:46
  • 22
    is this protected against SQL injections? – sren May 28 '12 at 02:17
  • I do not see any reason why it would make sense to implement the 'matches' method without escaping the provided input within. It must be safe against SQL Injections. Anyway, if you're keen on that, you may always verify it within the code: https://github.com/brynary/arel/tree/master/lib/arel I am also curious about the answer, and I did not find the code for the Arel::Nodes::Matcher class. – Pedro Rolo Jun 02 '12 at 14:41
  • Arel::Nodes::Matches is defined here. https://github.com/rails/arel/blob/master/lib/arel/nodes/binary.rb – OmniBus Jul 25 '12 at 11:37
  • 7
    This does NOT protect fully against SQL injection. Try setting user_name to "%". The query will return matches – travis-146 Jul 22 '13 at 15:22
  • s there any update to this in light of Rails 4? Something like User.where(:name .... – Michael K Madison Aug 05 '13 at 22:56
  • @MichaelKMadison it was added, but later reverted (see https://github.com/rails/rails/commit/8d02afeaee8993bd0fde69687fdd9bf30921e805). There is a lengthy discussion about the motives in the commit comments. – fabi Aug 26 '13 at 12:39
  • 5
    I tried to sql inject using params directly, `User.where(users[:name].matches("%#{params[:user_name]}%"))`, I tried `TRUNCATE users;` and other such queries and nothing happened on the sql side. Looks safe to me. – earlonrails Aug 28 '13 at 18:56
  • 4
    Also if you don't know you can try from console via `User.where(users[:name].matches("%TRUNCATE users;%")).to_sql` This will show that it is escaped and contained in single quotes. – earlonrails Aug 28 '13 at 19:02
  • 5
    Use ```.gsub(/[%_]/, '\\\\\0')``` for escaping MySql wildcard chars. – aercolino Aug 30 '13 at 10:58
  • I'm on Rails 4.0.0, but NoMethodError: undefined method `like' for # – Tom Rossi Sep 04 '13 at 18:47
  • +Tom Rossi : you are right. It was just a proposal and it has been rolled back. sorry for the mess: https://github.com/rails/rails/commit/8d02afeaee8993bd0fde69687fdd9bf30921e805 – Pedro Rolo Sep 05 '13 at 09:40
  • This seems to be a better option compared to passing an Array because it avoids any ambiguous column references. – Carson Reinke Jan 24 '14 at 13:49
  • 1
    Rails has `sanitize_sql_like` to safely escape wildcards. – Beni Cherniavsky-Paskin Jan 03 '18 at 11:51
  • 3
    both Arel and `LIKE ?` protect against arbitrary SQL injection but not against wildcard injection: https://github.com/rails/arel/blob/6cf061ed6f3f9c8128385765c07eaa4f8a43bd34/test/visitors/test_to_sql.rb#L299-L302 `matches("#{user.name}%")` is risky if user called himself `%`; `matches("#{sanitize_sql_like(user.name)}%")` is safe. – Beni Cherniavsky-Paskin Jan 03 '18 at 12:19
  • You can also use `matches(term, nil true)` to do a `LIKE` instead of an `ILIKE`. If you are doing an `ILIKE` on an indexed column (you'll want a `BTREE` index for `LIKE`/`ILIKE`), the first character needs to be a non-alphabetic character (a character that cannot be affected by upper/lower case conversion). – Samsinite Aug 14 '23 at 18:59
118

Try

User.where("name like ?", "%#{params[:query]}%").to_sql

PS.

q = "%#{params[:query]}%"
User.where("name like ? or description like ?", q, q).to_sql

Aaand it's been a long time but @cgg5207 added a modification (mostly useful if you're going to search long-named or multiple long-named parameters or you're too lazy to type)

q = "%#{params[:query]}%"
User.where("name like :q or description like :q", :q => q).to_sql

or 

User.where("name like :q or description like :q", :q => "%#{params[:query]}%").to_sql
Reuben Mallaby
  • 5,740
  • 4
  • 47
  • 45
  • 9
    How does Rails know not to escape `%` in the substituted string? It seems like if you only wanted a one-sided wildcard, there's nothing stopping the user from submitting a query value that includes `%` at both ends (I know that in practice, Rails prevents `%` from showing up in a query string, but it seems like there should be protection against this at the ActiveRecord level). – Steven Dec 31 '10 at 08:26
  • 8
    Isn't this vulnerable to SQL injection attacks? – Behrang Nov 14 '11 at 00:58
  • 7
    @Behrang no 8) User.where("name like %#{params[:query]}% or description like%#{params[:query]}%").to_sql would be vulnerable, but, in the format I show, Rails escapes params[:query] – Reuben Mallaby Nov 18 '11 at 21:36
  • Sorry for offtopic. I have the sql git of method `to_sql` or arel manager, how to execute the sql on db? – Малъ Скрылевъ Apr 26 '14 at 18:02
  • Model.where(to_sql_result) – Pedro Rolo May 31 '16 at 10:31
  • New to Rails. Just wondering why `where('foo LIKE ?', '%bar%')` works but `where('foo LIKE ?' => '%bar%')` doesn't - or should it and I'm missing something? – Mitya Jul 28 '22 at 15:50
3

Reuben Mallaby's answer can be shortened further to use parameter bindings:

User.where("name like :kw or description like :kw", :kw=>"%#{params[:query]}%").to_sql
Chris Bloom
  • 3,526
  • 1
  • 33
  • 47
cgg5207
  • 75
  • 2
0

Don't forget escape user input. You can use ActiveRecord::Base.sanitize_sql_like(w)

query = "%#{ActiveRecord::Base.sanitize_sql_like(params[:query])}%"
matcher = User.arel_table[:name].matches(query)
User.where(matcher)

You can simplify in models/user.rb

def self.name_like(word)
  where(arel_table[:name].matches("%#{sanitize_sql_like(word)}%"))
end
kuboon
  • 9,557
  • 3
  • 42
  • 32