Parameterized query

Also known as: prepared statement

On this page 7

A parameterized query is a database statement in which user-supplied values are passed separately from the SQL text through placeholders, so the database engine always treats them as data and never as executable code. In database security, it is the primary defense against SQL injection, and it is often implemented as a prepared statement that the server parses once and executes many times.

Instead of building a string such as a query concatenated with a typed name, the application sends a fixed template like SELECT * FROM users WHERE email = ? and binds the value in a separate step. The structure of the statement is locked before any untrusted input arrives.

How a parameterized query blocks injection

A parameterized query blocks injection because the database parses the statement structure before it sees any input. Once the parse tree is fixed, a bound value can only fill a value slot; quotes, semicolons or keywords inside it remain literal characters and cannot alter the command.

The process normally has three phases. In the prepare phase, the client sends the template and the server parses, validates and plans it. In the bind phase, the client supplies typed values for each placeholder. In the execute phase, the server runs the plan with those values and returns results. Many drivers combine these steps transparently, and some perform client-side parameter substitution with correct escaping instead of true server-side preparation.

The table below contrasts what happens to a hostile input under each approach.

ApproachWhat the engine receivesResult
String concatenationAltered SQL with an extra OR conditionReturns every row
Manual escapingEscaped literal, if escaping is correctFragile, encoding-dependent
Parameterized queryFixed template plus one string valueSearches for that exact text
Handling of the input x’ OR ‘1’=’1

Placeholder syntax and binding styles

Placeholder syntax varies by database and driver, but the principle is identical: a marker stands for a value that is bound later. Using the wrong style usually causes a syntax error rather than a security flaw.

StyleExampleWhere it appears
Positional question markWHERE id = ?JDBC, ODBC, SQLite, MySQL
Numbered positionalWHERE id = $1PostgreSQL protocol and drivers
Named colonWHERE id = :idOracle, many ORMs, Python drivers
Named at-signWHERE id = @idSQL Server and .NET providers
Common placeholder styles

Typed binding adds a second benefit. When the driver declares a parameter as an integer, date or binary value, the engine performs no string-to-type guessing, which avoids subtle conversion errors and keeps index use predictable.

What parameters cannot protect

Parameters protect values only; they cannot stand in for identifiers or SQL keywords. Table names, column names, sort directions and operators must be part of the template, so any dynamic choice among them needs a different safeguard.

Dynamic partCan it be a parameterSafe technique
Filter valueYesBind as parameter
Column in ORDER BYNoAllow-list mapped to fixed names
Sort directionNoMap input to ASC or DESC only
Table nameNoAllow-list or separate query per table
IN list of valuesYes, per elementGenerate one placeholder per item
LIKE patternYesBind value and escape wildcard characters
Dynamic query parts and safe handling

A second gap is dynamic SQL inside stored procedures. A procedure called with parameters can still be vulnerable if it concatenates those parameters into a string and runs it with EXEC or EXECUTE IMMEDIATE. The same separation must be applied inside the procedure, using constructs such as sp_executesql with parameters or USING clauses.

A parameterized query is a code-level control that removes the root cause of SQL injection, while other database security measures limit the damage if a flaw slips through. Effective designs combine them as defense in depth.

ControlLayerWhat it adds
Parameterized queryApplication codePrevents injected code from executing
Database privilegeDatabase engineRestricts what a compromised account can do
Database firewallNetwork or proxyDetects anomalous statements
Input validationApplication codeRejects malformed data early
Database activity monitoringOperationsRecords and alerts on suspicious use
Relationship to neighboring controls

Document stores face the analogous risk known as NoSQL injection, where operator objects in JSON input can alter a query. The equivalent remedy is to build query objects from typed values and reject input that contains operator keys, following the same separation principle.

Performance effects and common mistakes

Server-side prepared statements can improve performance because parsing and planning happen once, after which repeated executions reuse the cached plan. The benefit is largest for short statements executed thousands of times per second.

Plan reuse has a known drawback called parameter sniffing: a plan optimized for the first bound value may perform badly for values with very different selectivity. Engines mitigate this with custom versus generic plan heuristics, recompilation hints or adaptive plans.

  • Formatting values into the template before preparing it, which defeats the protection entirely.
  • Trusting ORM raw-query methods without checking that they bind values instead of interpolating them.
  • Escaping in addition to binding, which stores doubled quotes or backslashes as data.
  • Assuming internal data is safe, ignoring second-order injection from values stored earlier.
  • Leaking prepared statements in long-lived connections, exhausting server memory.

Understanding it simply

Think of a paper form with a fixed question and an empty box for the answer. Whatever a person writes in the box stays an answer; it can never change the question printed above it. A parameterized query works the same way: the program writes the question in advance and the visitor only fills in the box.

Without this separation, a clever visitor could write words that the database mistakes for new instructions, such as show every customer record. With it, the input is always just data, which is why developers treat it as the first rule of safe database code.

Applied in practice

A regional online pharmacy runs an order-lookup page where customers type an order number and postal code. An external penetration test finds that the page builds its SQL by joining strings, and the tester retrieves other customers’ orders by entering a crafted postal code containing a quote and an OR condition.

The development team rewrites 37 data-access functions to use parameterized queries with positional placeholders, replaces a dynamic sort feature with an allow-list of four column names and reduces the web account to read and insert privileges on three tables. A retest one week later shows that every injection attempt is now treated as a literal search string and returns no rows. As a side effect, the busiest lookup query runs about 20 percent faster because the database reuses one cached execution plan.