Class: RuboCop::Cop::DevDoc::Rails::AvoidRawSql

Inherits:

Base

• Object
• Base
• RuboCop::Cop::DevDoc::Rails::AvoidRawSql

Defined in:

lib/rubocop/cop/dev_doc/rails/avoid_raw_sql.rb

Overview

Avoid raw SQL in query methods and ‘connection.execute`.

## Rationale Raw SQL bypasses Rails’ adapter abstraction, hides typos in column and table names (a misspelled column silently returns nothing instead of raising ‘NameError`), is non-reversible in migrations, and ships no audit trail for why raw SQL was chosen over the Rails idiom. Every raw-SQL site should be a deliberate, reviewable choice — not a default.

Prefer, in order:

• The hash/array form: ‘where(status: “active”)`, `where(status: [“active”, “pending”])`.

• Parameterized fragments: ‘where(“col = ?”, val)`, `where(“col = :sym”, sym: val)`. The `?` / `:name` placeholder binds the value safely and the column name is the only user-controlled surface.

• Arel nodes for database-specific constructs that the hash form can’t express.

  ❌ Raw SQL — typo in ‘statu` returns nothing, silently User.where(“statu = ’active’”)

  ✔️ Hash form — typo raises ‘ArgumentError` at the query layer User.where(status: “active”)

  ✔️ Parameterized fragment — value bound, column name spelled User.where(“status = ?”, status)

## Scope The cop covers any string argument to:

• **Execution methods** — ‘execute`, `exec_query`, `exec_update`, `exec_delete`, `exec_insert`, `find_by_sql`, `select_rows`, `select_values`, `select_one`, `select_all`, `update_sql`, `insert_sql`, `delete_sql`, `update_all`, `delete_all`.

• **Query fragment methods** — ‘where`, `having`, `order`, `reorder`, `joins`, `select`, `group`, `from`, `lock`, `pluck`.

• **‘Arel.sql`** — the explicit opt-in to raw SQL (see below).

Applies everywhere — application code AND migrations. A migration’s ‘execute “UPDATE …”` is the same smell as an app code `where(“…”)`; both bypass the adapter and offer no audit trail.

## ‘Arel.sql` Always flagged. `Arel.sql` exists specifically to bypass Rails’ raw-SQL protection — reaching for it should require justification, not be a reflex. If a fragment is genuinely needed, disable inline with a reason; otherwise express it as an Arel AST node or a parameterized query.

## Counter updates (‘update_all(“col = col + 1”)`) For **single records**, use `Model.increment_counter(:col, id)` or `Model.update_counters(id, col: 1)` — both generate parameterized SQL and surface typos in the column name. Neither is flagged by `DevDoc/Migration/AvoidBypassingValidation` (they are the Rails-blessed atomic-counter primitives).

For **bulk counter updates**, the only clean option is ‘find_each { |m| Model.increment_counter(:col, m.id) }` — N queries, slow on large tables, but free of the validation-bypass smell. If the N-query cost is genuinely unacceptable, `update_all(“col = col + 1”)` requires disabling BOTH this cop AND `DevDoc/Migration/AvoidBypassingValidation` with reasons — the friction is the audit trail (locking implications, idempotency on re-runs, and the like are worth a second look).

❌ Single-record counter update via raw SQL
User.where(id: id).update_all("login_count = login_count + 1")

✔️ Parameterized — generates safe SQL
User.increment_counter(:login_count, id)

## Exception Genuine raw-SQL needs — database-specific DDL (‘CREATE INDEX CONCURRENTLY`, `ALTER TYPE … ADD VALUE`), complex joins that don’t fit Arel, analytic queries with CTEs — go through an inline disable comment with a brief reason. The friction is the feature: every raw-SQL site ends up in code review with an articulated justification.

## Migration backfills are NOT an exception A common AI-agent reflex is to justify ‘execute “UPDATE users SET role = 0 …”` with “models can drift, so raw SQL is safer.” That justification is rejected here. The data-integrity cost of bypassing validations and callbacks outweighs the (rare, usually catchable) risk of a migration failing because a model changed.

The correct backfill pattern goes through the model with ‘save!` — `DevDoc/Migration/AvoidBypassingValidation` enforces this, and `best_practices/backend/en/02_migration.md` shows the shape:

✔️ Model through the migration — validations run, callbacks fire
User.where(role: nil).find_each do |user|
  user.role = 0
  user.save!
end

If existing rows would fail validation, fix the data first rather than bypassing validation — that’s the signal, not an obstacle.

NOTE: Parameterized fragments are detected by the presence of a ‘?` or `:name` placeholder plus at least one additional argument. A `?` appearing in literal SQL that isn’t a placeholder (‘where(“name LIKE ’%?%‘”)`) is a residual false negative; inline-disable if it surfaces.

NOTE: Interpolated strings (‘where(“col = ’#val‘”)`) are ALWAYS flagged — they cannot be parameterized by construction and are a SQL-injection risk. Use the `?` placeholder form.

NOTE: Method calls returning strings (‘where(some_helper)`) are not flagged — the cop only inspects literal strings. Helper methods that wrap raw SQL should themselves carry the disable.

Examples:

# bad — raw SQL
execute "UPDATE users SET role = 0 WHERE role IS NULL"
User.where("status = 'active'")
User.where("status = '#{status}'")
User.order("created_at")
User.joins("INNER JOIN items ON items.user_id = users.id")
User.update_all("login_count = login_count + 1")
User.select("DISTINCT status")
Arel.sql("NULLS LAST")

# good — hash / array / symbol form
User.where(status: "active")
User.where(status: ["active", "pending"])
User.order(:created_at)
User.joins(:items)
User.update_all(status: "active")
User.select(:status)

# good — parameterized fragment
User.where("status = ?", status)
User.where("status = :sym", sym: status)
User.find_by_sql("SELECT * FROM users WHERE id = ?", id)

# good — counter update on a single record
User.increment_counter(:login_count, id)

Constant Summary

MSG =

'Avoid raw SQL — prefer the hash/array form, a ' \
'parameterized fragment, or an Arel node. Disable with ' \
'a reason when raw SQL is genuinely required.'.freeze

EXECUTION_METHODS =

%i[
  execute exec_query exec_update exec_delete exec_insert
  find_by_sql select_rows select_values select_one select_all
  update_sql insert_sql delete_sql update_all delete_all
].freeze

FRAGMENT_METHODS =

%i[
  where having order reorder joins select group from lock pluck
].freeze

SQL_METHODS =

(EXECUTION_METHODS + FRAGMENT_METHODS).freeze

RESTRICT_ON_SEND =

(SQL_METHODS + [:sql]).freeze

Instance Method Summary

• #on_send(node) ⇒ Object

Instance Method Details

#on_send(node) ⇒ Object

# File 'lib/rubocop/cop/dev_doc/rails/avoid_raw_sql.rb', line 172

def on_send(node)
  if arel_sql?(node)
    check_arel_sql(node)
  elsif SQL_METHODS.include?(node.method_name)
    check_sql_method(node)
  end
end