guest@websec ~ % man 02-sql-injection

02 · SQL Injection

SQL injection happens when user input becomes part of a database query's code. One bad line can let an attacker log in as anyone, read every table, or change our data. It has hit us before, so it comes first.

Never build SQL by gluing strings together. Pass user values as parameters, and let Active Record do the quoting.

on this page

What SQL injection is
What an attacker can do
The fix: parameterized queries
Where it hides in Rails code
Finding it in our code
Limiting the damage
If it happens again

2.1 What SQL injection is

Our app talks to the database by sending it SQL text. If we build that text by pasting in what the user typed, the user is now writing part of our query. The database can't tell which part is ours and which part is theirs.

Example: a login that runs WHERE name = '#{params[:name]}'. A normal user types alice. An attacker types ' OR '1'='1, and the query becomes WHERE name = '' OR '1'='1', which is true for every row.

# VULNERABLE: string interpolation into SQL
User.where("name = '#{params[:name]}' AND password = '#{params[:password]}'").first
Product.find_by_sql("SELECT * FROM products WHERE title LIKE '%#{params[:q]}%'")
Order.joins("JOIN users ON users.id = #{params[:uid]}")

// VULNERABLE: string concatenation
const sql = "SELECT * FROM users WHERE name = '" + req.body.name +
            "' AND password = '" + req.body.password + "' LIMIT 1";

# VULNERABLE: f-string / % formatting
sql = f"SELECT * FROM users WHERE name = '{name}' AND password = '{password}' LIMIT 1"
cursor.execute(sql)

The quote character ' is the key. It ends our string early, and everything after it is read as SQL.

2.2 What an attacker can do

Log in without a password

' OR '1'='1 in both fields makes the condition always true, so you're logged in as the first user, often the admin. admin'-- works too: -- starts a SQL comment, so the password check is ignored.

Read data they shouldn't see

UNION SELECT bolts a second query onto the first. A product search can suddenly return the users table, password hashes included.

Ask yes/no questions (blind SQL injection)

Hiding errors and results doesn't stop it. If the page behaves even slightly differently (logged in or not, a result or none, a fast or slow response), each request answers one yes/no question, like "does the admin's password start with S?". Tools like sqlmap ask thousands of these automatically and pull out whole tables.

Change or destroy data

Depending on the database and on what the app's DB user is allowed to do, injection can also update rows (making the attacker an admin), delete data, or read files on the database server.

Try it yourself. This is a tiny but real SQL engine running in your browser. Switch between string concatenation and a parameterized query and watch what changes. The two "blind" buttons ask a yes/no question about the admin's password: logged in means yes.

sim: a real (tiny) SQL enginequeries are parsed and run against an in-memory table

username

password

payloads:

string concatenation parameterized query

search products

payloads:

string concatenation parameterized query

2.3 The fix: parameterized queries

Never glue input into SQL. Use parameters. With a parameterized query, the SQL text and the values travel to the database separately. The database reads the SQL first and only then plugs in the values, so a value can never become code, whatever characters it contains.

Removing quotes by hand (gsub("'", "")) is not a fix. It's easy to miss a case, and it does nothing for numbers, column names or LIKE patterns.

Rails parameterizes for you when you use hash conditions (where(name: x), find_by(name: x)) and placeholders (? or :name). The danger is any string you build yourself with #{} and pass to where, order, find_by_sql, joins or execute. Brakeman catches most of these.

# hash conditions and placeholders are parameterized
User.find_by(name: params[:name])
Product.where("title LIKE ?", "%#{Product.sanitize_sql_like(params[:q])}%")
Product.where("price < :max AND stock > :min", max: params[:max], min: 0)

# identifiers (columns, directions) can't be bound: allowlist them
SORTS = { "newest" => { created_at: :desc }, "price" => { price: :asc } }
Product.order(SORTS.fetch(params[:sort], { id: :asc }))

// node-postgres: $1, $2 placeholders
await db.query("SELECT * FROM users WHERE name = $1 AND active = $2", [name, true]);
// mysql2
await conn.execute("SELECT * FROM products WHERE title LIKE ?", [`%${q}%`]);
// Prisma tagged template (parameterized automatically)
await prisma.$queryRaw`SELECT * FROM users WHERE email = ${email}`;
// identifiers (column/table names) can't be parameters: map them from an allowlist
const col = { newest: "created_at", price: "price" }[req.query.sort] ?? "id";

# DB-API: let the driver bind values (never use % or f-strings yourself)
cursor.execute("SELECT * FROM users WHERE name = %s AND active = %s", (name, True))
# SQLAlchemy
session.execute(text("SELECT * FROM products WHERE title LIKE :q"), {"q": f"%{q}%"})
# Django ORM
User.objects.filter(name=name)
# identifiers can't be parameters: map them from an allowlist
col = {"newest": "created_at", "price": "price"}.get(request.GET.get("sort"), "id")

Column names and sort directions can't be parameters. For those, map the user's choice to a fixed list (an allowlist), like the SORTS example above.

2.4 Where SQL injection hides in Rails code

Active Record is safe when you pass it values, and unsafe when you pass it SQL strings you built. These are the patterns to look for in code review:

method	unsafe (string with user input)	safe
`where`, `not`, `rewhere`, `find_by`	`where("email = '#{params[:email]}'")`	`where(email: params[:email])` `where("email = ?", params[:email])`
`order`, `reorder`	`order("#{params[:sort]} #{params[:dir]}")`	allowlist: `order(SORTS.fetch(params[:sort], { id: :asc }))`
`select`, `pluck`, `group`, `having`, `from`, `joins`, `lock`	`pluck(params[:column])`	symbols you wrote: `pluck(:name)`
`sum`, `average`, `minimum`, `maximum`, `calculate`	`Order.sum(params[:field])`	`Order.sum(:total)`
`update_all`, `delete_by`, `destroy_by`	`update_all("status = '#{s}'")`	`update_all(status: s)`
`find_by_sql`, `count_by_sql`	`find_by_sql("… WHERE id = #{x}")`	`find_by_sql(["… WHERE id = ?", x])`
`connection.execute`, `exec_query`, `select_all`	`execute("DELETE … #{x}")`	build it with `sanitize_sql_array(["… ?", x])`, or use the model API
`Arel.sql`	`Arel.sql(params[:anything])` tells Rails "this is trusted SQL"	only around fixed strings you wrote yourself

More traps:

Lists: where("id IN (#{ids.join(',')})") is injectable. Use where(id: ids).
LIKE searches: % and _ are wildcards. Bind the value and wrap it with sanitize_sql_like, so a search for % doesn't match everything.
Second-order injection: data that was saved safely can still be dangerous later. If a background job or report builds SQL from a value stored in the database (a saved filter, a username), treat that value as user input too.

Recent Rails versions refuse most raw SQL in order and pluck unless you wrap it in Arel.sql. That's a safety net, not a reason to skip the allowlist.

2.5 Finding it in our code

Run Brakeman and read every "SQL Injection" warning, even the weak-confidence ones. New Rails apps ship with bin/brakeman; older apps can add the gem. Run it in CI so new warnings fail the build.
Search for interpolation inside query methods. Every #{ inside a SQL string deserves a second look.
Review rule: no merge if a pull request builds SQL with #{} or +, unless the value comes from a fixed allowlist in the same file.

# only the SQL injection check, all confidence levels
bin/brakeman -t SQL -w1

# query methods that receive an interpolated string
grep -rnE '\.(where|not|find_by|order|reorder|group|having|select|pluck|joins|from|lock|update_all|delete_by|destroy_by|find_by_sql|count_by_sql|execute|exec_query|select_all|sum|calculate)[ (].*#\{' app lib

# raw SQL that someone marked as trusted
grep -rn 'Arel.sql' app lib

2.6 Limiting the damage

Parameterized queries are the fix. These are extra layers for the day one bug slips through anyway:

Least-privilege database user. The app's DB user shouldn't be a superuser or own the schema in production. Run migrations with a separate user, and give reporting tools a read-only one.
Never show database errors to users. Error messages help attackers map our tables. Keep consider_all_requests_local = false in production (section 19).
Alert on SQL errors. A spike in ActiveRecord::StatementInvalid errors usually means someone is probing.
Encrypt the most sensitive columns, so a stolen dump shows only ciphertext (section 16).
A WAF helps, but it isn't the fix. Attackers have many ways to disguise a payload.

Rails can't choose our database permissions or alerts. That's our setup and our process.

2.7 If it happens again

A short checklist, in order. Section 21 covers incident response in general.

Contain: block the attacking IPs or switch off the vulnerable feature. A quick hotfix is fine.
Fix the bug with parameters, then search the whole codebase for the same pattern (the commands above). One injection bug usually has siblings.
Work out what was read or changed. Search web and database logs for the vulnerable URL and for payloads like ', UNION, --, SLEEP or pg_sleep.
Rotate anything in the database that grants access: API tokens, OAuth tokens, password-reset tokens, and the database password itself.
Protect users: if password hashes may have leaked, force password resets. bcrypt slows attackers down, but weak passwords still fall.
Notify: a personal-data leak may have to be reported to the regulator within 72 hours (PDPA). Bring in legal early.
Learn: add a regression test that sends the attack payload, and add Brakeman to CI if it isn't there yet.

mark section 02 as read