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: stackoverflow.com/questions/4483049/…. — Brian Deterling
– Brian Deterling, Commented Dec 30, 2010 at 2:02
I second Brian's comment - the above link seems to be the same basic question. — Taryn East
– Taryn East, Commented Jun 12, 2011 at 12:57

Zubin · Accepted Answer · 2012-08-11 00:08:05Z

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, 2012 at 0:08

Zubin

9,8327 gold badges52 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shayne Over a year ago

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.

Collectives™ on Stack Overflow

How to execute arbitrary parameterized SQL in rails

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related