Rails: rake task change column values in database

Question

I have a column name "password" on my db some password has exclamation point(!) character how can i change the exclamation mark (!) character with dollar sign ($) using rake task?

This is my code so far:

namespace :update_db do
  desc "Change ! Character to $"
    User.where(password: '!').each do |t|
      t.password 
    end
  end
end

if not you cannot fire a query over password column to change the `!` to `$` in password because the password is encrypted and saved — Deepak Mahakale, Apr 21 '17 at 07:10
I think also better to add migration in your app and validate field in future instead of creating a rake task. This will work in all environments and you'll sure that you haven't `!` anywhere. — Ilya, Apr 21 '17 at 07:17

Andrey Deineko · Accepted Answer · 2017-04-21T07:18:13.387

namespace :update_db do
  desc "Change ! Character to $"
    User.where('password LIKE ?', "%!%").find_each do |t|
      t.update(password: t.password.gsub('!', '$'))
    end
  end
end

The above solution will fire one query for condition and one query per each updated user.

If you have a lot of users, you can go with single query for update part (using REGEXP_REPLACE):

User.update_all("password = REGEXP_REPLACE(password, '!', '&', 'g')")

P.S. Storing passwords as plain text is not the best idea. In fact, it's very bad idea.

Rails: rake task change column values in database

1 Answers1