Alex Rivera | Logout

How to execute arbitrary parameterized SQL in rails

Asked 2010-12-29T18:20:46.887
13

For performance reasons, I need to write a new method in my Rails model that executes some arbitrary SQL:

UPDATE table
   SET col1 = ? AND col2 = ?
   WHERE id = ?

I understand I can use ActiveRecord::Base.connection.execute or ActiveRecord::Base.connection.update with a string of SQL to get the results I need, but what is the proper procedure for substituting the parameter placeholders (?) with the actual parameter values? Is there a Rails method for interpolating parameters into a SQL statement, or should it just be done by manual interpolation? The latter seems unsafe...

sql ruby-on-rails sql-injection
Edit
Report

1 Answer

5

You could also do this:

updates = ActiveRecord::Base.send(:sanitize_sql_array, ["name = ? and category = ?", name, category])
ActiveRecord::Base.connection.execute("update table set #{updates} where id = #{id.to_s.to_i}")

to_s is being called on id before to_i in case it's nil.

answered 2012-08-11T00:08:05.397

Your Answer