Secure ActiveRecord Request

Question

I am trying to write a LIKE request.

I read that pure string queries are unsafe, however I could not find any documentation that would explain how to write a secure LIKE Hash Query query.

Is it possible? Do I have to manually defend against SQL Injection?

Answer 1

To verify that the query string is cleared correctly, use array or hash syntax to describe your conditions:

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

or

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

If it is possible that query may contain the % character, then you need to sanitize the query with sanitize_sql_like first:

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

Answer 2

Using Arel, you can execute this safe and portable request:

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

Answer 3

For PostgreSQL will be

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

Answer 4

You can do

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