Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Rails - How to properly escape values in a update_all query

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])
like image 242
gkpo Avatar asked Feb 19 '15 11:02

gkpo


1 Answers

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", "[email protected]"]
  SQL (0.7ms)  UPDATE "people" SET name = 'foo', email = '[email protected]' WHERE "people"."email" IS NULL
=> 3
[4] pry(main)> Person.pluck(:name, :email)
   (0.3ms)  SELECT "people"."name", "people"."email" FROM "people"
=> [["foo", "[email protected]"], ["foo", "[email protected]"], ["foo", "[email protected]"]]
[5] pry(main)>
like image 161
Arup Rakshit Avatar answered Oct 25 '22 20:10

Arup Rakshit