In an Active Record query, why is Car.where("make = '#{params[:make]}'") unsafe, and which forms of where pass values safely?
answer
- a lone string is raw SQL
- Ruby interpolates before Rails sees it
- hash conditions quote every value
- ? placeholders and :named binds
- binds cover values, not identifiers
basics
~20 sRuby interpolates params[:make] into the string before Active Record sees it, and where treats a lone string as raw SQL, so a quote in the input rewrites the query; hash conditions, ? placeholders and :named binds quote the value instead.
solid answer
~50 s`where` with a single string argument wraps it as a raw SQL literal and inserts it into the WHERE clause untouched. With `"make = '#{params[:make]}'"`, Ruby has already built the SQL text, so input such as `x' OR '1'='1` ends the literal and changes the query's meaning. The safe forms hand the value to Active Record separately: hash conditions like `where(make: params[:make])`, which also turn arrays into `IN` and ranges into `BETWEEN`; positional placeholders, `where("price < ?", params[:max])`; and named binds, `where("make = :make", make: params[:make])`. In each, the adapter quotes or binds the value, so it can only ever be data. The same rule applies to every Active Record method that accepts an SQL fragment, such as `find_by`, `having`, `joins` and `find_by_sql`. Placeholders bind values only; column names and sort directions need a different defence.
code
ruby · 8 lines# Unsafe: the user's text becomes part of the SQL
Car.where("make = '#{params[:make]}'")
# Safe: the value is bound or quoted by Active Record
Car.where(make: params[:make])
Car.where("price <= ?", params[:max_price])
Car.where("year BETWEEN :from AND :to", from: params[:from], to: params[:to])
Car.where(make: params[:make]).where("mileage < ?", params[:max_miles])go deeper
Recognise #{} inside a string passed to where as SQL injection, and rewrite it as a hash condition or a ? placeholder.
Explain why interpolation runs before Active Record sees the string, list the four where shapes, and note which other query methods accept raw fragments.
Review a codebase for raw fragments systematically, including select, joins, find_by_sql and connection.execute, and handle identifiers with allowlists rather than escaping.
Set the team rule that request data never enters an SQL string, back it with Brakeman in CI, and decide how legacy raw SQL gets migrated.
## What where does with a string Active Record's `where` accepts several argument shapes, and the shape decides how input is treated: | Call | What Active Record does | |---|---| | `where("make = 'Audi'")` | wraps the string as a raw SQL literal, no checks | | `where("make = ?", value)` | replaces each `?` with the quoted value | | `where("make = :make", make: value)` | replaces each `:make` with the quoted value | | `where(make: value)` | builds the predicate itself, binding the value | The first row is the dangerous one. A lone string is treated as **trusted SQL** because Active Record cannot tell your literal from text you assembled out of request data. ## Why interpolation is the bug In `Car.where("make = '#{params[:make]}'")`, the `#{}` is Ruby string interpolation. It runs **before** `where` is called, so by the time Active Record receives the argument it is one finished string with the user's characters inside it. If a visitor to the dealership's search page submits the make `x' OR '1'='1`, the condition becomes `make = 'x' OR '1'='1'`, true for every row. Other payloads can read other tables through a `UNION`, or hide rows another dealer should see. The attacker never needs special access: the search form is enough. ## The safe forms 1. **Hash conditions**: `Car.where(make: params[:make])`. Active Record builds `"cars"."make" = ?` and binds the value. Arrays become `IN (...)`, ranges become `BETWEEN`, `nil` becomes `IS NULL`. This is the first choice whenever the condition is equality, membership or a range. 2. **Positional placeholders**: `Car.where("price <= ? AND year >= ?", params[:max_price], params[:min_year])`. Each `?` is replaced by the quoted value, in order. 3. **Named binds**: `Car.where("price BETWEEN :low AND :high", low: params[:low], high: params[:high])`. Clearer when one value is used twice. 4. **Chaining**: `Car.where(make: params[:make]).where("mileage < ?", params[:max_miles])` mixes the forms without ever interpolating. ## The same rule beyond where Any Active Record method that takes an SQL fragment trusts that fragment: `find_by("...")`, `having`, `joins("...")`, `select`, `group`, `from`, `lock` and `find_by_sql`. `find_by`, `having` and `find_by_sql` accept the same placeholder forms as `where`; for the others, keep request data out of the string entirely. A few methods (`order`, `reorder`, `pluck`, `in_order_of`) add a guard that rejects strings that do not look like column names, but `where` and `select` have no such guard. ## What placeholders do not cover - **Identifiers**: a column name, table name or `ASC`/`DESC` cannot be a bind value. A user-chosen sort column needs an allowlist mapped to symbols. - **LIKE wildcards**: `where("model LIKE ?", "%#{params[:q]}%")` is injection-safe, because the interpolation happens inside the value, but a `%` or `_` in the search term still acts as a wildcard. Active Record's sanitization helpers handle that. - **Raw connections**: `connection.execute` with an interpolated string skips Active Record's quoting entirely. ## A review checklist for the search page When reviewing a filter-heavy page like the dealership's inventory search, walk each query once: 1. Find every string passed to a query method and look for `#{` inside it. 2. For each interpolated value, ask where it comes from: params, cookies, headers, or a model attribute a user once typed are all untrusted. 3. Move values into hash conditions or binds; move identifiers into an allowlist. 4. Check scopes and class methods on the model too, since `scope :by_make, ->(make) { where("make = '#{make}'") }` hides the same bug one file away from the controller. 5. Add a test that searches for `x' OR '1'='1` and expects no results rather than every car. ## How it is found in review Brakeman reports an interpolated fragment as an **SQL Injection** warning, with higher confidence when the value comes straight from `params`, cookies or request headers. In code review, the tell is a `#{` inside a string passed to a query method; the fix is almost always to move the value out of the string and into a hash condition or a bind.
- Is Car.where("model LIKE ?", "%#{params[:q]}%") vulnerable to SQL injection?Not to injection: the interpolation builds the bound value, which Active Record quotes as a whole, so a quote in `q` stays data. It is still imprecise, because `%` and `_` typed by the user act as LIKE wildcards. Escape them with Active Record's LIKE sanitization helper before wrapping the term in `%`.
- Why can a placeholder not fix Car.order("#{params[:sort]} DESC")?Placeholders bind values, and a column name is an identifier, not a value; a bound value would become a quoted string, not a column. Map the allowed choices to symbols instead, such as `{ "price" => :price, "year" => :year }.fetch(params[:sort], :created_at)`, and pass the symbol to `order`.
Hash conditions and placeholders are like a printed order form with a box labelled 'make': whatever a customer writes in the box stays an answer. Interpolation hands the customer a blank sheet and lets them write the instructions, including new ones.
- Active Record escapes every string passed to where automatically
- Interpolation is safe as long as the value is a param, not raw input
- Placeholders can bind column names and sort directions too
- Using find_by instead of where avoids SQL injection
- Wrapping the input in quotes inside the string makes it safe