Menu
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?
400
Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
Wildcards are special characters that can stand in for unknown characters in a text value and are handy for locating multiple items with similar, but not identical data. Wildcards can also help with getting data based on a specified pattern match. For example, finding everyone named John on Park Street.
To broaden the selections of a structured query language (SQL-SELECT) statement, two wildcard characters, the percent sign (%) and the underscore (_), can be used. The percent sign is analogous to the asterisk (*) wildcard character used with MS-DOS.
You should modify your query like this
User.where("first_name LIKE (?)", "%#{first_name}%") 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.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
