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!

Migore · Accepted Answer · 2014-08-21 01:23:37Z

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]}'")

Click here for more information

answered Aug 21, 2014 at 1:23

Migore

1,4973 gold badges20 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vee Over a year ago

Thanks, @Migore! I appreciate the additional resource as well as the explanation as to why my examples are SQL injection-proof!

Community · Accepted Answer · 2017-05-23 12:08:29Z

9

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

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

Simlilar qestion

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Aug 21, 2014 at 1:00

Alexander Kireyev

10.8k13 gold badges68 silver badges106 bronze badges

2 Comments

Bill Karwin Over a year ago

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('%', ?, '%')"

Vee Over a year ago

@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!

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related