SQL injection occurs when untrusted input changes the meaning of a database statement. The usual cause is joining a submitted value into SQL text. Parameterized queries keep the statement structure and the values separate. This reference explains placeholders, driver differences, identifier allowlists, dynamic lists, and safe testing. It includes a self-contained PHP PDO SQLite demonstration using an in-memory database, so no production credentials are needed. Parameter binding is essential at the SQL value boundary, but validation, authorization, least privilege, and private error handling still matter. A safe query can still ask the wrong business question or expose another user's records.
Understand how concatenation changes meaning
Imagine a handler building SELECT id FROM users WHERE email = ' plus submitted text plus '. A value containing quote characters can end the intended string and introduce new predicates when inserted directly into the statement. The problem is treating data as syntax, not merely allowing a suspicious word. Blocking a list of SQL keywords is unreliable because valid text can contain those words and attacks have many representations. Parameterization sends the value under the driver's binding protocol or supported safe preparation behavior. The database interprets it as a value for the placeholder, preserving the statement's structure. Do not manually add SQL quotes around a placeholder; the API handles the value representation.
Use the placeholder syntax your driver supports
Different drivers use different markers: PostgreSQL's native parameter API commonly uses $1 and $2, some APIs use question marks, and PDO supports its documented named or positional styles according to driver behavior. SQL Server clients have their own parameter naming and type rules. A placeholder is meaningful only when used with the relevant API; pasting SELECT ... WHERE id = ? into a generic SQL console without binding is not a completed example. Bind integer, decimal, string, binary, and null values according to the schema and driver contract. A date string still needs application validation. Native and emulated prepare settings can affect details, so choose an explicit supported policy and test it rather than relying on a tutorial's assumptions.
Keep identifiers and query structure controlled
A parameter generally represents a value, not a table name, column name, sort direction, or arbitrary WHERE fragment. If users choose a sort option, map a small public choice to a reviewed ORDER BY expression. For example, name maps to ORDER BY name, id and newest maps to ORDER BY created_at DESC, id DESC. Reject unsupported choices or use a documented safe default. Do not concatenate an entire submitted query fragment after binding one other field and call the query safe. Identifiers sometimes need dialect-specific quoting, but allowlisting is simpler when the application has a finite set. Keep the SQL structure owned by the application.
Build dynamic lists and patterns deliberately
One placeholder cannot stand for a comma-separated IN list as SQL structure. Generate one placeholder per validated value in a bounded list and bind each separately. Handle an empty list before building the query. LIKE patterns have their own grammar: bound percent and underscore still act as wildcards unless you escape them under an explicit escape policy. Decide whether the feature permits patterns or promises literal search text. LIMIT and OFFSET may require explicit integer binding according to the driver. These are correctness and resource questions as well as injection prevention. Keep maximum list size, term length, and page size under server control. A prepared statement can still become an expensive query.
Combine binding with the other trust boundaries
Validate the field's shape and business meaning before use. Authorize the requested record using trusted user or tenant identity rather than a submitted ownership claim. Use a least-privilege runtime account so an unexpected flaw has less power. Database constraints protect uniqueness and relationships under concurrency. Escape results for their output destination, such as HTML text; preparation does not prevent cross-site scripting. Keep raw SQL exceptions and credentials out of public responses. Stored procedures are safe only when their own SQL construction respects these principles; a procedure that concatenates untrusted dynamic SQL can still be vulnerable. Review the entire path instead of searching only for prepare().
Test the boundary without attacking live systems
Use a disposable local database and fixed fixtures. Test ordinary input, apostrophes such as O'Reilly, an injection-looking string, null where allowed, and invalid shapes rejected by the validator. Verify that the injection-looking value matches only literal data, not every row. Include unauthorized IDs to ensure permission checks are independent of SQL syntax safety. Do not run destructive payloads against production or systems you do not control. Unit tests can inspect the application contract, while integration tests prove the selected driver and database behave as expected. Record runtime and driver assumptions. A passing example establishes its specific boundary behavior, not a guarantee every query elsewhere in the application is safe.
Practice task and worked solution
Rewrite a product-search handler that joins submitted name and sort text into SQL. The solution validates a scalar bounded name, binds the search value, and maps sort choices to fixed reviewed expressions. If the feature promises literal LIKE search, escape wildcard characters separately from binding. Add a tenant predicate using trusted authenticated state and cap the returned rows. Test an apostrophe, an injection-looking value, an unsupported sort choice, an empty term, and another tenant's record. Verify the result text is escaped in HTML. Explain why a stored procedure using the same unsafe concatenation would not automatically solve the flaw.
Continue learning
Review database security, WHERE, and row changes.
The PostgreSQL parameter-execution documentation describes values separate from SQL; PDO prepare documents its placeholder contract.
Parameterized Queries and SQL Injection Prevention Examples
Each example shows the code, the output it produces and what each part does.
Treat injection-looking text as a value
This PHP CLI example uses PDO SQLite with :memory:, creates only an ephemeral fixture, and requires pdo_sqlite. It shows a complete binding API instead of an unbound placeholder.
The submitted string is compared literally with the fixture's email values, so it matches neither account. No persistent database file or external connection is created. The browser does not execute this PHP source.
<?php
$pdo = new PDO('sqlite::memory:', null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL)');
$seed = $pdo->prepare('INSERT INTO users (id, email) VALUES (?, ?)');
$seed->execute([1, 'ada@example.com']);
$seed->execute([2, 'sam@example.com']);
$submitted = "' OR 1=1 --";
$stmt = $pdo->prepare('SELECT id FROM users WHERE email = ?');
$stmt->execute([$submitted]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo 'Matches: ' . count($rows) . PHP_EOL;Matches: 0Parameterized Queries and SQL Injection Prevention FAQ
Do I need manual SQL escaping with bound values?
No. Use the driver's binding contract: the value is sent separately from the SQL text, so quotes inside it can't break the query. Escaping it yourself as well would corrupt the stored value, for example by doubling apostrophes, so don't double-process them blindly.
Can I bind a column name?
No. Placeholders represent values, not identifiers. Use an allowlist for structure: map the user's choice, such as a sort option, to a column name your code already knows, and refuse anything else.
Does binding make percent literal?
No. Binding stops injection, but LIKE semantics remain separate: % and _ in the bound value still act as wildcards. Escape them in the search text and declare an ESCAPE character if users should be able to search for them literally.
Are stored procedures automatically safe?
No. A procedure is safe only if its internal SQL construction is also safe. One that concatenates its parameters into dynamic SQL and executes it is just as vulnerable as application code that does the same.
Do parameterized queries improve performance?
Sometimes. A prepared statement can be parsed and planned once and executed many times with different values, which helps in loops. Many drivers prepare statements automatically. The main reason to use them is still safety, not speed.
Parameterized Queries and SQL Injection Prevention Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
A bound LIKE value contains %. What must be decided?
Parameterized Queries and SQL Injection Prevention Quiz
2 questions to check what you learned.
What is the essential parameterization boundary?
- Trimming input and joining it into quoted SQL text
- Removing a few suspicious keywords before concatenation
- SQL structure is separate from supplied values
- HTML-escaping the input and then inserting it into SQL
What should handle a user-selected sort column?
- A normal value placeholder used as a column identifier
- Raw submitted text concatenated into ORDER BY
- A stored procedure that concatenates the same unchecked sort text
- An application-owned allowlist of SQL identifiers