47

I have to sanitize a part of sql query. I can do something like this:

class << ActiveRecord::Base
  public :sanitize_sql
end

str = ActiveRecord::Base.sanitize_sql(["AND column1 = ?", "two's"], '')

But it is not safe because I expose protected method. What is a better way to do it?

Timur Shtatland
  • 12,024
  • 2
  • 30
  • 47
dimus
  • 8,712
  • 10
  • 45
  • 56
  • 3
    Can you give us a little more context? `sanitize_sql` and friends are often called inside AR::Base-derived classes, without needing to alter visibility – pilcrow Jun 09 '10 at 19:59
  • That is a good and valid point. I just cringe when I use someone's private or protected methods. – dimus Jun 11 '10 at 11:55

5 Answers5

56

You can just use: 

ActiveRecord::Base::sanitize_sql(string)
Jason Swett
  • 43,526
  • 67
  • 220
  • 351
HashDog Team
  • 657
  • 5
  • 4
  • 13
    This delegates to `ActiveRecord::Base.connection.quote` (at least in Rails 4) – Daniel Rikowski May 30 '15 at 15:29
  • 2
    Deprecated. New sanitization methods [here](https://api.rubyonrails.org/classes/ActiveRecord/Sanitization/ClassMethods.html). See Bryan's [answer](https://stackoverflow.com/a/35608331/165673) – Yarin Dec 27 '18 at 15:44
  • 1
    I updated this answer from the deprecated version (`sanitize`) to the current working version as of Rails 6 (`sanitize_sql`). If someone wants a full explanation of all the sanitization methods, I'd suggest [the docs](https://api.rubyonrails.org/classes/ActiveRecord/Sanitization/ClassMethods.html). – Jason Swett Apr 08 '20 at 11:41
  • I get an error message: private method `sanitize_sql' called for ActiveRecord::Base:Class – PHZ.fi-Pharazon Dec 24 '22 at 09:41
18

ActiveRecord::Base.connection.quote does the trick in Rails 3.x

Ulysse BN
  • 10,116
  • 7
  • 54
  • 82
dimus
  • 8,712
  • 10
  • 45
  • 56
16

This question does not specify that the answer has to come from ActiveRecord nor does it specify for which version of Rails it should be. For that reason (and because it is one of the top and few) answers on how to sanitize parameters in Rails...

Here a solution that works with Rails 4:

In ActiveRecord::Sanitization::ClassMethods you have sanitize_sql_for_conditions and its two other aliases:  sanitize_conditions and sanitize_sql. The three do literally the exact same thing.

sanitize_sql_for_conditions

Accepts an array, hash, or string of SQL conditions and sanitizes them into a valid SQL fragment for a WHERE clause.

Also in ActiveRecord you have

sanitize_sql_for_assignment which

Accepts an array, hash, or string of SQL conditions and sanitizes them into a valid SQL fragment for a SET clause.

  • The methods above are included in ActiveRecord::Base by default and therefore are included in any ActiveRecord model.

See docs

Also, however, in ActionController you have ActionController::Parameters which allows you to

choose which attributes should be whitelisted for mass updating and thus prevent accidentally exposing that which shouldn't be exposed. Provides two methods for this purpose: require and permit.

    

params = ActionController::Parameters.new(user: { name: 'Bryan', age: 21 })
req  = params.require(:user) # will throw exception if user not present
opt  = params.permit(:name)  # name parameter is optional, returns nil if not present
user = params.require(:user).permit(:name, :age) # user hash is required while `name` and `age` keys are optional

The "Parameters magic" is called Strong Parameters (docs here) and you can use that to sanitize parameters in a controller before sending it to a model.

  • The methods above are included by default in ActionController::Base and therefore are included in any Rails controller.

I hope that helps anyone, if only to learn and demystify Rails! :)

Yarin
  • 173,523
  • 149
  • 402
  • 512
Bryan Dimas
  • 1,389
  • 13
  • 18
  • 1
    Does `require` and `permit` sanitize parameters against SQL injection, or just validate their presence? – Matt Aug 01 '16 at 15:18
  • 3
    it does zero sanitizing, just validates presence – Jimmy Aug 02 '16 at 14:22
  • 5
    @Matt: Jimmy is correct, `require` and `permit` don't do any sanitizing by themselves. But `ActionController::Parameters.new` does the sanitizing, so all of your controllers should already be sanitizing all parameters. I'll update my answer later when I have time because I also found this very cool gem called rails-html-sanitizer https://github.com/rails/rails-html-sanitizer – Bryan Dimas Aug 09 '16 at 21:52
  • 2
    Thanks Bryan. I'm in a situation where I'm sending a large amount of JSON to my controller, and am dealing with 3s+ response times. I was able to decrease these to 2s by eliminating redundancy in my JSON, and further get it down to below 500ms by not instantiating an ActiveRecord object. Just wanted to be sure that I was exposing a security risk! – Matt Aug 11 '16 at 11:08
  • Note that the sanitization methods above are protected class methods, so you'll either need to call them from within your AR class or expose them via a public class method in your own AR class. – Yarin Dec 27 '18 at 16:03
  • None of the current crop of sanitization methods correctly handle nil attributes as WHERE conditions. See [here](https://stackoverflow.com/a/53948845/165673) – Yarin Dec 27 '18 at 17:39
10

As of rails 5 the recomended way is to use: ActiveRecord::Base.connection.quote(string)

as stated here: https://github.com/rails/rails/issues/28947

ActiveRecord::Base::sanitize(string) is deprecated

Lucian Tarna
  • 1,806
  • 4
  • 25
  • 48
  • Not the recommended. Use the sanitation apis in Bryan's [answer](https://stackoverflow.com/a/35608331/165673) instead. – Yarin Dec 27 '18 at 16:18
1

Note that when it comes to sanitizing SQL WHERE conditions, the best solution was sanitize_sql_hash_for_conditions, because it correctly handled NULL conditions (e.g. would generate IS NULL instead of = NULL if a nil attribute was passed).

For some reason, it was deprecated in Rails 5. So I rolled a future-proofed version, see here: https://stackoverflow.com/a/53948665/165673

Yarin
  • 173,523
  • 149
  • 402
  • 512