107

I'm trying to write LIKE query.

I read that pure string quires aren't safe, however I couldn't find any documentation that explain how to write safe LIKE Hash Query.

Is it possible? Should I manually defend against SQL Injection?

Undo
  • 25,519
  • 37
  • 106
  • 129
Gal Weiss
  • 1,307
  • 3
  • 9
  • 8
  • Possible duplicate of [How to do a LIKE query in Arel and Rails?](http://stackoverflow.com/questions/4430578/how-to-do-a-like-query-in-arel-and-rails) – Pedro Rolo Jan 02 '17 at 14:06

5 Answers5

199

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)}%")
spickermann
  • 100,941
  • 9
  • 101
  • 131
  • This fails to escape `%` in the query string. It's not arbitrary "SQL injection" but still may function unexpectedly. – Beni Cherniavsky-Paskin Nov 16 '17 at 09:02
  • @BeniCherniavsky-Paskin : That is the whole point, you do not want to escape the `%` because the `%` is part of the `LIKE` syntax. If you escaped the `%` then the result would basically be a normal `=` query. – spickermann Nov 16 '17 at 09:05
  • 2
    Right, YOU want to use % wildcards in your pattern template but that pattern is parametrized with `query` variable, and in many cases you want to match literally the string in `query` variable, not allow `query` to use LIKE metacharacters. Let's take a more realistic example that %...%: the strings have a path-like structure, and you try to match `/users/#{user.name}/tags/%`. Now if I arrange my username to be `fr%d%`, I'll be able to observe `fred` and `frida`'s tags... – Beni Cherniavsky-Paskin Nov 16 '17 at 09:16
  • 3
    OK, what I'm after is combining this question with https://stackoverflow.com/questions/5709887/a-proper-way-to-escape-when-building-like-queries-in-rails-3-activerecord which suggests `sanitize_sql_like()`. – Beni Cherniavsky-Paskin Nov 16 '17 at 09:18
  • 2
    @BeniCherniavsky-Paskin Now I understand where you a coming from and you are right. I updated my answer to address that issue. – spickermann Nov 16 '17 at 10:04
  • 1
    Worth also mentioning `_` and \ characters as needing escaping. – Beni Cherniavsky-Paskin Nov 16 '17 at 10:22
  • this will return value even if the params[:query] is "" or nil – Vishal Feb 09 '23 at 04:32
  • @Vishal Sure, because that is how `LIKE` in SQL works. Passing an empty string to `LIKE` surrounded with `%` means: Match everything, no matter how it starts or ends. What would you expect instead? – spickermann Feb 09 '23 at 05:08
  • @spickermann I expected it should return nothing but it's not possible as this is the default behavior. we have to avoid blank string externally if we don't want this behavior – Vishal Feb 09 '23 at 06:35
  • Just use a conditional like `params[:query].present? ? Foo.where(like-query) : Foo.none`. There is nothing special with the `LIKE` query in this case. Even normal `where` conditions like `where(foo: params[:query])` will return unexpected results on `nil` or `''` that might need special treatment. – spickermann Feb 09 '23 at 06:42
46

Using Arel you can perform this safe and portable query:

title = Model.arel_table[:title]
Model.where(title.matches("%#{query}%"))
Pedro Rolo
  • 28,273
  • 12
  • 60
  • 94
  • 1
    This is the preferable solution, since Arel is sql-db-agnostic and has some internal input cleansing. Also is much more legible and consistent as far as code-style goes, IMHO. – Andrew Moore Jan 28 '17 at 14:49
  • How do you negate this? (i.e. NOT LIKE) `Model.where(title.matches("%#{query}%").not)` works, although the generated SQL is a little awkward: `WHERE (NOT (\`models\`.\`title\` LIKE '%foo%'))` – Noach Magedman Feb 27 '18 at 13:00
  • Aah...found it. `Model.where(title.does_not_match("%#{query}%"))`. Generates: `WHERE (\`models\`.\`title\` NOT LIKE '%foo%')` – Noach Magedman Feb 27 '18 at 13:04
  • 1
    Careful - this fails to sanitise `%` in untrusted input: ```>> ActiveRecord::VERSION::STRING => "5.2.3" >> field = Foo.arel_table[:bar] >> Foo.where(field.matches('%')).to_sql => "SELECT `foos`.* FROM `foos` WHERE `foos`.`bar` LIKE '%'"``` – vjt May 30 '19 at 17:33
  • @NoachMagedman or `Model.where.not(title.matches("%#{query}%"))`. `does_not_match` reads better though, IMO. – elquimista Oct 25 '19 at 14:52
  • The problem with Arel inside a `where` method is that it doesn't work well with `joins` and often breaks scope chains. The Arel expression is converted to an sql string when passed as argument to `where`, ignoring contexts like automatically constructed table aliases in the AR evaluation. Having it coded using sql strings often makes it easier to spot such anomalies in your code. – bert bruynooghe Oct 03 '22 at 11:59
9

For PostgreSQL it will be

Foo.where("bar ILIKE ?", "%#{query}%")
Khoga
  • 857
  • 2
  • 8
  • 26
  • The field has to be citext type to make case insensitive search work. Otherwise it will be the same as LIKE. – sekrett Mar 10 '21 at 06:56
4

In case if anyone performing search query on nested association try this:

Model.joins(:association).where(
   Association.arel_table[:attr1].matches("%#{query}%")
)

For multiple attributes try this:

Model.joins(:association).where(
  AssociatedModelName.arel_table[:attr1].matches("%#{query}%")
    .or(AssociatedModelName.arel_table[:attr2].matches("%#{query}%"))
    .or(AssociatedModelName.arel_table[:attr3].matches("%#{query}%"))
)

Don't forget to replace AssociatedModelName with your model name

Rajan Verma - Aarvy
  • 1,969
  • 18
  • 20
1

You can do 

MyModel.where(["title LIKE ?", "%#{params[:query]}%"])
Santhosh
  • 28,097
  • 9
  • 82
  • 87