Split View: SQL 인젝션과 파라미터 바인딩 — ORM을 써도 뚫리는 지점들

SQL Injection and Parameter Binding — The Places That Still Break When You Use an ORM

• Introduction — If You Are Looking for an Escaping Function, You Are Headed the Wrong Way
• Why Binding Rather Than Escaping — Separation at the Parser Level
• How a Prepared Statement Is Actually Handled on the Server
• Three Places That Still Break Under an ORM
• The Things That Cannot Be Bound — Identifiers, ORDER BY, IN, LIKE
• Second-Order Injection and NoSQL Injection — Same Principle, Different Surface
• Defense in Depth — Least Privilege Accounts and Detection
• Wrapping Up — Look at the Second Argument of the execute Call

Introduction — If You Are Looking for an Escaping Function, You Are Headed the Wrong Way

The fix most often suggested in code reviews about SQL injection takes this shape. "Escape the quotes," "filter the special characters," "double up the single quotes." Escaping functions and blacklist regexes still show up at the top of search results.

This approach is a losing fight in principle. Escaping tries to make a value safe inside a string literal, and cases where that premise breaks keep coming up. The following code escapes correctly and is still exploitable.

# In a numeric context there is no quote to escape
order_id = request.args["id"]          # "1 OR 1=1"
cur.execute("SELECT * FROM orders WHERE id = " + escape(order_id))

SELECT * FROM orders WHERE id = 1 OR 1=1

An escaping function deals with quotes and backslashes, but where a value goes in without quotes it has nothing to do. The same problem arises in a LIMIT clause, a sort direction, a boolean comparison, and in situations where the character set is mismatched.

The right answer lives at a different layer. Instead of turning the value into a safe string, keep the value out of the SQL text altogether. This post covers how that structure actually works, and where the common belief that using an ORM solves it automatically falls apart.

Why Binding Rather Than Escaping — Separation at the Parser Level

A database processes a query in roughly three stages: parsing, planning, and execution. Injection is a problem of the parsing stage. Once user input becomes part of the SQL text, the parser interprets it as a grammatical element. The parser has no information saying "this part came from a user."

Binding reverses this order. First the SQL containing placeholders goes to the parser and the syntax tree is fixed; the values are delivered afterward in a separate message. By the time the values arrive, the grammar is already decided, so whatever is inside a value cannot change the structure.

Put the vulnerable code and the safe code side by side and the difference is obvious.

import psycopg

email = request.args["email"]

# Vulnerable: the value becomes SQL text
with conn.cursor() as cur:
    cur.execute(f"SELECT id, email, role FROM users WHERE email = '{email}'")

# Safe: the value travels on a separate channel
with conn.cursor() as cur:
    cur.execute("SELECT id, email, role FROM users WHERE email = %s", (email,))

One thing here absolutely must be pointed out: what %s really is. It is not Python string formatting but the placeholder of the DB-API driver. Which means the following two lines are completely different code.

cur.execute("SELECT * FROM users WHERE email = %s", (email,))   # binding
cur.execute("SELECT * FROM users WHERE email = %s" % email)     # string formatting, vulnerable

In the second line Python completes the string first, so only already assembled SQL reaches the driver. A single character of difference makes it a vulnerability. Checking whether the execute call has a second argument is the fastest test in a code review.

JavaScript is the same.

// Vulnerable: assembled with a template literal
const { rows } = await pool.query(
  `SELECT id, email FROM users WHERE email = '` + req.query.email + `'`
)

// Safe: placeholder plus an array of values
const { rows } = await pool.query('SELECT id, email FROM users WHERE email = $1', [
  req.query.email,
])

Why binding beats escaping, in one sentence: escaping makes the developer responsible for proving that a value is safe, whereas binding removes the very path by which a value can become code. The former requires you to consider every possible input; the latter leaves nothing to consider.

How a Prepared Statement Is Actually Handled on the Server

Let us confirm that binding really does separate the value. In PostgreSQL the extended query protocol is split into three stages: Parse, Bind, Execute. You can reproduce it explicitly.

PREPARE user_by_email (text) AS
  SELECT id, email, role FROM users WHERE email = $1;

EXECUTE user_by_email ('alice@example.com');

Now put the attack string straight in.

EXECUTE user_by_email ('nobody@example.com'' OR ''1''=''1');

 id | email | role
----+-------+------
(0 rows)

No rows come back. OR 1=1 was not interpreted as a condition but treated as part of the email value. Turn on server logging and you see exactly what happened.

psql -c "ALTER SYSTEM SET log_statement = 'all'" -c "SELECT pg_reload_conf()"
tail -f /var/log/postgresql/postgresql-16-main.log

LOG:  execute user_by_email: SELECT id, email, role FROM users WHERE email = $1
DETAIL:  parameters: $1 = 'nobody@example.com'' OR ''1''=''1'

The query text and the parameters are recorded on separate lines. This is the essence of binding. The SQL text the server received contains no user input.

There is one trap here that is often missed. Not every driver actually uses server-side prepare. Some drivers escape the values on the client side, assemble the SQL, and send it as a simple query. This is called emulation.

// The PHP PDO default may be emulation depending on the driver
$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_EMULATE_PREPARES => false,   // force server-side prepare
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

# MySQL JDBC: you have to say so explicitly to get server-side prepare
jdbc:mysql://db:3306/app?useServerPrepStmts=true&cachePrepStmts=true

Emulation is not automatically a vulnerability. If the implementation is correct it is safe. But the basis of that safety drops from "the parser separated it" down to "the escaping in the driver is correct." The known techniques for bypassing escaping under the GBK family of character sets in older MySQL were a problem at exactly this layer. If you have the choice, turning on server-side prepare is better.

Three Places That Still Break Under an ORM

"We use an ORM, so we do not have to think about injection" is only half right. Values that pass through the query builder API of an ORM are bound, but every ORM leaves open a door down to raw SQL. And production code uses that door often.

The first place is raw queries.

from sqlalchemy import text

# Vulnerable: text() turns the string straight into SQL
result = session.execute(
    text(f"SELECT * FROM orders WHERE status = '{status}' ORDER BY created_at DESC")
)

# Safe: text() has binding placeholders too
result = session.execute(
    text("SELECT * FROM orders WHERE status = :status ORDER BY created_at DESC"),
    {"status": status},
)

The mere fact of using text() is not the problem. Using an f-string inside it is. This distinction becomes the central criterion in a code review.

Django is the same.

# Vulnerable
User.objects.raw("SELECT * FROM auth_user WHERE username = '%s'" % username)
User.objects.extra(where=[f"last_login > '{since}'"])

# Safe
User.objects.raw("SELECT * FROM auth_user WHERE username = %s", [username])
User.objects.filter(last_login__gt=since)

The Django extra() API is one the documentation itself discourages. Search the codebase for extra(, RawSQL( and .raw( and you usually turn up a few spots.

rg -n --type py '\.extra\(|RawSQL\(|\.raw\(' src/

src/reports/views.py:88:    qs = Order.objects.extra(where=[f"total_amount > {threshold}"])
src/admin/search.py:41:    rows = User.objects.raw("SELECT * FROM auth_user WHERE email LIKE '%%%s%%'" % q)

The second place is the pattern of assembling the condition clause as a string. It is especially common on screens with several search filters.

// Vulnerable: using knex but gluing the conditions on as strings
let query = knex('orders')
if (req.query.status) {
  query = query.whereRaw(`status = '${req.query.status}'`)
}
if (req.query.minAmount) {
  query = query.whereRaw(`total_amount >= ${req.query.minAmount}`)
}

// Safe: the builder API, or the binding argument of whereRaw
let query = knex('orders')
if (req.query.status) {
  query = query.where('status', req.query.status)
}
if (req.query.minAmount) {
  query = query.whereRaw('total_amount >= ?', [Number(req.query.minAmount)])
}

Sequelize is no different. With sequelize.query you always use replacements or bind.

// Safe
const rows = await sequelize.query(
  'SELECT id, email FROM users WHERE tenant_id = :tenantId AND email = :email',
  { replacements: { tenantId, email }, type: QueryTypes.SELECT }
)

The third place is the pattern of filling the ORM filter conditions directly from client input. Grammatically this is not injection, but the result is the same.

// Vulnerable: the request body becomes the where clause verbatim
const users = await User.findAll({ where: req.body.filter })

If the client puts another tenant condition or an operator into filter, unintended data comes out. Input has to be validated against a schema and then mapped explicitly.

const schema = z.object({
  status: z.enum(['pending', 'paid', 'refunded']).optional(),
  minAmount: z.coerce.number().min(0).optional(),
})
const filter = schema.parse(req.body)

The Things That Cannot Be Bound — Identifiers, ORDER BY, IN, LIKE

This is where people get stuck most often in practice. Binding only works for values. You cannot use a placeholder in a spot that determines the syntactic structure.

-- Does not work. The column name is interpreted as a string literal
SELECT * FROM orders ORDER BY $1;

ERROR:  cannot use column reference in ORDER BY with a parameter

To let users choose the sort column, an allowlist is the only way. You are not inspecting the input; you are substituting the input with a safe SQL fragment decided in advance.

SORT_COLUMNS = {
    "created": "o.created_at",
    "amount": "o.total_amount",
    "status": "o.status",
}
SORT_DIRECTIONS = {"asc": "ASC", "desc": "DESC"}

def build_order_by(sort_key: str, direction: str) -> str:
    column = SORT_COLUMNS.get(sort_key, "o.created_at")
    order = SORT_DIRECTIONS.get(direction.lower(), "DESC")
    return f"ORDER BY {column} {order}"

sql = f"SELECT o.id, o.total_amount FROM orders o WHERE o.tenant_id = %s {build_order_by(sort, dir)} LIMIT %s"
cur.execute(sql, (tenant_id, limit))

Input that is not in SORT_COLUMNS quietly falls back to the default. User input never enters the SQL; it is used only as a dictionary key, so whatever string arrives, the result is one of three safe values.

If you have to use table or column names dynamically, use the identifier quoting API of the driver. Do not wrap them as strings.

from psycopg import sql

cur.execute(
    sql.SQL("SELECT * FROM {} WHERE tenant_id = %s").format(sql.Identifier(table_name)),
    (tenant_id,),
)

The same principle applies when you build dynamic SQL inside a PostgreSQL function.

-- %I quotes safely as an identifier, %L as a literal
EXECUTE format('REFRESH MATERIALIZED VIEW %I', target_view);
EXECUTE format('SELECT count(*) FROM orders WHERE status = %L', status_value);

IN lists are confusing because the number of placeholders varies. There are two straightforward approaches.

# Approach 1: generate as many placeholders as there are values (the values are still bound)
placeholders = ", ".join(["%s"] * len(ids))
cur.execute(f"SELECT * FROM orders WHERE id IN ({placeholders})", tuple(ids))

# Approach 2: bind a single array (PostgreSQL)
cur.execute("SELECT * FROM orders WHERE id = ANY(%s)", (list(ids),))

Approach 2 is better. The number of placeholders never changes, so the execution plan cache is reused, and it is easy to put an upper bound on the list length.

The LIKE clause has a trap of a different kind from injection. Even with correct binding, a % or _ supplied by the user acts as a wildcard.

# Bound correctly, but if the user enters just "%" every row is returned
cur.execute("SELECT * FROM users WHERE name LIKE %s", (f"%{q}%",))

This does become a security incident. It triggers a full table scan leading to denial of service, or it exceeds the intended search scope and exposes other data. The wildcards have to be escaped.

def like_escape(value: str) -> str:
    return value.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")

cur.execute(
    r"SELECT * FROM users WHERE name LIKE %s ESCAPE '\'",
    (f"%{like_escape(q)}%",),
)

To summarize, the injection surface has to be handled differently in each position.

Second-Order Injection and NoSQL Injection — Same Principle, Different Surface

The type that input validation cannot catch is second-order injection. The value was stored safely, but later some other code assembles it into a string.

# At signup: stored safely via binding
cur.execute("INSERT INTO users (username, email) VALUES (%s, %s)", (username, email))

Even if username contains something like report'; DROP TABLE audit_log; --, nothing happens at this point. It is just a string. The problem is the batch job that runs a few days later.

# Nightly batch: create a view per user
for row in cur.execute("SELECT username FROM users").fetchall():
    name = row[0]
    cur.execute(f"CREATE OR REPLACE VIEW report_{name} AS SELECT * FROM orders WHERE owner = '{name}'")

This is where it detonates. The cause is trusting a value because it was read from the database. Second-order injection is dangerous because the attack flow spans two codebases, so it is invisible in review, and because in the logs it looks as though the batch job executed strange SQL all by itself.

The principle is simple. Wherever the value came from, always bind it when it goes into SQL. "It came from our own database" is not grounds for trust. If it has to be used as an identifier, like a report view name, use an internal integer such as the user ID rather than a user-supplied string.

NoSQL follows the same principle. Only the syntax is not SQL; the fact that user input can change the query structure is identical.

// Vulnerable: the value from the request body becomes the filter verbatim
app.post('/login', async (req, res) => {
  const user = await db.collection('users').findOne({
    email: req.body.email,
    password: req.body.password,
  })
  if (user) return res.json({ token: issueToken(user) })
  return res.status(401).json({ error: 'invalid credentials' })
})

A JSON body does not carry only strings. It can carry objects.

curl -X POST https://api.example.com/login \
  -H 'Content-Type: application/json' \
  -d '{"email":"admin@example.com","password":{"$ne":null}}'

{"token":"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."}

Login succeeds without knowing the password. The driver received an operator object rather than a value, and interpreted it as a query operator. In form-encoded requests the bracket notation can also build a nested object, so the same thing happens.

The defense is to fix the type.

import { z } from 'zod'

const LoginBody = z.object({
  email: z.string().email().max(254),
  password: z.string().min(8).max(200),
})

app.post('/login', async (req, res) => {
  const parsed = LoginBody.safeParse(req.body)
  if (!parsed.success) return res.status(400).json({ error: 'invalid payload' })

  const { email, password } = parsed.data
  const user = await db.collection('users').findOne({ email })
  if (!user || !(await argon2.verify(user.passwordHash, password))) {
    return res.status(401).json({ error: 'invalid credentials' })
  }
  return res.json({ token: issueToken(user) })
})

For the same reason it is better to turn off server-side JavaScript evaluation features. The MongoDB where operator and function execution in the aggregation pipeline are textbook paths by which input becomes code.

Defense in Depth — Least Privilege Accounts and Detection

Even with binding applied rigorously, you have to assume there is a spot somewhere in the codebase that was missed. What determines the blast radius at that moment is the database account privileges.

If the application account is the owner or a superuser, a single injection leads to the whole schema being dropped. Conversely, if it only has DML privileges on the tables it needs, the same injection ends at reading data.

-- Application-only role: no DDL, no ownership
CREATE ROLE app_rw LOGIN PASSWORD 'set-by-secret-manager';

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA app TO app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app_rw;

-- Cut off runaway queries
ALTER ROLE app_rw SET statement_timeout = '5s';
ALTER ROLE app_rw SET idle_in_transaction_session_timeout = '30s';

For read-only paths such as reports or admin screens, separate the account.

CREATE ROLE app_ro LOGIN PASSWORD 'set-by-secret-manager';
GRANT USAGE ON SCHEMA app TO app_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_ro;

You also need the habit of checking the privileges.

SELECT grantee, table_name, string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privs
FROM information_schema.role_table_grants
WHERE grantee IN ('app_rw', 'app_ro')
GROUP BY grantee, table_name
ORDER BY grantee, table_name;

 grantee | table_name |             privs
---------+------------+--------------------------------
 app_ro  | orders     | SELECT
 app_rw  | orders     | DELETE, INSERT, SELECT, UPDATE
 app_rw  | users      | DELETE, INSERT, SELECT, UPDATE

For detection, static analysis has the best cost-benefit ratio. It traces, as a data flow, the path by which string assembly reaches a query execution function.

semgrep --config 'p/sql-injection' --config 'p/nosql-injection' src/

src/reports/views.py
   88┆ qs = Order.objects.extra(where=[f"total_amount > {threshold}"])
      ⚠ python.django.security.injection.sql.sql-injection-extra
        User-controlled data flows into a raw SQL clause.

src/api/search.js
   34┆ knex.raw(`SELECT * FROM orders WHERE status = '${status}'`)
      ⚠ javascript.knex.security.knex-raw-sql-injection

If you fail CI only on newly added violations, you are not held hostage by the existing code.

semgrep ci --config 'p/sql-injection' --baseline-commit "$(git merge-base origin/main HEAD)"

Finally, one correction about web firewalls. A WAF blocks known patterns such as UNION SELECT, but bypass techniques keep appearing and it also produces false positives that block legitimate input. It is a mitigation that buys time until the fix ships, not a countermeasure. Adding a WAF rule and closing the ticket is the most dangerous way to finish.

Wrapping Up — Look at the Second Argument of the execute Call

The content of this post compresses into three lines of code review rules.

First, check whether values are passed to the query execution function as a separate argument. If an f-string, a template literal, string addition or percent formatting appears inside the query string, that spot is a vulnerability. The fact that you are using an ORM does not exempt you from this check.

Second, handle positions that cannot be bound with an allowlist only. The ORDER BY column, the sort direction and the table name are a mapping, not a validation. Make user input serve solely as a key for selecting a safe value, never as something that enters the SQL.

Third, do not treat the origin of a value as grounds for trust. A value read from the database and a value handed over by an internal service both get bound the same way when they go into SQL. Second-order injection disappears with this one principle.

And in case all of that fails, strip DDL privileges from the application account. Whether the incident report says "some tables were read" or "the schema was dropped" is decided not by the code but by the GRANT statements.