FlawOpen

flawopen.com/sql-injection/Ruby

● CWE-89 · Critical
FlawOpen Security Research

SQL Injection in Ruby (ActiveRecord, pg, Sequel)

Ruby string interpolation (#{}) inside where(), find_by_sql or order() lets a request parameter rewrite your SQL and return every row (CWE-89). The safe form for ActiveRecord, the pg gem and Sequel, and why wrapping input in Arel.sql to silence a Rails error reopens the hole.

💡 Plain English Explainer (ELI5)

You hand a librarian a slip that reads 'Fetch the record for: ___' and write in the blank: 'John. Also unlock the archive and bring every file.' A librarian who reads the whole slip as one set of instructions does both. Gluing user input into a SQL string builds exactly that slip. A parameterized query is a printed form with a sealed box marked 'name': whatever is written inside is only ever looked up as a name, however it is worded.

Core Concepts & Subsystem Terms

where("... #{}") vs where("... ?", v)
#{} runs before ActiveRecord is called, so where("id = #{params[:id]}") hands Rails a finished SQL fragment. With where("id = ?", id) or the hash form where(id: id), ActiveRecord quotes the value through the connection adapter.
find_by_sql / sanitize_sql_array
find_by_sql and select_all take raw SQL. Pass an array — find_by_sql([sql, value]) — and Rails runs it through sanitize_sql_array, which quotes each value into its ?. A plain interpolated string is sent as written.
order() and Arel.sql
Since Rails 6.1, order and pluck raise ActiveRecord::UnknownAttributeReference for arguments that are not column names. Wrapping input in Arel.sql() to make that error go away tells Rails the string is trusted SQL, which reopens the injection.
PG::Connection#exec_params
The pg gem's exec(sql) sends a finished string and accepts several semicolon-separated statements. exec_params(sql, [values]) sends values separately to fill $1, $2, and allows only one statement per call.
Sequel.lit and placeholders
Sequel's dataset methods such as where(team: team) bind values. Sequel.lit("team = ?", team) and db["... = ?", team] bind too, but Sequel.lit on an interpolated string is raw SQL.

Framework mappings

Step-by-Step Attack Flow

Step 1

Request value reaches the controller

params[:id] arrives as a String from the query string and goes straight into UsersController#show. Rails does not coerce it to an integer.

Step 2

Interpolation builds the fragment

"id = #{params[:id]}" evaluates first. With the benign test value 0 OR 1=1, where receives id = 0 OR 1=1 as if it were code the developer wrote.

Step 3

ActiveRecord treats the string as SQL

A String argument to where is a SQL fragment by design, so Rails appends it to the WHERE clause unchanged and the database parses OR 1=1 as part of it.

Step 4

Every row is exposed

.first hides the scale here, but User.where(...) in an index or export action returns the full table; the pg-gem exec in the export action does the same.

Source Code: Flaw vs. Secure Implementation

UNPATCHED FLAW
# users_controller.rb: each query below interpolates request data into SQL
class UsersController < ApplicationController
  # ActiveRecord: #{} is evaluated before where() ever sees it
  def show
    @user = User.where("id = #{params[:id]}").first
  end

  # find_by_sql with a plain string is sent as written
  def orders
    @orders = Order.find_by_sql("SELECT * FROM orders WHERE status = '#{params[:status]}'")
  end

  # Arel.sql silences the Rails 6.1+ check and marks input as trusted SQL
  def index
    @users = User.order(Arel.sql(params[:sort].to_s))
  end

  # pg gem: exec sends one finished string
  def export
    conn = PG.connect(dbname: "app")
    rows = conn.exec("SELECT email FROM users WHERE team = '#{params[:team]}'")
    render json: rows.to_a
  end
end
HARDENED SECURE PATCH
# users_controller.rb: values are bound, sort columns come from an allowlist
class UsersController < ApplicationController
  SORTABLE = { "id" => :id, "email" => :email, "created" => :created_at }.freeze

  # ActiveRecord: the hash form quotes the value through the adapter
  def show
    @user = User.find_by(id: params[:id])
  end

  # array form: sanitize_sql_array quotes the value into the ?
  def orders
    @orders = Order.find_by_sql(["SELECT * FROM orders WHERE status = ?", params[:status]])
  end

  # allowlist lookup; order receives a known column symbol
  def index
    @users = User.order(SORTABLE.fetch(params[:sort].to_s, :id) => :asc)
  end

  # pg gem: exec_params sends the value separately for $1
  def export
    conn = PG.connect(dbname: "app")
    rows = conn.exec_params("SELECT email FROM users WHERE team = $1", [params[:team]])
    render json: rows.to_a
  end
end

Engineering & System Hardening Checklist

Sources