Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to execute arbitrary parameterized SQL in rails

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

like image 464
nohat Avatar asked Dec 29 '10 18:12

nohat


People also ask

How do I write a parameterized query in SQL?

Declare statements start with the keyword DECLARE , followed by the name of the parameter (starting with a question mark) followed by the type of the parameter and an optional default value. The default value must be a literal value, either STRING , NUMERIC , BOOLEAN , DATE , or TIME .

How does SQL parameterized query work?

Parameterized SQL queries allow you to place parameters in an SQL query instead of a constant value. A parameter takes a value only when the query is executed, which allows the query to be reused with different values and for different purposes.

Is parameterized query safe?

Parameterized queries are generally the safest and most efficient way to pass user defined values in a query, however not every database driver supports them.


1 Answers

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.

like image 74
Zubin Avatar answered Sep 22 '22 01:09

Zubin