In a Rails chess-club app, why does Member.order(params[:sort]) raise ActiveRecord::UnknownAttributeReference for some inputs, and how should the user-chosen sort be built?
answer
- a shape check, not an allowlist
- column_name_with_order_matcher per adapter
- Arel.sql skips the check
- hash order validates the direction
- order, pluck, pick, in_order_of guarded
basics
~20 sActive Record checks string arguments to order and pluck against a column-shaped pattern and raises UnknownAttributeReference for anything else. Passing the check is not safety, and Arel.sql skips it; map params[:sort] through an allowlist into order(column => direction).
solid answer
~40 s`order`, `reorder`, `in_order_of`, `pluck` and `pick` run string arguments through `disallow_raw_sql!`, which accepts symbols, Arel nodes and strings matching the adapter's column pattern: a column or `table.column`, a function of at most one column, `ASC`/`DESC`, comma lists. Anything else, such as `rating - peak_rating DESC` or a `CASE`, raises `ActiveRecord::UnknownAttributeReference`. The check looks at shape, not at your columns, so `password_digest DESC` passes, and an unknown name fails later in the database. `Arel.sql` marks a string as trusted and skips the check, so `Arel.sql(params[:sort])` is the wrong fix. Map the parameter through a hash of allowed columns and pass `order(column => direction)`; Active Record quotes the column and raises `ArgumentError` for an invalid direction. Keep `Arel.sql` for expressions you wrote yourself, with `?` binds for any value.
code
ruby · 8 linesMember.order("rating - peak_rating DESC")
# raises ActiveRecord::UnknownAttributeReference
Member.order(Arel.sql("rating - peak_rating DESC"))
# ORDER BY rating - peak_rating DESC (string written in code)
Member.order(Arel.sql("CASE WHEN title = ? THEN 0 ELSE 1 END", params[:title]))
# the expression is ours; only the value comes from the requestgo deeper
Know that order and pluck reject raw SQL strings with UnknownAttributeReference, and that a symbol or hash order is the normal form.
Explain what the column-shape pattern accepts, what Arel.sql does, and how a hash order quotes the column and validates the direction.
Show why the guard is a shape check, not an allowlist, which methods it skips, and build the allowlist-to-hash sort with a safe default.
Turn it into a review rule: Arel.sql only around literals written in code, a grep target for any Arel.sql touching params, and allowlists for every identifier.
## The guard on order and pluck Some Active Record methods take **identifiers or expressions** rather than values: `order`, `reorder`, `in_order_of`, `pluck` and `pick` among the query methods, and `insert_all`'s `returning:` and `on_duplicate:` among the writes. A string passed there goes into the SQL as written, so Rails runs it through a guard, `disallow_raw_sql!`. An argument is allowed when it is: - a **Symbol**, such as `:rating`; - an **Arel node**, including anything wrapped in `Arel.sql`; - a **string matching the adapter's pattern**: `column_name_matcher` for `pluck`, `column_name_with_order_matcher` for ordering. The ordering pattern accepts a column or `table.column`, a function call with zero or one column argument (`LOWER(name)`), an optional `ASC` or `DESC`, comma-separated lists of those and, depending on the adapter, a `COLLATE` clause or `NULLS FIRST`/`NULLS LAST` (PostgreSQL). Anything else raises **`ActiveRecord::UnknownAttributeReference`** with the message "Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s)". | Argument | Result | |---|---| | `order(:rating)` / `order(rating: :desc)` | allowed | | `order("rating DESC")` | allowed | | `order("LOWER(name)")` | allowed | | `order("rating - peak_rating DESC")` | raises `UnknownAttributeReference` | | `order(Arel.sql("rating - peak_rating DESC"))` | allowed, trusted as written | | `pluck("rating * 2")` | raises `UnknownAttributeReference` | ## Why passing the guard is not enough The guard rejects most **injected SQL**, because injected SQL rarely looks like a bare column name. It is still a **shape check, not an allowlist**: 1. `order(params[:sort])` with `password_digest DESC` passes: the string looks like a column, and the page now sorts by a column it never offered, which leaks information through ordering. 2. A name that is not a column also passes the guard and fails in the database with `ActiveRecord::StatementInvalid`, a 500 instead of a clean fallback. 3. The guard covers only those methods. Strings passed to `select`, `where`, `group`, `having`, `joins` and `from` are not checked at all. 4. `Arel.sql` turns any string into a trusted node, so `order(Arel.sql(params[:sort]))` silences the error and sends the caller's text straight into ORDER BY. ## Building a user-chosen sort Keep **identifiers in code** and use the parameter only to choose among them: ```ruby class MembersController < ApplicationController SORTS = { "rating" => :rating, "name" => :name, "joined" => :joined_on }.freeze def index column = SORTS.fetch(params[:sort], :rating) direction = params[:dir] == "asc" ? :asc : :desc @members = Member.where(club: current_club).order(column => direction) end end ``` - The **hash form** quotes the column with its table name, so `order(rating: :desc)` becomes `ORDER BY "members"."rating" DESC`. - Hash directions are validated: anything outside `asc`, `desc`, `ASC`, `DESC` (as strings or symbols) raises `ArgumentError` ("Direction ... is invalid"). - The fallback (`fetch` with a default) turns an unknown parameter into the default sort instead of an error. ## Where Arel.sql belongs `Arel.sql(string)` wraps a string as an `Arel::Nodes::SqlLiteral`, meaning "I wrote this and vouch for it". Use it for **expressions you wrote**, never for input: - `Member.order(Arel.sql("rating - peak_rating DESC"))` for a computed sort. - `Member.pluck(:name, Arel.sql("rating - peak_rating"))` for a computed column. - When the expression needs a **value**, give it a placeholder. `Arel.sql` also takes binds itself, `Arel.sql("CASE WHEN title = ? THEN 0 ELSE 1 END", "GM")`, and `order` also accepts the array `[Arel.sql("CASE WHEN title = ? THEN 0 ELSE 1 END"), "GM"]`, which `sanitize_sql_for_order` binds. The array form only works when the first element is already `Arel.sql`: a plain `CASE` string fails the ordering pattern before any binding happens. For ordering by a fixed list of values, `in_order_of` builds the `CASE` for you. ## pluck and pick follow the same rule `pluck` and `pick` use the slightly wider `column_name_matcher`, which also allows an alias: `Member.pluck("rating AS r")` passes, while `Member.pluck("rating * 2")` raises. A computed column therefore needs `Arel.sql` there too, written in code: `Member.where(club: club).pluck(:name, Arel.sql("rating - peak_rating"))` returns pairs of name and rating change without loading any models. A cheap regression test pins the behaviour: request the index with `sort=password_digest` and `dir=sideways` and assert that the page renders with the default sort, which proves the allowlist, not the guard, is doing the work. ## What interviewers listen for - That the error is a **feature**, and the fix is never to wrap the parameter in `Arel.sql`. - That an allowlist maps input to identifiers, while placeholders carry values; the two never swap roles. - That the guard's coverage is partial, so a reviewer still reads every `select`, `group` and `joins` string.
- Why does Member.order(["CASE WHEN title = ? THEN 0 ELSE 1 END", "GM"]) still raise, while the same array with Arel.sql around the string works?`sanitize_sql_for_order` checks the first element against the ordering pattern before it binds anything, and a `CASE` expression does not look like a column. Wrapping it in `Arel.sql` makes it a trusted node, after which the `?` is replaced with the quoted value.
- Is Member.select(params[:fields]) protected by the same guard?No. `select` passes strings through without `disallow_raw_sql!`, as do `where`, `group`, `having`, `joins` and `from`. Any user-chosen field list must be mapped through an allowlist in code before it reaches `select`.
- Wrapping params[:sort] in Arel.sql is the right fix for UnknownAttributeReference.
- If order accepts the string, the sort is safe to expose to users.
- order checks the string against the model's actual column names.
- A ? placeholder can carry the user's chosen column into ORDER BY.
- select and group strings get the same raw-SQL guard as order.
- An invalid hash direction silently falls back to ascending order.