Safe Activerecord Like Query

Safe ActiveRecord like query

To ensure that your query string gets properly sanitized, use the array or the hash query syntax to describe your conditions:

Foo.where("bar LIKE ?", "%#{query}%")

or:

Foo.where("bar LIKE :query", query: "%#{query}%")

If it is possible that the query might include the % character and you do not want to allow it (this depends on your usecase) then you need to sanitize query with sanitize_sql_like first:

Foo.where("bar LIKE ?", "%#{sanitize_sql_like(query)}%")
Foo.where("bar LIKE :query", query: "%#{sanitize_sql_like(query)}%")

How do I do a LIKE % query in ActiveRecord?

like_keyword = "%#{keyword}%"    
MyModel.where("description LIKE ?", like_keyword)

How to do a LIKE query in Arel and Rails?

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

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

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.

Rails: safe query against stored array

According to ActiveRecord and PostgreSQL guide you can do the following:

People.where('? = any("pets")', checkedPet)

Or

People.where('"pets" @> ?', "{#{checkedPet}}")

LIKE Query in rails 5 not working

According to this response if you are using Postgres you can try something like this :

field2_ilike_params = params[:array_for_field2].map { |p| "%p%" }
field3_ilike_params = params[:array_for_field3].map { |p| "%p%" }
Model.where(field1: params[:array_for_field1]).where("field2 ILIKE ANY ( array[?] ) AND field3 ILIKE ANY ( array[?] )", field2_ilike_params, field3_ilike_params)

Rails 5 best controller action to write a like query

In my opinion, you should keep only your index action and just accumulate the scopes on your @client variable.

Remember that your SQL query is only sent to the database when performing an Array method like each on your variable, not before.
So you can write something like:

def index
  @client = Client.all
  if params[:client_name].present?
    @client = @client.where("client_name LIKE ? ", "%#{params[:client_name]}%")
  else
    @client = @client.paginate(page: params[:page])
  end

  respond_to do |format|
    format.html
    format.json
  end
end

SQL query in Rails using % and LIKE

You need to add the %s in Ruby before quoting:

connection.query("SELECT * 
                  FROM test
                  WHERE y LIKE #{connection.quote('%' + name + '%')}
                  ORDER BY x ASC")

connection.quote will add single quotes to produce a valid SQL string literal and you want to get the %s inside that string literal, hence the Ruby string concatenation before connection.quote is called.

Or you could do it in SQL:

connection.query("SELECT * 
                  FROM test
                  WHERE y LIKE '%' || #{connection.quote(name)} || '%'
                  ORDER BY x ASC")

|| is the standard SQL string concatenation operator, you might need to use the concat function or something else if you're using a database that doesn't really support SQL.

You're better off using the ActiveRecord interface as spickermann suggests but sometimes you need to do it by hand so it is useful to know how.

How to include a LIKE clause in Rails query on HABTM join table

Try this:

.where("keywords.name LIKE ?", "%#{search}%")

EDIT

Note: this requires using .joins instead of .includes before the WHERE query.

---

Related Topics