rod rod - 6 months ago 27
MySQL Question

Safe/Better SQL Query with Ruby on Rails

I wrote two queries doing the same thing, but result on console log is different. Which is better? Are both safe from

SQL
injection?

Order.where(user_id: current_user.id).or(Order.where(seller_id: current_user.id))


Log:

Order Load (0.3ms)
SELECT "orders".*
FROM "orders"
WHERE ("orders"."user_id" = ? OR "orders"."seller_id" = ?)
ORDER BY "orders"."created_at" DESC
[["user_id", 1], ["seller_id", 1]]





Order.where("user_id = ? OR seller_id = ?", current_user.id, current_user.id)


Log:

Order Load (0.3ms)
SELECT "orders".*
FROM "orders"
WHERE (user_id = 1 OR seller_id = 1)
ORDER BY "orders"."created_at" DESC


Thanks

Answer Source

Yes, they are both safe regarding the SQL injection for several reasons:

  1. the variables passed to it is the result of calling id on a User object and will always be either nil or an integer and will never be a string like 1;DELETE FROM users.
  2. the variables are given as arguments to the where method, which makes Rails parameterise or sanitize the input for you. If you did some direct string interpolation like User.where("name = '#{params[:search]}'") and the end-user submitted bla';delete from users' then yeah, SQL injection.

The best option for you is to use the .or version which does not use any hard-coded SQL code. It just pass ruby code to the DB adapter you defined and ask it to translate it to SQL.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download