Fix SQL injection in Ruby on Rails

1. Find SQL fragments that can reach request data: where, order, reorder, group, having, select, joins, from, pluck, find_by_sql, update_all and connection.execute with "#{...}" interpolation, and every Arel.sql() call.
2. Use hash conditions (where(status: status), where(customers: { name: name })) or placeholders (where("customers.name = ?", name), where("name = :name", name: name)). Active Record sends the values as bind parameters or quotes them for the adapter; the SQL text stays yours.
3. When you must build a fragment yourself, use sanitize_sql_array(["customers.name = ?", name]) or find_by_sql([sql, *values]); sanitize_sql_like only escapes LIKE wildcards.
4. For the sort column and direction, map each public option to an Arel attribute (Customer.arel_table[:name]) and call asc or desc on it; answer 400 for anything else.
5. Read request values with params.expect(:customer), so a missing value, an array or a hash answers 400 instead of reaching the query.
6. Add regression tests with this card's payloads (tautology, UNION, comment, sort expression, hidden column) next to functional tests for O'Brien and non-ASCII names, and run Brakeman in CI.

The three implementations share one API and one test suite; only the code below changes. The tests load the one that IMPL names.

def self.orders_for_customer(customer_name, status)
  Order.joins(:customer)
       .where("customers.name = '#{customer_name}' AND orders.status = '#{status}'")
       .select(:id, :status, :total_cents, :created_at)
       .order(:id)
end

def self.orders_for_customer(customer_name, status)
  # The SQL text never changes: SQLite receives both values as parameters.
  Order.joins(:customer)
       .where(customers: { name: customer_name }, status: status)
       .select(:id, :status, :total_cents, :created_at)
       .order(:id)
end

# Map each public sort option to a column. order() would accept any column name the caller
# sends, including customers.email, which the API never returns.
SORT_COLUMNS = {
  "created_at" => Order.arel_table[:created_at],
  "total_cents" => Order.arel_table[:total_cents],
  "customer" => Customer.arel_table[:name]
}.freeze

def self.search_orders(status, sort, direction)
  column = SORT_COLUMNS[sort]
  unless column && %w[asc desc].include?(direction)
    raise UnsupportedSort, "unsupported sort column or direction"
  end

  Order.joins(:customer)
       .where(status: status)
       .select("orders.*, customers.name AS customer")
       .order(direction == "desc" ? column.desc : column.asc, :id)
end

def self.orders_for_customer(customer_name, status)
  # sanitize_sql_like escapes %, _ and the escape character. A quote passes through unchanged.
  name = Order.sanitize_sql_like(customer_name)
  state = Order.sanitize_sql_like(status)
  Order.joins(:customer)
       .where("customers.name = '#{name}' AND orders.status = '#{state}'")
       .select(:id, :status, :total_cents, :created_at)
       .order(:id)
end

def self.search_orders(status, sort, direction)
  # Without Arel.sql(), order() raises for anything that is not a column name and direction:
  # it refuses a CASE expression, but takes customers.email or a column that does not exist.
  Order.joins(:customer)
       .where(status: status)
       .select("orders.*, customers.name AS customer")
       .order("#{sort} #{direction}", :id)
rescue ActiveRecord::UnknownAttributeReference
  raise UnsupportedSort, "unsupported sort column or direction"
end

The exploit tests send the attack and check that it fails; functional tests next to them keep ordinary requests working, so a fix that rejects everything fails too.

# Exploit: each payload must stay data and leave the query's meaning unchanged. Active Record
# wraps a string condition in parentheses, so the payloads close one.

test "exploit: tautology does not return other customers' orders" do
  assert_equal [], order_ids("/orders", customer: "' OR '1'='1", status: "shipped")
end

test "exploit: union cannot read another customer's orders" do
  customer = "') UNION SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 2 --"
  assert_equal [], order_ids("/orders", customer:, status: "shipped")
end

test "exploit: comment cannot cut off the status filter" do
  assert_equal [], order_ids("/orders", customer: "Alice Martin')--", status: "shipped")
end

test "exploit: sort expression is rejected, not used as an oracle" do
  # Row order would reveal whether each customer's hidden email starts with "b".
  sort = "CASE WHEN substr(customers.email, 1, 1) = char(98) THEN 0 ELSE 1 END"
  get "/admin/orders", params: { status: "shipped", sort:, dir: "asc" }
  assert_response :bad_request
end

test "exploit: direction cannot add a second sort expression" do
  dir = "ASC, CASE WHEN substr(customers.email, 1, 1) = char(98) THEN 0 ELSE 1 END"
  get "/admin/orders", params: { status: "shipped", sort: "created_at", dir: }
  assert_response :bad_request
end

test "exploit: sort by a column the API never returns is rejected" do
  # order() accepts any column name; sorting by the hidden email leaks its order.
  get "/admin/orders", params: { status: "shipped", sort: "customers.email", dir: "asc" }
  assert_response :bad_request
end

• Active Record wraps a string condition in parentheses: where("customers.name = '#{name}'") becomes WHERE (customers.name = '...'), so the UNION and comment payloads close the parenthesis too (') UNION ..., ')--). A payload written for another framework may fail with a syntax error and make the vulnerable code look safe.
• Since Rails 6.1, order() raises ActiveRecord::UnknownAttributeReference for an argument that does not look like a column name and direction. It still accepts any column name, including customers.email from a joined table, and a column that does not exist becomes a 500. This card's wrong implementation relies on that check, and sorting by the hidden email leaks its order.
• Arel.sql() tells Active Record that a string is trusted SQL and turns its raw-SQL check off. Never pass request data through it.
• sanitize_sql_like escapes %, _ and the escape character for LIKE patterns and leaves quotes alone: in this card's wrong implementation O'Brien breaks the query and ' OR '1'='1 returns every shipped order. Its right use is inside a bound value: where("name LIKE ?", "%#{Customer.sanitize_sql_like(q)}%").
• select, group, having, joins, from, pluck and update_all take SQL fragments too, and find_by_sql takes raw SQL; pass arrays with placeholders where the method accepts them.
• Keep consider_all_requests_local off in production (it is in production.rb) so a database error is the generic 500 page, not the SQL.

1. Run the tests in all three modes: IMPL=fixed must pass; IMPL=vulnerable and IMPL=wrong must fail (node ../../run-all.mjs sql-injection/ruby-rails does this for you, in the pinned Ruby container).
2. Against your own endpoint, send customer=' OR '1'='1 and customer=Alice Martin')-- and expect no extra rows; send a CASE expression or customers.email as the sort and expect 400.
3. Run Brakeman: it reported both interpolated where() strings and the Arel.sql() order here, but not order("#{sort} #{direction}") without Arel.sql(). Semgrep's tainted-sql-string rule only follows params within one file.

What each rule reported when it was run on this card's code, or what its source shows it covers where it was not run. A rule that exists for the weakness does not always catch it.

Download the project, install its dependencies once (this step needs the network), then run the tests against each implementation from the sql-injection/ruby-railsfolder.

BUNDLE_PATH=vendor/bundle BUNDLE_FROZEN=true bundle install

# Expected: every test passes
IMPL=fixed BUNDLE_PATH=vendor/bundle bundle exec ruby -Itest test/orders_test.rb

# Expected: an exploit test fails
IMPL=vulnerable BUNDLE_PATH=vendor/bundle bundle exec ruby -Itest test/orders_test.rb

# Expected: a test fails
IMPL=wrong BUNDLE_PATH=vendor/bundle bundle exec ruby -Itest test/orders_test.rb

Without Ruby installed, run the same commands with Docker in the image the card was verified in, pinned by its digest. The named volumes keep the downloaded dependencies and the build cache between runs, so the test runs need no network.

docker run --rm --init \
  -v "$PWD":/card -w /card \
  -e BUNDLE_PATH=vendor/bundle \
  ruby:4.0.7-trixie@sha256:0192d5244fa068587325c3ef4f886fdb2d6775689757d598f9a5261d0f86d40e \
  sh -c 'BUNDLE_PATH=vendor/bundle BUNDLE_FROZEN=true bundle install'

# Expected: every test passes
docker run --rm --init --network=none \
  -v "$PWD":/card -w /card \
  -e IMPL=fixed -e BUNDLE_PATH=vendor/bundle \
  ruby:4.0.7-trixie@sha256:0192d5244fa068587325c3ef4f886fdb2d6775689757d598f9a5261d0f86d40e \
  sh -c 'bundle exec ruby -Itest test/orders_test.rb'

# Expected: an exploit test fails
docker run --rm --init --network=none \
  -v "$PWD":/card -w /card \
  -e IMPL=vulnerable -e BUNDLE_PATH=vendor/bundle \
  ruby:4.0.7-trixie@sha256:0192d5244fa068587325c3ef4f886fdb2d6775689757d598f9a5261d0f86d40e \
  sh -c 'bundle exec ruby -Itest test/orders_test.rb'

# Expected: a test fails
docker run --rm --init --network=none \
  -v "$PWD":/card -w /card \
  -e IMPL=wrong -e BUNDLE_PATH=vendor/bundle \
  ruby:4.0.7-trixie@sha256:0192d5244fa068587325c3ef4f886fdb2d6775689757d598f9a5261d0f86d40e \
  sh -c 'bundle exec ruby -Itest test/orders_test.rb'

Each run writes a JUnit report to reports/TEST-*.xml. From the top folder of the download, node run-all.mjs sql-injection/ruby-rails runs all three modes and checks the expected results, in the container, so it needs Docker and Node.js but not the toolchain.

Download the project ZIP, 26 KB

Verified

Machine

Debian 13 (trixie) in Docker, on macOS 27.0 (Darwin 27.0.0, arm64)

Toolchain

• ruby 4.0.7 (ruby:4.0.7-trixie image, linux/arm64)
• bundler 4.0.20
• rails 8.1.4
• minitest 6.0.6
• minitest-reporters 1.8.0
• sqlite3 2.9.6 (SQLite 3.53.2)
• semgrep 1.180.0
• brakeman 8.1.0

Matrix

fixed: 12 of 12 tests pass; vulnerable: 8 of 12 tests fail (6 exploit tests); wrong: 6 of 12 tests fail (4 exploit tests)

Test results

fixed 12/12 pass; vulnerable 8/12 fail (6 exploit tests: all six, plus O'Brien and the unknown sort column as 500s); wrong 6/12 fail (4 exploit tests: the tautology, the UNION as a 500 because sanitize_sql_like escapes the underscores in total_cents and created_at, the comment and the hidden-column sort, plus O'Brien and the unknown sort column), test runs offline (--network=none) after install; node examples/fixes/run-all.mjs ruby-rails --install, results/2026-10-11-fix-rails.json

Dependency freshness

Rails 8.1.4 was published on 2026-09-24 and Brakeman 8.1.0 on 2026-10-01. uri 1.1.2 and prettyprint 0.2.1 (both 2026-10-08) were three days old and are not security releases, so the Gemfile holds uri at 1.1.1 and prettyprint at 0.2.0; every other gem in Gemfile.lock is at least 13 days old. OSV reported no known vulnerabilities for any pin. Ruby 4.0.7 (2026-09-15) is the latest 4.0 release.

Versions and results hold for the toolchain above. The card's tests are the check: run them with your own versions before relying on the fix.

References

1. OWASP SQL Injection Prevention Cheat Sheet
2. Rails Guides: Securing Rails Applications, SQL injection
3. Rails Guides: Active Record Query Interface, pure string and array conditions
4. Rails API: ActiveRecord::QueryMethods (where, order)
5. Rails API: ActiveRecord::Sanitization::ClassMethods (sanitize_sql_array, sanitize_sql_like)
6. Rails SQL Injection Examples (rails-sqli.org, tested on Rails 6.1)
7. Justin Collins: Rails 6.1 SQL injection updates (order() argument checks)
8. Brakeman: SQL injection
9. CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')

Attack patterns:

• CAPEC-66 at MITRE