Rails - How to properly escape values in a update_all query

escaping, ruby-on-rails, sql, sql-injection

Solution

Write as :

Task.where(...)
    .update_all(['position = ?, parent_id = ?', position - X, Y])

Read `update_all`.

conditions - An SQL fragment like "administrator = 1" or [ "user_name = ?", username ]. See conditions in the intro for more info.

Demo:

Loading development environment (Rails 4.2.0)
[1] pry(main)> Person.pluck(:name, :email)
   (0.4ms)  SELECT "people"."name", "people"."email" FROM "people"
=> [["xxxx", nil], ["xxxx", nil], ["xxxx", nil]]
[3] pry(main)> Person.where(email: nil).update_all ["name = ?, email = ?", "foo", "test@test.com"]
  SQL (0.7ms)  UPDATE "people" SET name = 'foo', email = 'test@test.com' WHERE "people"."email" IS NULL
=> 3
[4] pry(main)> Person.pluck(:name, :email)
   (0.3ms)  SELECT "people"."name", "people"."email" FROM "people"
=> [["foo", "test@test.com"], ["foo", "test@test.com"], ["foo", "test@test.com"]]
[5] pry(main)>

Problem

So I have an update_all query that looks like this: ``` Task.where(...).update_all('position = position - X, parent_id = Y') ``` I want to replace X and Y with integer values. And I'd like to do it safely: "the rails way". Any idea of how I can achieve this ? EDIT: There is no position variable in my rails controller. If X = 1, The final query should literally contain "position=position-1". Also, The update_all documentation specifies that this method only takes one argument: A string, array, or hash representing the SET part of an SQL statement. EDIT 2: Alright, I got it working by slightly tweaking Arup Rakshit solution. Here's the final working solution: ``` Task.update_all(['position = position - ?, parent_id = ?', X, Y]) ```

Original source