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

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 2

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.