Rails 4 - Prevent SQL injection using LIKE operator in SQL query

Question

I'd like to use the following query for my Rails 4 application but am concerned about SQL injection attacks:

@persons = People.where("persons.name LIKE ?", "%#{params[:search]}%")

Can somebody show me the safe way to write the above statement? I've tried the following but am not sure if it is SQL-injection-proof:

search = "%" + params[:search] + "%"
@persons = People.where("persons.name LIKE ?", search)

Thanks!

score 12 · Accepted Answer · answered Aug 21 '14 at 01:23

12

Your examples are fine, as zishe said.

Whenever you use question marks to a method and pass another parameters as the search query, it sanitizes your query string.

It is dangerous when you manually do string concatenations to create your query, for example:

Project.where("name = '#{params[:name]}'")

answered Aug 21 '14 at 01:23

Migore

Thanks, @Migore! I appreciate the additional resource as well as the explanation as to why my examples are SQL injection-proof! – Vee Aug 21 '14 at 02:15

score 9 · Answer 2 · edited May 23 '17 at 12:08

9

Both your statements will be safe. Also you can write it like this:

@persons = People.where("persons.name LIKE concat('%', ?, '%')", params[:search])

edited May 23 '17 at 12:08

Community

answered Aug 21 '14 at 01:00

zishe

1

No, the pattern must be a string. You don't have the SQL string in quotes. But you must not put the placeholder inside a SQL string. This would work: `"persons.name LIKE CONCAT('%', ?, '%')"` – Bill Karwin Aug 21 '14 at 01:10
@zishe - thanks for answering my question as well as providing a more concise piece of code. I'm accepting Migore's response since he provided some additional information as to why my examples were not vulnerable to SQL injection. Thanks! – Vee Aug 21 '14 at 02:14

2 Answers2