Rails - Is this query open for SQL injection?

Question

I'm still learning how to write good queries with ActiveRecord. I am curious if this query will be subject to sql injection due to the way I use the date field in the query.

Can anyone point out any obvious errors or any more efficient ways to write this query?

@arrangements_for_month = Arrangement.joins(:timeslot). where("timeslots.timeslot BETWEEN '#{month}' AND '#{month.end_of_month}'", params[:id]). order('location_id') 

Answer 1

You just have to use your preferred way to enable options for security. Check out this guide :

Building your own conditions as blank lines can leave you vulnerable to SQL injection exploits. For example, Client.where("first_name LIKE '%#{params[:first_name]}%'") not secure. See the next section for a preferred way to handle conditions using an array.

Try:

 @arrangements_for_month = Arrangement.joins(:timeslot) .where("timeslots.timeslot BETWEEN ? AND ?", month, month.end_of_month) .order('location_id') 

And only heads-up, if you like, is there an alternative way to determine the conditions of a range like this using ruby ​​ranges, as described in this section of the related guide:

 Client.where(:created_at => (Time.now.midnight - 1.day)..Time.now.midnight) 

So, without knowing anything about your code, you can do something like this:

 @arrangements_for_month = Arrangement.joins(:timeslot) .where("timeslots.timeslot" => month .. month.end_of_month) .order('location_id') 

Answer 2

Yes it is. Each time you insert user input into the query string, it is vulnerable. If month is:

 5' AND '8'; DROP TABLE timeslots;-- 

You may have serious problems. Not to mention the drop database, etc.

I did not reproduce this particular request, but something similar [I had to add) to my request due to the use of the act_as_paranoid plugin]:

 SomeModel.pluck(:id) => [1, 2, 4, 3, 5, 6] abc = 'a\');delete from some_models where id=6;--' User.where("name = '#{abc}'") => [] SomeModel.pluck(:id) => [1, 2, 4, 3, 5] # please note that record with id 6 was deleted! 

The reason the attack is possible is because I could provide ' and -- (which starts to comment). When you use the proposed method, that is, use .where ("name =?", "My_name"), then the attack will be impossible. Check this:

 abc = 'a\');delete from some_models where id=5;--' User.where("name = ?", abc) => [] SomeModel.pluck(:id) => [1, 2, 4, 3, 5] # this time record with id 5 was not deleted 

This is the first request:

  User Load (1.5ms) SELECT "users".* FROM "users" WHERE ("users"."deleted_at" IS NULL) AND (name = 'a');delete from some_models where id=6;--') 

This is the second

  User Load (1.0ms) SELECT "users".* FROM "users" WHERE ("users"."deleted_at" IS NULL) AND (name = 'a'');delete from some_models where id=5;--') 

Note the extra ' in the second - query(name = 'a'')