CODEWITHSUNDEEP
mysqlruby-on-railsruby
user16012143
user16012143

Reputation: 131

Rails6 how to do `XXX.update_all` with mysql function

I would like to use update_all in order to update all records in a database.
Let me assume that the name of the attribute to be update is price.

I succeeded command A.

A

MyModel.update_all(price: "$500")

However, I would like to mysql function(e.g. IF, CONCAT, arithmetic...) in updating.
so I tried command B, but failed and the string value CONCAT('＄', 500) was stored.

B

MyModel.update_all(price: "CONCAT('＄', 500)")

I succeeded when I tried C, but I don't want use it because of the SQL injection risks.

C

MyModel.update_all("price = CONCAT('＄', 500)")

How can I do that without any risk of SQL injection attacks?
Here, I have to use sql function here for some reasons.

Thank you in advance.

Upvotes: 0

Views: 245

Answers (1)

Payal
Payal

Reputation: 394

You need to create methods corresponding to allowable sql functions that take the params as input. So if you want to provide "CONCAT(..,...)" then user should be able to call a method like this. You can extent it to check type of arguments are allowed and add checks there as well.

def allowed_functions(func_name, all_args_here)
    case func_name
      when 'concat' then "CONCAT(#{all_args_here})"
    end
end

Upvotes: 1

Related Questions