skip to content

In Rails 8.1, what does Active Record do when params[:sort] reaches order or pluck, and how do you sort by a user-chosen column safely?

level: middleimportance: should knowfreq 40%

answer

  1. Dangerous query method message
  2. ActiveRecord::UnknownAttributeReference
  3. column-name matcher, not an allowlist
  4. Arel.sql switches the guard off
  5. hash form validates direction

basics

~10 s

order, reorder, pluck and in_order_of reject strings that do not look like column names, raising ActiveRecord::UnknownAttributeReference, but any column-shaped string passes, so map params[:sort] through an allowlist to symbols.

solid answer

~40 s

Active Record runs string arguments to `order`, `reorder`, `pluck` and `in_order_of` through the adapter's column-name matcher. A plain column, `table.column`, a function with at most one argument, and `ASC`/`DESC` pass; anything else, such as `price; DROP TABLE cars` or a subquery, raises `ActiveRecord::UnknownAttributeReference` with a "Dangerous query method" message that suggests `Arel.sql` for known-safe values. The guard is not an allowlist: `order(params[:sort])` still lets a visitor sort by `dealer_cost` or `length(vin)` and infer hidden values from the order. The hash form `order(column => direction)` additionally raises `ArgumentError` for a direction other than asc or desc. The safe pattern is a constant mapping from allowed strings to symbols, `SORTS.fetch(params[:sort], :created_at)`, plus a direction picked from two literals. Wrapping params in `Arel.sql` removes the guard and reopens injection.

code

ruby · 9 lines
ruby
class CarsController < ApplicationController
  SORTS = { "price" => :price, "year" => :year, "mileage" => :mileage }.freeze

  def index
    column    = SORTS.fetch(params[:sort], :created_at)
    direction = params[:dir] == "asc" ? :asc : :desc
    @cars = Car.where(make: params[:make]).order(column => direction)
  end
end

go deeper

for a junior

Know that a user-chosen sort must be mapped to a known column, never passed straight into order.

for a middle

Explain the column-name matcher, UnknownAttributeReference, the hash form's direction check, and why Arel.sql on params is a bug.

for a senior

Spot the column-disclosure risk the guard leaves open, review every Arel.sql call, and apply the allowlist pattern to order, pluck and dynamic select lists alike.

for a principal

Standardise sortable and filterable fields as declared metadata so new endpoints inherit an allowlist instead of re-inventing one.

## The guard Rails adds to a few methods Most Active Record methods trust SQL fragments completely. A handful that commonly receive user choices run their string arguments through a check first: - `order` and `reorder` - `pluck` - `in_order_of` - `upsert_all` / `insert_all`'s `on_duplicate:` and `returning:` options For `order`, `reorder` and `in_order_of`, each string is matched against the adapter's **column-name-with-order matcher**. Symbols and Arel nodes pass untouched. If a string fails, Active Record raises **`ActiveRecord::UnknownAttributeReference`**. Its message opens with "Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s)", quotes the rejected string (here `"price; DROP TABLE cars"`), warns against passing request parameters or model attributes, and says known-safe values can be wrapped in `Arel.sql()`. ## What the matcher accepts | String | Passes? | |---|---| | `"price"`, `"price DESC"`, `"cars.price"` | yes | | `"price DESC, year"` | yes, comma lists are allowed | | `"length(vin)"` | yes, a function with one argument | | `"price; DROP TABLE cars"` | no | | `"(SELECT 1)"`, `"CASE WHEN ... END"` | no | The abstract and PostgreSQL matchers also accept `NULLS FIRST` / `NULLS LAST`, and the SQLite and PostgreSQL matchers accept a `COLLATE` clause. ## Why the guard is not enough The matcher asks "does this look like a column?", not "is this a column the user may sort by?". On the dealership's inventory page, `order(params[:sort])` lets a visitor submit `dealer_cost` and watch where each car lands, revealing the cost order without ever seeing the column. A function such as `length(vin)` passes too. The guard stops arbitrary SQL; it does not express business rules. ## The safe pattern 1. Declare the allowed sorts as a frozen hash from the strings the UI sends to symbols or Arel attributes. 2. Look the param up with `fetch` and a default, so unknown values fall back instead of raising. 3. Pick the direction from two literals, not from the param. 4. Pass the result as a hash: `order(column => direction)`. The hash form adds its own check: a direction outside `asc`, `desc`, `ASC` and `DESC` (as strings or symbols) raises `ArgumentError`, "Direction ... is invalid". That is a backstop, not the design. ## Arel.sql is a promise, not a sanitizer `Arel.sql("...")` marks a string as trusted SQL, and the guard skips it. It exists for constant expressions such as `Arel.sql("price IS NULL, price")`. Writing `order(Arel.sql(params[:sort]))` to silence the exception turns the guard off and reintroduces injection. In review, an `Arel.sql` call with anything but a literal is a finding. ## Methods with no guard `where`, `select`, `group`, `having`, `joins` and `from` accept raw strings without the column check. `pluck` also checks its arguments, with the column-name matcher rather than the order one, so `pluck(params[:field])` raises on SQL but still exposes any column whose name a user can guess. The same allowlist pattern applies. ## Testing the allowlist A controller or request test pins the behaviour down: - `sort=price&dir=asc` orders by price ascending; - `sort=dealer_cost` falls back to the default order instead of sorting by cost; - `dir=sideways` falls back to descending rather than raising, because the direction is chosen from two literals. Those three cases document the contract better than a comment, and they fail loudly if someone later replaces the lookup with `params[:sort]`. ## How Brakeman sees it Brakeman 8.1 stops checking `order`, `reorder` and `pluck` for SQL injection in apps on Rails 6.1 or later, because Rails' own guard already rejects SQL fragments there. The consequence is that `order(params[:sort])` produces **no warning at all**, and the column-disclosure problem above is left to code review. The same scan still reports interpolation into `where`, `group`, `having`, `joins` and the other unguarded methods.

  • If order raises on SQL fragments, why is order(params[:sort]) still a finding?
    The matcher accepts any column-shaped string, so a visitor can sort by a column the page never displays, such as a cost or a token, and infer its values from the resulting order. Functions with one argument pass as well. Only an allowlist expresses which columns users may sort by.
  • When is Arel.sql the right tool in an order call?
    When the string is a constant written by a developer, for example `Arel.sql("price IS NULL, price")` to put unpriced cars last. It tells Active Record the SQL is trusted and skips the column check. Never pass request data or model attributes through it; that silences the guard and reopens injection.

  • Rails already blocks SQL in order, so params[:sort] is safe there
  • Wrap params[:sort] in Arel.sql to fix UnknownAttributeReference
  • Placeholders like order("? DESC", params[:sort]) bind the column
  • where has the same column-name guard as order
  • Arel.sql(params[:sort]) is fine once semicolons are stripped