Avoid Binding Variables in Rails #where

Question

If I have a polymorphic association, such as:

create Transmission < ApplicationRecord
  belongs_to :transmittable, polymorphic: true
end

create Contact < ApplicationRecord
  has_many :transmissions, as: :transmittable
end

create Product < ApplicationRecord
  has_many :transmissions, as: :transmittable
end

... then the following uses a bound variable for transmittable_type ...

Transmission.where(transmittable_type: 'Product')

When there's a large skew between the number of transmissions of Products and Contacts this can be undesirable, as the lack of an equivalent to Oracle's bind variable peeking can lead to bad estimates of result cardinality.

Question: Is there a way of avoiding the use of a bound variable in this case, other than with:

Transmission.where("transmittable_type = 'Product'")

... which I don't like because of the need in a complex query to identify the correct table alias for transmissions?

I've looked at plan_cache_mode as a database workaround for this but it didn't seem to offer a solution.

max · Accepted Answer · 2021-10-27 10:29:21Z

1

The ActiveRecord query interface parameterizes everything as a secure by default approach to avoid SQL injection and there really is no way to avoid it (to my knowledge at least) when passing a hash. You can use Arel to construct the query though:

class Transmission < ApplicationRecord
  def self.by_transmittable_type(type)
    where(arel_table[:transmittable_type].eq(type))
  end
end

Arel will simply sanitize the value passed to .eq and use it as part of the SQL string instead.

edited Oct 27, 2021 at 10:29

answered Oct 27, 2021 at 9:48

max

103k15 gold badges113 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Aldridge Over a year ago

Thanks - this is really a situation where no sanitisation is required, as the parameter is part of the codebase. Much of this would actually be imlemented in scopes. I guess what I'm really looking for here is a param passed to where that says "don't bind these query params", but I don't think that such a thing exists.

max Over a year ago

The sanitization is really just escaping quotes which is completely negible from a performance standpoint.

Collectives™ on Stack Overflow

Avoid Binding Variables in Rails #where

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related