Backend Interview: How Do You Prevent SQL Injection?

Problem and Scope

A multi-tenant order platform exposes an internal search endpoint. The authenticated user's tenant is available from the server session. The request may contain a customer email, a list of order statuses, a sort field, a sort direction, and a result limit. A review finds code shaped like this:

const sql = `
  SELECT id, customer_email, status, total_cents, created_at
  FROM orders
  WHERE tenant_id = '${tenantId}'
    AND customer_email = '${input.email}'
    AND status IN (${input.statuses.join(",")})
  ORDER BY ${input.sort} ${input.direction}
  LIMIT ${input.limit}
`

Explain how you would redesign this query boundary so attacker-controlled text cannot change the SQL grammar. Cover values, optional filters, list parameters, identifiers such as sort columns, stored procedures, ORM raw query escape hatches, database privileges, logging, tests, and previously stored data that is later reused in dynamic SQL.

The central rule is that SQL code must be determined by trusted application code, while external data reaches the database through a server-side parameter-binding interface. Input validation and least privilege add useful barriers, but they do not turn string concatenation into a safe query.

This is a backend question because the decisive skills are query construction, database-driver behavior, authorization at the service boundary, database permissions, and production verification. The implementation language is incidental.

What the Interviewer Is Evaluating

The first signal is whether the candidate identifies every grammar boundary instead of saying only “use a prepared statement.” Values such as tenant IDs, email addresses, statuses, and limits should be bound as data. Table names, column names, SQL keywords, and sort directions usually cannot be supplied through an ordinary value placeholder, so they require query redesign or a server-owned allowlist that maps a small public enum to fixed SQL tokens.

The second signal is distinguishing injection prevention from authorization. A parameterized tenant_id is still unsafe for tenant isolation if it comes from the request body and the caller may choose another tenant. The service should derive the tenant from the authenticated principal and include it in every relevant query. Parameterization prevents a value from changing query structure; it does not prove that the caller may access that value.

The third signal is recognizing hidden execution boundaries. An ORM is safe only while the code uses its parameterized APIs correctly. Raw-query helpers, string-built filters, migration tools, reporting jobs, and stored procedures that execute dynamic SQL can recreate the same flaw. Data stored safely today can also become a second-order injection source if another job later concatenates it into SQL.

The final signal is layered verification. A strong answer combines code review, server-side binding, allowlisted identifiers, least-privilege database roles, safe error handling, integration tests with hostile strings, tenant-isolation assertions, and monitoring for database errors. It does not present a web application firewall or manual escaping as the primary repair.

Clarifying Questions Before Answering

• Which database and driver are deployed? Placeholder syntax, array binding, identifier-quoting utilities,

and prepared-statement behavior vary. The design principle is portable, but the exact API must match the deployed driver.

• Does the request supply tenantId? If it does, ignore that field for authorization and derive the tenant

from the authenticated server context. Administrative cross-tenant access needs a separate, explicitly authorized path.

• Which filters are optional? Optional predicates should be added from fixed SQL fragments while their

values remain parameters. A generic “append any field and operator” API greatly expands the grammar surface.

• Which sort fields are actually required? If the product needs only creation time and total amount, expose

those two public keys. Do not accept arbitrary column expressions, functions, collations, or comma-separated order clauses.

• Can the endpoint search many statuses or IDs? Use the database driver's array facility, a typed array

parameter, or a generated set of placeholders. Never join raw values into the SQL text.

• Does any ORM raw-query API appear in the path? Inspect both explicitly unsafe methods and “safe” template

helpers to confirm whether values are bound by the driver or interpolated into a string first.

• Do stored procedures construct dynamic SQL? A procedure is not automatically safe. Its parameters must

remain data when the procedure invokes SQL; dynamic EXEC-style paths need the same review.

• What privileges does the application role have? A read endpoint normally should not connect with schema

ownership, DDL, or unrelated table permissions. Separate operational and migration credentials from runtime credentials.

• What evidence is required before release? Define the hostile-input corpus, tenant-isolation checks,

raw-query inventory, database-role verification, and production error signals before changing the code.

30-Second Answer Framework

“I would make the authenticated server context the source of tenantId, then bind every data value through the database driver: tenant, email, status array, and limit. Sort columns and directions are grammar, so I would map two public enum values to fixed SQL tokens owned by the server and reject everything else. I would inventory ORM raw-query calls and stored procedures because they can reintroduce string-built SQL, and I would parameterize stored data again whenever it reaches a later execution boundary. Then I would reduce the runtime database role, return generic client errors, monitor detailed internal failures, and run integration tests proving hostile strings remain literals and can never return another tenant's rows.”

Step-by-Step Deep Dive

Step 1: Mark Code, Data, and Authorization Sources

Classify each part of the original query:

| Query part | Type | Safe source | |---|---|---| | SELECT, table, predicates | SQL grammar | Static application code | | Tenant ID | Data and authorization scope | Authenticated server context | | Email, statuses, limit | Data | Bound driver parameters | | Sort column | SQL identifier | Server allowlist | | Sort direction | SQL keyword | Server allowlist |

The unsafe example lets request-derived strings participate in parsing. Quotes, comments, operators, or additional expressions can alter the statement before the database knows which characters were supposed to be data. Checking for a few suspicious substrings does not restore the boundary because SQL has dialect-specific syntax, encodings, comments, functions, and parser behavior.

Start from every place that executes SQL, not only this endpoint. Search for raw-query APIs, template strings, string concatenation around query methods, dynamic procedure execution, reporting filters, and query builders that accept field or operator names. Trace wrappers until you can show what reaches the driver as SQL text and what reaches it as a parameter collection.

Step 2: Bind Every Value on the Server

A PostgreSQL-style TypeScript implementation can keep values separate:

const SORT_COLUMNS = {
  createdAt: "o.created_at",
  total: "o.total_cents",
} as const

const SORT_DIRECTIONS = {
  asc: "ASC",
  desc: "DESC",
} as const

interface OrderSearchInput {
  email: string | null
  statuses: string[] | null
  sort: string
  direction: string
  limit: number
}

async function findOrders(authenticatedTenantId: string, input: OrderSearchInput) {
  if (
    !Object.hasOwn(SORT_COLUMNS, input.sort) ||
    !Object.hasOwn(SORT_DIRECTIONS, input.direction)
  ) {
    throw new Error("Unsupported sort option")
  }

  const sortColumn =
    SORT_COLUMNS[input.sort as keyof typeof SORT_COLUMNS]
  const sortDirection =
    SORT_DIRECTIONS[input.direction as keyof typeof SORT_DIRECTIONS]

  const sql = `
    SELECT o.id, o.customer_email, o.status, o.total_cents, o.created_at
    FROM orders AS o
    WHERE o.tenant_id = $1
      AND ($2::text IS NULL OR o.customer_email = $2)
      AND ($3::text[] IS NULL OR o.status = ANY($3))
    ORDER BY ${sortColumn} ${sortDirection}
    LIMIT $4
  `

  return db.query(sql, [
    authenticatedTenantId,
    input.email,
    input.statuses,
    input.limit,
  ])
}

The interpolation that remains uses only values selected from constant maps after a runtime membership check. No request string becomes an identifier or keyword. The tenant, email, status array, and limit remain in the driver's parameter collection.

Validate limit as an integer within the product's allowed range before execution. That protects resource usage and API semantics. It is still bound as a parameter because business validation and code/data separation serve different purposes.

For databases without a convenient array parameter, generate placeholders from the array length and bind each element:

status IN ($3, $4, $5)
values = [tenantId, email, status1, status2, status3]

The placeholder text may be generated by trusted code; the status values must not be joined into the SQL text. Define the empty-array behavior explicitly. It may mean “no status filter” or “match nothing,” and those choices should not change accidentally with query-builder behavior.

Step 3: Keep Dynamic Grammar Small and Server-Owned

Ordinary placeholders represent values, not arbitrary identifiers or keywords. Passing "created_at" as $1 usually sorts by a string literal rather than selecting a column, and treating an untrusted identifier with a value API does not solve the product requirement.

Expose a small public vocabulary and map it to fixed internal tokens:

createdAt -> o.created_at
total     -> o.total_cents
asc       -> ASC
desc      -> DESC

Reject unknown keys. Do not pass through quoted identifiers, SQL functions, JSON paths, collations, null-order clauses, or expressions from the request. If product requirements later add a computed sort, implement that expression in server code and map one new public key to it.

An identifier-quoting helper can be appropriate when trusted administrative code genuinely needs a dynamic identifier, but quoting arbitrary user input often preserves a wider capability than the endpoint should have. For a normal API, a narrow mapping is easier to review and test.

Step 4: Preserve Tenant Isolation Independently

Derive authenticatedTenantId from the verified session, token, or service identity. Do not trust a duplicate tenant value in the JSON body, query string, or browser state. Every read and write that touches tenant-owned rows should include the authorized scope or use a repository abstraction that requires it.

The test matrix must include a valid order ID, email, or status belonging to another tenant. The query should return no such row even when all SQL values are syntactically harmless. This catches an authorization failure that injection tests alone cannot detect.

Database row-level security can add another boundary when it is configured around a trustworthy session identity and tested through connection pooling. It does not excuse missing service authorization, unsafe session-variable handling, or SQL concatenation. Treat it as an additional enforcement layer with its own configuration and tests.

Step 5: Audit ORMs, Stored Procedures, and Second-Order Paths

An ORM's normal filter API commonly binds values, but raw execution methods may offer both safe parameterized forms and explicitly unsafe string forms. Review the exact method, framework version, and driver path. A method name such as queryRaw is insufficient evidence by itself; prove whether the final SQL and values are sent separately.

Stored procedures are safe only when their inputs remain parameters. A procedure that concatenates a parameter into dynamic SQL and executes it has moved the vulnerable boundary into the database. Search procedure bodies for dynamic execution and review privileges granted to the application role.

Second-order injection occurs when hostile text is stored as ordinary data and later reused as SQL grammar. For example, an import may safely insert a report name, while a nightly report job later concatenates that name into a query. The first write can be parameterized and still leave the later execution vulnerable. Apply parameterization or a fixed mapping at every execution boundary, regardless of whether the value came directly from a request, another service, a file, or an existing database row.

Step 6: Add Defense in Depth Without Hiding the Defect

Use a runtime database role with only the required tables and operations. A read-only search service should not own the schema or have permission to drop tables, create extensions, or update unrelated data. Migrations and administration should use separate credentials. Least privilege limits damage if an injection or another application compromise survives; it does not make an unsafe query acceptable.

Input validation should enforce business types, lengths, enum membership, and ranges. It can reject malformed requests early and reduce abuse. It remains secondary because a value that passes validation can still be dangerous in the wrong SQL context, and free-form fields such as names legitimately contain punctuation.

Manual escaping is database-specific and fragile. Do not create a general escapeSql helper and concatenate its output. If a legacy path cannot be replaced immediately, isolate it, use the database vendor's documented facility, restrict privileges, add tests, and track removal. A web application firewall may provide temporary detection or virtual patching during an incident, but it cannot prove that every database execution path is safe.

Return a generic failure to the client and log a structured internal event with route, operation, deployment, and database error class. Do not echo SQL text, stack traces, connection details, or sensitive parameter values to the caller. Avoid logging secrets or full personal data while preserving enough identifiers to investigate.

Step 7: Verify the Repair at the Query Boundary

Build integration tests against the real driver and database dialect. Include strings containing quotes, comment markers, semicolons, Unicode, wildcard characters, and text that resembles SQL. The assertion is not merely “the request did not crash.” The value must be treated literally, the query shape must remain fixed, and the endpoint must return only authorized rows.

Cover at least:

1. Email text with quotes or comment-like characters remains a literal comparison.
2. Every allowed sort key produces the expected fixed ORDER BY clause.
3. Unknown sort keys, directions, statuses, and out-of-range limits are rejected before querying.
4. Empty, one-element, and many-element arrays have defined behavior and use bound values.
5. A caller cannot retrieve another tenant's row by changing any request field.
6. ORM raw-query and stored-procedure paths receive the same hostile corpus.
7. Stored strings later used by reporting or maintenance jobs remain data at the second execution boundary.
8. The runtime database role cannot perform schema changes or access unrelated tables.

Static analysis and code review can prevent new raw interpolation paths. Configure a rule or review checkpoint for unsafe raw-query methods and SQL-adjacent string construction, but keep integration tests because wrappers, generated code, procedures, and driver behavior may not be visible to a simple text search.

Step 8: Release, Observe, and Respond

Roll out with database error-rate, endpoint latency, rejected-input counts, and query-shape monitoring. A sudden rise in syntax errors, permission failures, or rejected sort keys can reveal missed clients or active probing. Do not log the full hostile payload merely to count it.

If a real injection is suspected, disable or narrow the vulnerable endpoint, preserve relevant deployment and audit evidence, rotate exposed database credentials, and determine what the runtime role could read or change. Review database logs and business records for unauthorized access, repair every equivalent execution path, and add the discovered class to the regression corpus before restoring normal access.

Completion means the unsafe grammar path is gone, tenant authorization remains enforced, the runtime role is bounded, all query executors were inventoried, and tests prove that hostile inputs remain data across both immediate and later execution.

High-Quality Sample Answer

“I would first inventory every SQL execution path used by the endpoint, including ORM raw methods and stored procedures. The original query mixes five different concerns. Tenant, email, status values, and limit are data; the sort column and direction are SQL grammar; and tenant scope is also an authorization decision.

I would derive the tenant from the authenticated server context and bind it through the driver with the email, typed status array, and bounded integer limit. I would expose only createdAt and total as sort keys and asc and desc as directions, then map them to constant SQL tokens after runtime membership checks. Unknown values would be rejected. If the driver lacks array binding, I would generate only the placeholder list and bind every element separately.

I would verify the ORM's exact raw-query API rather than assuming all ORM calls are safe. I would also inspect stored procedures for dynamic execution. Previously stored values remain untrusted when a report or batch job later builds SQL, so they must be parameterized again at that execution boundary.

For defense in depth, the search service would use a database role that can read only the required columns or views and cannot alter schema. Business validation would enforce allowed enums, lengths, and limits, while parameterization remains the injection defense. Client responses would be generic; internal logs would capture the operation and error class without SQL text or sensitive parameters.

Finally, I would run integration tests using the real driver. Quotes, comments, SQL-looking text, Unicode, and array elements must remain literal values. Tests would cover every allowed and rejected sort option, empty and large lists, ORM and procedure paths, stored-data reuse, and cross-tenant identifiers. The fix is complete only when the query structure stays fixed and no request can return another tenant's rows.”

Common Mistakes

• Parameterize only the email → tenant, list elements, limit, or another filter can still alter SQL →

bind every value and inventory the entire execution path.

• Bind the requested column name as a normal value → a value placeholder does not represent an identifier →

map a small public enum to fixed server-owned SQL tokens.

• Quote any requested identifier → the endpoint still grants callers control over a broad grammar surface →

expose only product-required sort keys and reject the rest.

• Join a validated status list into IN (...) → validation can drift and each element reenters SQL text →

use a typed array parameter or generate placeholders and bind every element.

• Take tenantId from the request because it is parameterized → code/data separation does not authorize the

selected tenant → derive tenant scope from authenticated server context.

• Assume the ORM prevents all injection → raw or unsafe methods can bypass normal binding →

verify the exact API and final driver call.

• Move string construction into a stored procedure → dynamic SQL inside the procedure remains injectable →

keep procedure inputs parameterized through the final execution.

• Sanitize only at the original write → stored hostile text can later become SQL grammar in a report job →

protect every execution boundary against second-order injection.

• Escape quotes with a custom helper → dialect, encoding, and context differences make manual escaping

fragile → use the driver's server-side parameter-binding interface.

• Grant the application owner privileges because the query is fixed → another defect or credential leak has

unnecessary blast radius → use a least-privilege runtime role and separate migration credentials.

• Return the database error and SQL for debugging → callers learn schema and query details, while logs may

expose personal data → return a generic error and record structured, redacted internal evidence.

• Test one famous payload and stop → authorization, arrays, stored procedures, alternate raw paths, and

second-order execution remain untested → verify query shape and tenant results across a varied corpus.

Follow-up Questions

Follow-up 1: Can Prepared Statements Protect Dynamic Table or Column Names?

Ordinary bind parameters represent values. They generally do not replace table names, column names, operators, or SQL keywords. Redesign the API so callers choose from a small enum, then map each allowed key to a fixed SQL fragment owned by the application. If trusted administrative tooling truly needs dynamic identifiers, use the database vendor's identifier facility and a much narrower authorization boundary; do not expose that capability through a normal user-facing search endpoint.

Follow-up 2: Is an ORM Enough to Prevent SQL Injection?

Only if the selected API preserves parameter binding through the final driver call. Normal equality and filter methods often do. Raw string methods, unsafe variants, dynamic field names, custom operators, and extensions may not. Review the deployed framework version, inspect generated SQL and values in a safe test environment, and run hostile-input integration tests. The ORM reduces opportunities for mistakes; it does not remove the need to understand its escape hatches.

Follow-up 3: What Is Second-Order SQL Injection?

The attacker-controlled value is first stored as data without changing the original statement. A later process reads that value and concatenates it into dynamic SQL, where it changes the new statement's grammar. The repair belongs at the later execution boundary: bind the stored value as data, or map it to a fixed token if it is supposed to select grammar. Treat database rows, files, queues, and internal services as untrusted sources when they influence executable SQL.

Follow-up 4: Should the Application Reject Every Quote, Semicolon, or SQL Keyword?

No. Names, search text, and other legitimate data may contain punctuation or words that resemble SQL. Business validation should enforce the actual domain type, length, format, enum, and range. Parameterization supplies the security property by making the complete value literal data. A denylist is incomplete and can also break valid input.

Follow-up 5: How Do You Handle a Large Dynamic IN List?

Use the database's typed array or table-valued parameter when available, or generate one placeholder per element and bind each value. Define a maximum list size for resource control. Very large lists may justify a temporary table, bulk-load mechanism, or a different API, but the inserted values still use bound or bulk protocol data rather than SQL string concatenation.

Follow-up 6: What Would You Do During a Confirmed Production Injection Incident?

Restrict the vulnerable path, preserve deployment, database, and application audit evidence, and rotate credentials that may have been exposed. Use the runtime role's permissions to bound the investigation, check for unauthorized reads and writes, and repair all equivalent query builders and procedures. Add the observed path to regression tests, reduce privileges where possible, and restore traffic only after the fixed query and tenant boundary are verified.