Escape parameters in a query object, using ActiveRecord?

Question

Given a query object (not an AR model)

class ComplexQuery
  QUERY = <<-SQL
    ...
  SQL

  def new(param1, param2)
    ...
  end

  def execute
    # format and interpolate parameters into QUERY
    # pass finished SQL to `execute` or `select_all`
  end
end

How can I conveniently escape all parameters?

I've succeeded with three techniques, but none are convenient.

1. Use the raw_connection which (for me) returns an instance of PG::Conn and call exec_params. I am not satisfied with this because exec_params requires a verbose set of arguments for specifying data types.
2. include ActiveRecord::Sanitization in my query object and use one of its convenient methods, like replace_named_bind_variables. I am not satisfied with this because replace_named_bind_variables is protected and I have to use send.
3. Write a module instead. For some reason, when I include ActiveRecord::Sanitization into a module, I'm able to use its protected methods. I'm not satisfied with this because I want to instantiate my query object sometimes without executing it, e.g. for testing.

Including ActiveRecord::Sanitization into a class feels like the best solution, but I must be doing something wrong because I should be able to use a protected method.

I'm looking for a solution that:

1. escapes multiple parameters
2. is intended to be used by consumers of ActiveRecord
3. infers the data type of a parameter and formats it accordingly

I was able to find some related questions

• Escaping values in Rails (similar to mysql_real_escape_string()) discusses escaping a single value, or suggest sending to private methods.
• Another question (ActiveRecord Select with Parameter Binding) shows how to use sanitize_sql_array in a subclass of ActiveRecord::Base, but my question here is about a separate query object, not an AR model.
• How to execute a raw update sql with dynamic binding in rails

Answer 1

The best way is probably to create a prepared statement using the raw Postgres driver. Unfortunately, ActiveRecord does not expose a way do do this generically. They may add it soon now that mysql2 supports prepared statements. But in the meantime, here's how to do it with the raw PG driver in rails.

http://deveiate.org/code/pg/PG/Connection.html#method-i-prepare

conn = ActiveRecord::Base.connection.raw_connection
conn.prepare('my_query', 'SELECT foo FROM bar WHERE baz=$1 OR baz=$2')
result = conn.exec_prepared('my_query', ['param1', 'param2'])

Note the use of $ as the symbol to indicate a positional parameter. The numbers correspond to the parameter's position in the array you pass to exec_prepared.

Answer 2

All the sanitization methods are already included in ActiveRecord::Base as class methods and supposed to be run as ActiveRecord::Base#sanitize_***.

The reason is that all sanitize_* functions are driver-specific and rely on connection object, which is apparently coming from ActiveRecord::ConnectionHandling.

The ActiveSupport::Concern contract forces Sanitization::ClassMethods module content to become class methods of the class/module that includes Sanitization, that is how they become available inside open ActiveRecord::Base derived class. They are not made public [IMHO] because they require the connection established, which is usually true for descendants and might be not a case for the ActiveRecord::Base itself.

Generally speaking, there is no unambiguous way to do what is asked without the connection (e. g. not in ActiveRecord::Base descendant.) Quoting for postgres differs from the one for mysql.

The summing up: including ActiveRecord::Sanitization into a class/module makes not much sense, since the whole functionality is already presented in ActiveRecord::Base. As soon, as one wants to use these quoting functionality without actual connection behind, she is probably to implement her own quoting functions, mapping to respective ConnectionAdapter#quote.

For the case when the connection is supposed to be established, I would go with prepending a wrapper module to ActiveRecord::Base that would wrap protected functions to public (in this module protected functions will be available.)

Hope this helps.

Answer 3

One things that might be of help to others who write raw sql in rails is ActiveRecord::Base.connection.quote method.

It prevents SQL injection by quoting strings and also cast values for use in raw sql such as converting nil (NilClass) to "NULL" and true(TrueClass) to "TRUE".

Using ActiveRecord::Base.connection.quote @justinhoward's answert can be re-written as

def quote_value(value)
  ActiveRecord::Base.connection.quote(value)
end

ActiveRecord::Base.connection('SELECT foo FROM bar WHERE baz=#{quote_value(param1)} OR baz=#{quote_value(param2)}')