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.
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.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
. 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:
I was able to find some related questions
send
ing to private methods.sanitize_sql_array
in a subclass of ActiveRecord::Base, but my question here is about a separate query object, not an AR model.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
.
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.
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)}')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With