Safe ActiveRecord like query

Question

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?

Solution 1

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)}%")

Solution 2

Using Arel you can perform this safe and portable query:

title = Model.arel_table[:title]
Model.where(title.matches("%#{query}%"))

Solution 3

For PostgreSQL it will be

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

Solution 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

Solution 5

You can do

MyModel.where(["title LIKE ?", "%#{params[:query]}%"])

View more solutions

Comments

[Andrew Moore]

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.

[Beni Cherniavsky-Paskin]

This fails to escape % in the query string. It's not arbitrary "SQL injection" but still may function unexpectedly.

[spickermann]

@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.

[Beni Cherniavsky-Paskin]

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]

OK, what I'm after is combining this question with stackoverflow.com/questions/5709887/… which suggests sanitize_sql_like().

[spickermann]

@BeniCherniavsky-Paskin Now I understand where you a coming from and you are right. I updated my answer to address that issue.

[Beni Cherniavsky-Paskin]

👍 Worth also mentioning _ and \ characters as needing escaping.

[Noach Magedman]

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]

Aah...found it. Model.where(title.does_not_match("%#{query}%")). Generates: WHERE (`models`.`title` NOT LIKE '%foo%')

[Santhosh]

@mikkeljuhl Please look at my answer carefully.

[vjt]

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 '%'"

[elquimista]

@NoachMagedman or Model.where.not(title.matches("%#{query}%")). does_not_match reads better though, IMO.

[sekrett]

The field has to be citext type to make case insensitive search work. Otherwise it will be the same as LIKE.