How to execute arbitrary parameterized SQL in rails

Question

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...

Check out this question also: http://stackoverflow.com/questions/4483049/how-to-execute-a-raw-update-sql-with-dynamic-binding-in-rails/4484549#4484549. — Brian Deterling, Dec 30 '10 at 02:02
I second Brian's comment - the above link seems to be the same basic question. — Taryn East, Jun 12 '11 at 12:57

score 5 · Answer 1 · answered Aug 11 '12 at 00:08

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 Aug 11 '12 at 00:08

Zubin

9,422
7
48
52

2

This isnt answering the question, as it uses string interpolation and is thus vunerable to SQL injections. Yes I am aware its using "sanitized" sql array, but theres a long history of these things being a poor substitute for actual security. What is being asked for is access to parameterized queries like all sane dbms systems have had since the 1980s. – Shayne Jul 13 '16 at 08:42

How to execute arbitrary parameterized SQL in rails

1 Answers1

Linked