Preventing SQL injection in Rails

Direct Answer

SQL injection occurs when untrusted data is interpreted as SQL syntax because code builds a query by concatenation or interpolation. Use Active Record hash conditions or placeholder binds for values, allowlist any dynamic identifiers such as sort columns, and keep database permissions limited; input validation alone is not a substitute for parameterization.

Deep Dive & Architecture

A query such as User.where(email: value) gives Active Record both the column and the Ruby value as separate inputs. A placeholder condition does the same with an explicit SQL template. The adapter quotes and binds the value according to its type. An input like ' OR 1=1 -- remains one string value and cannot close a quote that the application never assembled itself.

Interpolation reverses that separation. When request text is inserted into an SQL string before the adapter sees it, the database receives one combined program. Escaping by hand is fragile because databases have different syntax, encodings, modes, and value types. Rails sanitization helpers exist for specialized framework code, but ordinary application queries should prefer structured Active Record APIs and binds.

Not every dynamic query part is a value. A placeholder cannot stand for a column name, table name, ASC keyword, or arbitrary operator. For sorting, translate a small public choice through a hash such as { 'newest' => { created_at: :desc }, 'title' => { title: :asc } }. Reject unknown choices. The same allowlist principle applies to selected fields and report expressions.

Raw SQL may be justified for a database-specific feature or measured performance path. Keep the static statement close to reviewed code, bind every external value through the adapter's supported API, cover it with tests, and avoid granting the application database user schema-changing privileges. A compromised read query should not automatically be able to drop tables or create users.

Testing should prove behaviour rather than search for one famous payload. Send quotes, comments, wildcards, Unicode, very long values, unexpected types, and invalid sort keys. Confirm the endpoint returns the correct empty or validation result and that the SQL shape in logs stays constant. Parameter filtering, log filtering, dependency updates, and database monitoring complete the defense, but none makes string interpolation safe.

Review query fragments at the point they become executable, not only where input first enters the controller. Data can travel through service objects, background jobs, or stored filters before reaching SQL, so the final query-building boundary must preserve parameterization.

Code Example

SORTS = {
  "newest" => { created_at: :desc },
  "title" => { title: :asc }
}.freeze

scope = User.where("email LIKE ?", "%#{User.sanitize_sql_like(params[:email].to_s)}%")
order = SORTS.fetch(params.fetch(:sort, "newest"), SORTS.fetch("newest"))
users = scope.order(order).limit(50)

The search value is escaped for LIKE wildcards and then bound; the sort identifier comes only from application-owned choices.

Common Mistakes & Pitfalls

Interpolating params into a where/order string or treating a validated string as safely quoted SQL.