0

I'm struggling to sanitize a raw SQL query in which the WHERE conditions may either have a value or be NULL. I was hoping to use Active Record's built-in sanitizers...

(NOTE: I'll be using a simplified query for demo purposes- our real one is a complex UNION across different model types that would be hard to do with the AR query interface)

Try 1:

raw_query = "SELECT * FROM folders WHERE user_id = ? AND parent_id = ?"
sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, [raw_query, current_user.id, params[:parent_id]])
results = ActiveRecord::Base.connection.execute(sanitized_query);

But if params[:parent_id] is nil, then sanitized_query ends up as SELECT * FROM folders WHERE user_id = 1 AND parent_id = NULL Which isn't valid, as parent_id = NULL should be parent_id IS NULL

Try 2:

I then found the sanitize_sql_hash method, which seemed perfect for building the condition:

sanitized_conditions = ActiveRecord::Base.send(:sanitize_sql_hash, {user_id: current_user.id, parent_id: params[:parent_id]})
sanitized_query = "SELECT * FROM folders WHERE #{sanitized_conditions}"
results = ActiveRecord::Base.connection.execute(sanitized_query);

But the first line fails with:

NoMethodError: undefined method `abstract_class?' for Object:Class

The method is also listed as deprecated and will be removed in Rails 5, but it's exactly what I'm looking for. Is there another way to generate a safe WHERE condition from a hash of values?

Community
  • 1
  • 1
Yarin
  • 173,523
  • 149
  • 402
  • 512
  • somewhat related: http://stackoverflow.com/questions/7145645/rails-how-to-sanitize-sql-in-find-by-sql – Yarin Jan 18 '15 at 00:44

2 Answers2

1

It looks like the error is being thrown in the reset_table_name method at https://github.com/rails/rails/blob/7bb620869725ad6de603f6a5393ee17df13aa96c/activerecord/lib/active_record/model_schema.rb#L160 so maybe this method is not designed to work on the ActiveRecord::Base class.

Assuming you have got a model class for Folder this should work:

Folder.send(:sanitize_sql_hash, {user_id: current_user.id, parent_id: params[:parent_id]})

In a quick test I got something like:

'"folders"."user_id" = 123 AND "folders"."parent_id" IS NULL'
Steve
  • 15,606
  • 3
  • 44
  • 39
  • Thanks, this *does* work for now- annoying that `sanitize_sql_hash` is being removed in Rails 5 though, meaning we can't use this as a permanent solution... – Yarin Jan 17 '15 at 17:48
  • 1
    Yes upgrading to Rails 5 will be interesting. Maybe there will be a better option then, otherwise the only option I can think of is use the `to_sql` method to generate a whole query and then extract the where clause, something like `Folder.where(...).to_sql.split('WHERE ')[1]` – Steve Jan 17 '15 at 18:10
  • @Steve- yeah, extracting the where clause from the generated query is actually not a bad idea- thanks for the help! – Yarin Jan 17 '15 at 18:13
  • I created a monkey patch for the where clause extraction: [answer](https://stackoverflow.com/a/53948665/165673) – Yarin Dec 27 '18 at 17:26
1

I created a Rails 5-safe version of the deprecated but much needed sanitize_sql_hash_for_conditions, using @Steve's trick:

class ActiveRecord::Base 

  # Sanitizes a hash of attribute/value pairs into SQL conditions for a WHERE clause.
  #
  # (This is an alternative to sanitize_sql_hash_for_conditions, which was deprecated in Rails 5.
  # SEE: https://api.rubyonrails.org/v4.2/classes/ActiveRecord/Sanitization/ClassMethods.html#method-i-sanitize_sql_hash_for_conditions)
  # 
  # This is needed because the latest SQL sanitization methods cannot handle conditions where an attribute is nil.

  def self.sanitize_sql_hash_for_conditions_alt(attrs)
    self.where(attrs).to_sql.split('WHERE ')[1]
  end

end

Call it on whatever AR model class represents the table you're targeting (not on ActiveRecord::Base directly).

Folder.sanitize_sql_hash_for_conditions_alt({user_id: 1, parent_id: nil})
=> "\"folders\".\"user_id\" = 1 AND \"folders\".\"parent_id\" IS NULL"

Yarin
  • 173,523
  • 149
  • 402
  • 512