SQL Injection Prevention: Parameterized Queries, ORMs, and Defense in Depth

SQL Injection Prevention is how you keep user data from changing the shape of a query. It matters because a joined string can turn one lookup into a read or write you never planned. The database will do what the finished SQL says. You must make sure the plan stays fixed and the data stays data.

If you only filter quotes in the app, a new code path will forget the filter. Then the hole returns. Also, a strict database role limits the damage when a query bug still ships.

What goes wrong in production

The bug is string building. A handler takes a request field and pastes it into SQL text. The database parses that text as commands, not as a value. Because the parser cannot tell your intent from the pasted text, the query can grow extra clauses.

In my experience, the first incident is not a clever case. However, it is a search box or a sort field that bypassed the helper everyone else uses. As a result, one route talks to the database in raw text. You should treat raw SQL as a reviewed exception, not as a shortcut.

ORMs do not remove the risk. They move it to the raw escape hatches, to order by fields, and to people who turn the generated SQL back into strings. Still, an ORM used with bound parameters is a strong default. You should ban string format inside query calls in review.

Where the trust boundary sits

Anything from the client is data. First, that includes path parts, query strings, headers, and JSON. Next, it includes values you stored earlier and now place back into SQL.

A row that came from a user is still untrusted when you build the next query. If you bind it, the past content cannot change the statement shape.

Internal jobs can be unsafe too. A report tool that builds SQL from a saved filter is the same bug with a delay. Therefore bind parameters there as well. Do not assume a value is safe because your own worker loaded it.

Architecture and the safe path

Use a data access layer that only accepts a statement template and a parameter list. The template is fixed in code. The list holds values.

The driver sends them out of band, or the database treats them as data. If a developer cannot paste text into the template, the common bug cannot land.

A common mistake I have seen is to bind the value and still format the column name from user input. Parameters do not bind identifiers in most drivers. Specifically, a sort column must be checked against a fixed map you wrote. For example, the word date can map to a real column, and anything else is rejected.

Give the app a database role that can touch only the tables it needs. Grant read on read models. Grant insert and update only on the tables for that service.

Do not use a superuser role in the app config. Split migrate roles from runtime roles so a query bug cannot drop a table.

Roll the standard out

1. Pick one driver API that always takes parameters.
2. Add a linter rule that flags string format in query calls.
3. Replace raw call sites, starting with public routes.
4. Map every dynamic sort or filter column to an allow list.
5. Create a runtime role with grants that match the service.

When you leave one raw path for later, write the owner and the ticket on it. After the public routes are clean, move to jobs and admin tools. Although admin tools feel trusted, they often build the most dynamic SQL.

Trade-offs you should write down

Bound queries are the default. They can be slightly harder when you need optional filters. An ORM is faster to write and can hide a raw clause.

A query builder is safe only when values are bound and identifiers are allow listed. You should pick one style per service and test it.

If a report needs many optional filters, build the template from fixed clauses you select in code. Bind every user value. If you need a new column, change code, do not accept the column name from the client. Instead of a free form where clause, offer a small set of filters.

Stored procedures help when the app role can only execute them. They hurt when the procedure builds dynamic SQL from strings inside the database. Therefore review procedure bodies the same way you review app queries. A procedure is not a free pass.

Pitfalls and failure modes

Second order bugs show up when you store text and later concatenate it into SQL. The first insert looked safe because it was bound. The next job pasted the column into a new statement.

Bind on every query, including jobs. Also, search the code for the query string markers your language uses.

Like filters and search boxes tempt people to wrap user text with wildcards inside the SQL string. Keep the wildcards in the bound value, or add them in code around a bound parameter. Do not paste the user string into the pattern syntax of the statement.

Error text leaks structure. A database error that returns the full query to the client helps an attacker learn table names. Log the detail on the server.

Return a generic error to the caller. Also, watch for ORMs that put the SQL into a client visible debug page in production.

Tests that catch the regression

Add a test that passes odd punctuation as a value and expects a normal empty result or a validation error. The query plan or the logged statement should still show a placeholder, not the punctuation inside the SQL text. You do not need a destructive payload to prove the bind. You need to prove the statement shape stayed fixed.

• String format or concat used to build SQL.
• Sort or order field taken from the request.
• App role is a database owner or superuser.
• Database errors returned in full to the client.
• Raw SQL helper with no review label.

A web application firewall can block some bad traffic, and it will miss a query built in a job. Do not treat the edge as the fix. We once hit a bottleneck when a search feature switched from a bound query to a formatted string so it could splice a sort column.

The sort needed an allow list, not string pasting. The latency win was real, and the safe version was just as fast.

Unicode and encoding tricks are a reason to avoid hand built filters. Your driver and database already agree on how a bound value is encoded. A home grown escaper will drift from that. Use the driver.

A query pattern you can adapt

The snippet uses a placeholder and a parameter tuple. The SQL text does not include the user value. The sort column comes from a map, not from the request.

Replace the connection setup with your pool. Also, keep this shape when you add a filter.

# Illustrative data access. The statement text is fixed.
# User input is passed as data. Sort keys come from a map.

SORT_COLUMNS = {
    "created": "created_at",
    "status": "status",
}

def find_orders(conn, account_id, status, sort_key):
    column = SORT_COLUMNS.get(sort_key)
    if column is None:
        raise ValueError("unsupported sort")
    sql = (
        "SELECT id, status FROM orders "
        "WHERE account_id = %s AND status = %s "
        "ORDER BY " + column
    )
    # column is from SORT_COLUMNS, not from the client.
    with conn.cursor() as cur:
        cur.execute(sql, (account_id, status))
        return cur.fetchall()

Notice the order by clause uses a value you chose, not a value you were sent. If the map lookup fails, the function refuses the call. That is the safe way to handle identifiers. Values such as account id and status stay in the parameter list.

The PostgreSQL PREPARE document shows how a prepared statement separates the plan from the values. The OWASP SQL Injection Prevention Cheat Sheet lists defense in depth beyond the driver. Use both when you write a standard for the team.

Performance, scale, and cost

Bound statements are friendly to plan caches. The database sees the same text many times and can reuse a plan. Formatted SQL with raw values creates a new string per call and pollutes the cache. Therefore the safe path is also the stable path at scale.

Optional filters can explode into many statement shapes. That is fine if the number of shapes is small and each one is fixed in code. It is not fine if each user builds a new string.

Cap the filter combinations you support. Extra indexes cost disk, and they are still cheaper than an incident.

Least privilege has a small runtime cost and a real ops cost. You manage grants, passwords, and rotate secrets. Use IAM roles or a short lived database auth token when your platform supports it. Do not copy a long lived superuser password into every service.

How deep to go

App binding is mandatory. Role split is the next layer. Edge filters are a bonus.

Also, review XSS prevention so a value you store and later render cannot run as script. These are different bugs that often share the same input.

Outbound calls that build a URL from user input are a cousin problem. Read SSRF defenses if a query result becomes a fetch. The PostgreSQL libpq exec docs describe parameter passing at the protocol level if you need to confirm the driver is not concatenating.

Cost of the safe design is review time and a few linter rules. Cost of the unsafe design is a data leak and a long cleanup. Budget the linter in CI so the price stays on the pull request, not on the incident channel.

Key Takeaways

• Keep SQL text fixed and pass user values as parameters.
• Allow list column names and sort keys. Do not bind them as a trick.
• Treat ORM raw hatches as exceptions you review.
• Run the app as a database role with narrow grants.
• Bind again in jobs. Stored text is still untrusted.
• Hide raw database errors from clients.
• Lint query builders so string format cannot sneak back in.

FAQ

Do ORMs make SQL injection impossible?

No. They make the safe path easy and the unsafe path one method away. Raw SQL, extra clauses, and string format can still ship.

Review those call sites. Keep parameters on the normal path.

Can you bind a table or column name?

Usually no. Placeholders are for values. Check identifiers against a map you control.

If the name is not in the map, reject the request. Do not escape the name by hand and hope.

Is a WAF rule enough?

No. It can stop some public payloads and will miss internal builders. The query layer must be safe on its own. Use the edge as extra noise reduction, not as the control you rely on.

How should tests prove the fix?

Pass punctuation and extra words as a value. Assert the call still uses a placeholder and returns a normal result. Assert a bad sort key is rejected before the query runs. Keep that test on every data access helper.

SQL Injection Prevention is a fixed statement, bound values, and a database role that cannot do much else. Search your service for query strings built with format or concat. Replace the public ones first.

Next, add an allow list for every sort and filter column. Then split the runtime database role from the migrate role. After that, put a linter in CI so a new raw query cannot merge in silence.

Last updated on 10 September 2026.