escaping query for SQL LIKE in rails

Question

In rails source (https://github.com/rails/rails/blob/fe4b0eee05f59831e1468ed50f55fbad0ce11e1d/activerecord/lib/active_record/sanitization.rb#L112) there is a sanitize_sql_like method that (I am hoping) will sanitize strings before using them with a SQL LIKE

however, I can't seem to use that, as Rails says that method doesn't exist.

My string has an apostrophe in it and the query is

@query = "Joe's"
Model.where("lower(field) LIKE ?", "%#{@query}%") 

Using ActiveRecord::Base.sanitize doesn't help, as there are no results for the query.

How can I escape @query, and keep my SQL secured?

Answer 1

I've solved this same problem (on MySQL) using ActiveRecord::Sanitization::ClassMethods to properly sanitize user input. Unfortunately, the methods defined in ActiveRecord::Sanitization::ClassMethods are declared as protected, so they are only accessible in the scope of a Model class. The methods defined in ActiveRecord::Sanitization::ClassMethods are mixed in to all Model classes, so they are available directly from within the scope of a Model. That requires you to define a class/instance method or scope (ActiveRecord scope) on your model to use them, rather than using them externally, as in your example. However, design-wise, it's probably preferable to encapsulate the query logic in the Model anyway.

This solution also has the advantage of not only escaping the single-quote character, but also escaping other characters that would be interpreted by a SQL like query, such as the '%' character (among others). This should properly prevent SQL injection by escaping the the other characters which might be able to cause values to be interpreted rather than being treated as literals. There are also additional sanitization methods defined in ActiveRecord::Sanitization::ClassMethods that are useful for embedding user input (or other tainted input) in other SQL query contexts. Here is a working solution, tested on MySQL.

class Model  < ActiveRecord::Base
  # Finds a Model by case-insensitive substring match on Model.field
  #
  # @param query [String] A value to use in the substring match.
  # @return [ActiveRecord::Relation] An `Relation` of `Model`s whose
  #   `field` includes the `query` substring.
  scope :find_by_field_substring, ->(query) do
    where(arel_table[:field].matches("%#{sanitize_sql_like(query)}%"))
  end
end 

You can then access this scope like this:

Model.find_by_field_substring "Joe's"
=> #<ActiveRecord::Relation [#<Model id: 1, field: "Joe's">]>

The usage of both ActiveRecord scopes and ActiveRecord::Relations are pretty well documented, but may only be available in newer versions of Rails.

Note that your database may require a second parameter to the sanitize_sql_like method to specify a different escape character than '\'.

Also note that escaping like this doesn't work for MySQL if it is running in NO_BACKSLASH_ESCAPES mode, because it forces a different mechanism for escaping values. If you're using NO_BACKSLASH_ESCAPES mode see this answer.

I haven't tested this part, but rather than using Arel, the SQL AST builder that sits underneath ActiveRecord, one could probably also use the style suggested in your initial example, as long as it is defined within the Model class.

class Model  < ActiveRecord::Base
  scope :find_by_field_substring, ->(query) do
    where("lower(field) LIKE ?", "%#{sanitize_sql_like(query)}%") 
  end
end

Answer 2

If you use where properly, it will escape the input automatically

@query = "Joe's"
Model.where("lower(field) LIKE ?", "%#{@query}%") 

Just note that your query is wrong. You have a lower() operator, then you pass an input which is not lower-case. The query will always return 0.

Moreover, lower() will reduce the ability of the database to use the index. In most databases, LIKE is already case insensitive (except for PostgreSQL where you should use ILIKE).

query = "Joe's"
Model.where("field LIKE ?", "%#{query}%") 

or

query = "Joe's"
Model.where("field ILIKE ?", "%#{query}%") 

Here's a real example on a real database. As you can see, the input is properly escaped in the final SQL.

> query = "Joe's"
> User.where("lower(email) LIKE ?", "%#{query}%") 
  User Load (4.4ms)  SELECT "users".* FROM "users"  WHERE (lower(email) LIKE '%Joe''s%')
 => #<ActiveRecord::Relation []> 

Answer 3

Short and illegal answer:

ActiveRecord::Base.send(:sanitize_sql_like, text_to_escape)

Here's the output in the rails console (postgres)

irb(main):001:0> ActiveRecord::Base.send(:sanitize_sql_like, '%foo_bar%')
=> "\\%foo\\_bar\\%"

sanitize_sql_like is not a public method, so you should use it wisely. You are not using this method just because of the apostrophe, it also escapes characters like %_ that are wildcards in Postgres.

This answer could be useful in case you want to extend ActiveRecord::Base, so all your models can include this method: Rails extending ActiveRecord::Base

Answer 4

As previously stated, you can still use the sanitization methods if you use them within context of the model. Although adding a scope works (as shown in a previous answer), it's not strictly necessary. For example, you can add this to your model:

def self.where_ilike(search_terms)
  where('search_tokens ILIKE ?', "%#{sanitize_sql_like(search_terms)}%")
end