How to use wildcards in an active record where clause while protecting against sql injection

Question

From the rails active record querying guide on sql injection

This code is highly preferable:

Client.where("orders_count = ?", params[:orders])

to this code:

Client.where("orders_count = #{params[:orders]}")

My problem is I want to use a LIKE clause with a wildcard. My old query looks like this -

User.where("first_name LIKE '%#{first_name}%'")  

Which is vulnerable to sql injection, but if I do this:

User.where("first_name LIKE '%?%'", first_name) 

Then the resulting sql looks like:

SELECT "users".* FROM "users"  WHERE (first_name LIKE '%'michael'%') 

which is invalid due to the extra single quotes.

What is the best way to use wildcards and a LIKE clause but also protect against sql injection attacks?

Answer 1

You should modify your query like this

User.where("first_name LIKE (?)", "%#{first_name}%") 

Answer 2

Update: Rails has improved this some.

Arel allows you to build queries with a simple DSL, and as a bonus, it's included in ActiveRecord. Here is an example for your use case.

User.where(User.arel_table[:first_name].matches("%#{first_name}%")) 

This will do a case-insensitive search using ILIKE instead of LIKE.

Also want to note that Arel is internal to Rails and purposefully not exposed by the framework.