On 19/08/2009, at 9:26 AM, MarkBennett wrote:
> I've been able to get a raw database connection using
> ActiveRecord::Base.connection which I can use to execute my SQL,
> however I'm not sure how to properly escape parameters I'm passing
> in to my queries if I want to prevent SQL injection attacks. What
> is a safe way to escape these values?
One of the unfortunate things about ActiveRecord is that a bunch of
the great functionality is only available if you use it in the context
of models. An example is SQL escaping, which is tucked away as private
methods in ActiveRecord::Base. This is fine if you're able to make do
with model-specific custom SQL, like this:
> # I understand that this query could be done with
> YourModel.all(:conditions => {}), but you get the idea
> YourModel.find_by_sql(['SELECT * FROM your_models WHERE foo = ? AND
> bar > ?', dangerous_foo, dangerous_bar])
But that's going to instantiate a YourModel for each record returned
by the query, which might not be what you're after for building
reports. To get around that you can use ActiveRecord::Base.connection
to execute raw commands, but you lose functionality like SQL
sanitisation. Mucking around with using instance_eval to call the
private methods you're after will work, but personally I think it's
more trouble than it's worth.
I'd recommend dropping the Sequel gem [1] into your app which vastly
simplifies the querying interface, because they've done the right
thing by separating Sequel::Database and Sequel::Model. You can do
something like this:
> DB.fetch('SELECT * FROM your_models WHERE foo = ? AND bar > ?',
> dangerous_foo, dangerous_bar).all
In both cases, ActiveRecord and Sequel will sanitise the dangerous_foo
and dangerous_bar variables and prevent SQL injection attacks.
Cheers,
Nathan
[1] http://sequel.rubyforge.org
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Ruby
or Rails Oceania" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rails-oceania?hl=en
-~----------~----~----~----~------~----~------~--~---
