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?
answer
- Dangerous query method message
- ActiveRecord::UnknownAttributeReference
- column-name matcher, not an allowlist
- Arel.sql switches the guard off
- hash form validates direction
basics
~10 sorder, 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 sActive 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 linesclass 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
endgo deeper
Know that a user-chosen sort must be mapped to a known column, never passed straight into order.
Explain the column-name matcher, UnknownAttributeReference, the hash form's direction check, and why Arel.sql on params is a bug.
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.
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