How to do a LIKE query in Arel and Rails?

Question

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?

Conchaconchie

I updated the arel answer for the bonus. – Side

Answer 1

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)))

Side

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. – Convexoconcave

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. – Terrieterrier

is this protected against SQL injections? – Hardner

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: 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. – Side

Arel::Nodes::Matches is defined here. github.com/rails/arel/blob/master/lib/arel/nodes/binary.rb – Bogy

This does NOT protect fully against SQL injection. Try setting user_name to "%". The query will return matches – Nicollenicolson

s there any update to this in light of Rails 4? Something like User.where(:name .... – Serf

@MichaelKMadison it was added, but later reverted (see github.com/rails/rails/commit/…). There is a lengthy discussion about the motives in the commit comments. – Jareb

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. – Unpack

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. – Unpack

Use .gsub(/[%_]/, '\\\\\0') for escaping MySql wildcard chars. – Felishafelita

I'm on Rails 4.0.0, but NoMethodError: undefined method `like' for #<ActiveRecord::QueryMethods::WhereChain:0x007fe03b9dd0a0> – Leatrice

+Tom Rossi : you are right. It was just a proposal and it has been rolled back. sorry for the mess: github.com/rails/rails/commit/… – Side

This seems to be a better option compared to passing an Array because it avoids any ambiguous column references. – Stalinabad

Rails has sanitize_sql_like to safely escape wildcards. – Phiona

both Arel and LIKE ? protect against arbitrary SQL injection but not against wildcard injection: github.com/rails/arel/blob/… matches("#{user.name}%") is risky if user called himself %; matches("#{sanitize_sql_like(user.name)}%") is safe. – Phiona

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). – Jerri

Answer 2

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

Marlee

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). – Gavra

Isn't this vulnerable to SQL injection attacks? – Tractable

@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] – Marlee

Sorry for offtopic. I have the sql git of method to_sql or arel manager, how to execute the sql on db? – Chyme

Model.where(to_sql_result) – Side

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? – Quota

Answer 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

Edwards

Answer 4

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

Tusche