In Active Record, how do you bind user input into hand-written SQL with the array form, sanitize_sql_array and sanitize_sql_like?
answer
- ? or :name, never #{}
- array form on find_by_sql
- PreparedStatementInvalid on arity mismatch
- quoted, not type-cast
- LIKE wildcards % and _ escaped
basics
~20 sPass [sql, values] with ? or :name placeholders to find_by_sql or count_by_sql; use Member.sanitize_sql_array to get the quoted string where only a string is accepted, and wrap search text in sanitize_sql_like so % and _ match literally.
solid answer
~40 s`find_by_sql` and `count_by_sql` accept `[sql, *values]` or `[sql, { name: value }]` and quote each value with the adapter's `quote` before running it. The connection's `select_all` and `execute` take only a finished string, so you build it with the public class method `Member.sanitize_sql_array([...])`. A count mismatch between `?` marks and values, or a missing `:name`, raises `ActiveRecord::PreparedStatementInvalid`. Arrays expand to a comma list, an empty array to `NULL`, a relation to its SQL as a subquery. The helpers quote but do not cast to the column type, so on MySQL a number is quoted as a string. `sanitize_sql_like(q)` escapes `%`, `_` and the escape character with a backslash, leaving the wildcards to you: `["name LIKE ?", "%#{Member.sanitize_sql_like(q)}%"]`. Avoid the `'%s'` form, which only escapes and leaves the quotes to you.
code
ruby · 9 linesclass Member < ApplicationRecord
def self.search_ranked(club, query)
find_by_sql([<<~SQL, { club_id: club.id, term: "%#{sanitize_sql_like(query)}%" }])
SELECT members.*, RANK() OVER (ORDER BY rating DESC) AS position
FROM members
WHERE club_id = :club_id AND name LIKE :term
SQL
end
endgo deeper
Remember the array form with ? or :name on find_by_sql, and that values never go into the SQL string through #{}.
Explain where the array form is accepted and where sanitize_sql_array must build the string first, what PreparedStatementInvalid means, and how sanitize_sql_like neutralises % and _.
Show the edge cases that bite in production: empty IN lists becoming NULL, no type casting, MySQL quoting numbers, SQLite's missing LIKE escape, and why identifiers need an allowlist instead.
Set the rule a team can review against: values only through placeholders, identifiers only from code, and raw SQL kept in a few named query methods.
## Why the helpers exist Hand-written SQL in Active Record still needs **values from outside**: a club id from the URL, a search box, a date range. Interpolating them with `#{}` puts caller-controlled text into the statement. Active Record's answer is the **array form** and the `sanitize_sql_*` family, defined in `ActiveRecord::Sanitization::ClassMethods` and public on every model (`Member.sanitize_sql_array`). They replace placeholders with values quoted by the current adapter's `quote` method. ## The array form Methods that take SQL conditions, such as `where` and `find_by_sql`, also accept an array whose first element is the SQL and the rest are values: - **Positional**: `["rating > ? AND club_id = ?", 1800, club.id]` — each `?` is replaced in order. - **Named**: `["club_id = :club_id AND joined_on >= :since", { club_id: club.id, since: 1.year.ago }]` — each `:name` is looked up in the hash; the same name may repeat. - `find_by_sql` and `count_by_sql` run `sanitize_sql` (an alias of `sanitize_sql_for_conditions`) on what you pass, so the array form works there directly. The connection's methods (`select_all`, `select_rows`, `select_value`, `execute`) do **not** take the array form. Build the string first: ```ruby sql = Member.sanitize_sql_array([ "SELECT name, rating FROM members WHERE club_id = :club_id AND rating >= :floor", { club_id: club.id, floor: params[:floor].to_i } ]) rows = Member.with_connection { |c| c.select_all(sql) } ``` ## How substitution behaves `sanitize_sql_array` handles each value by its shape: | Value | Becomes | |---|---| | String, number, date, `true`/`false`, `nil` | the adapter's quoted literal (`nil` becomes `NULL`) | | An Active Record object | its id, quoted | | An Array | a comma-separated list, for `IN (?)` | | An empty Array | `NULL`, so `IN (?)` becomes `IN (NULL)` | | An `ActiveRecord::Relation` | its `to_sql`, for a subquery | Errors and edge cases worth knowing: 1. Two `?` marks and one value raise **`ActiveRecord::PreparedStatementInvalid`** ("wrong number of bind variables"); a `:name` missing from the hash raises the same class. 2. In named form a PostgreSQL cast such as `::date` is left alone, and a backslash before a colon (`\:`) keeps a literal colon. 3. The helpers are **not schema-aware**: they quote without casting to the column's type. The MySQL adapter therefore quotes numbers as strings (`'4'`) to prevent query manipulation. 4. A string with neither `?` nor a hash falls back to `'%s'` printf substitution, where values are escaped with `quote_string` but **you** must write the surrounding quotes. It is a trap; use placeholders. This is quoting into a literal, done in Ruby before the SQL is sent. It is not a server-side bind parameter, which matters for prepared-statement reuse but not for correctness. ## LIKE patterns: sanitize_sql_like A search box feeding `LIKE` has a second problem besides quoting: `%` and `_` are **wildcards**. A member searching for `100%` would match every name starting with `100`. `sanitize_sql_like(string, escape_character = "\\")` prefixes each `%`, `_` and the escape character itself with the escape character: - `Member.sanitize_sql_like("50%_off")` returns `50\%\_off`. - It adds **no** wildcards and **no** quotes: you add `%` where you want them and still bind the result with `?`. - PostgreSQL and MySQL treat backslash as the default LIKE escape. SQLite has no default, so add `ESCAPE '\'`, or pass another character, `sanitize_sql_like(q, "!")`, with `ESCAPE '!'`. ```ruby term = "%#{Member.sanitize_sql_like(params[:q])}%" # SQLite has no default LIKE escape, so this query names one Member.find_by_sql(["SELECT * FROM members WHERE name LIKE ? ESCAPE '\\'", term]) ``` ## Mistakes reviewers catch 1. Building an `IN` list with `ids.join(\",\")` instead of `IN (?)` with the array: the join skips quoting, and an empty list produces `IN ()`, which most databases reject. 2. Calling `sanitize_sql_array` and then interpolating a **second** user value into the result with `#{}`: only the values that went through the placeholders are quoted. 3. Forgetting `sanitize_sql_like` on a search box, so a lone `%` or `_` turns a search into a match-everything pattern, which on a large table is also a slow scan. 4. Quoting by hand with `gsub(\"'\", \"''\")`: the adapter's `quote` also knows about backslashes, dates, booleans and `nil`. ## The rest of the family - `sanitize_sql_for_conditions` (alias `sanitize_sql`) returns a plain string unchanged and runs arrays through `sanitize_sql_array`. - `sanitize_sql_for_assignment` builds a SET clause from an array or a hash; the hash form does cast through attribute types. - `sanitize_sql_for_order` handles an ORDER BY fragment with `?` and is guarded against raw SQL; it belongs with `Arel.sql`. The habit an interviewer looks for: values only ever reach hand-written SQL through a placeholder, and identifiers (columns, directions) never do, because quoting a value cannot make a column name safe.
- Why can't a placeholder carry a column name or ASC/DESC in Active Record's array form?A placeholder becomes a quoted literal, so `ORDER BY ?` with `"rating"` becomes `ORDER BY 'rating'`: a constant that, depending on the database, sorts nothing or is rejected, and never the column. Identifiers must come from an allowlist in your code, or go through a hash order such as `order(rating: :desc)`.
- When does the escape character passed to sanitize_sql_like matter?When the database has no default LIKE escape or you prefer another one. Pass the character, `sanitize_sql_like(q, "!")`, and write the same one in `ESCAPE '!'`. The helper also doubles any occurrence of the escape character in the input so it matches literally.
- Does sanitize_sql_array cast "42abc" to an integer for an integer column?No. It is not schema-aware: it quotes the string as a string literal, and the database decides whether the comparison casts or fails. Cast in Ruby first, for example `Integer(params[:floor])`, when you need a number.
- Interpolating params into find_by_sql is fine once the value is stripped of quotes.
- connection.select_all sanitizes the [sql, values] array form like find_by_sql does.
- sanitize_sql_like adds the surrounding % wildcards and quotes for you.
- A missing placeholder value is bound as NULL instead of raising.
- sanitize_sql_array casts each value to the column's type before quoting it.
- A ? placeholder can safely carry a user-chosen column name for ORDER BY.