Prepared Statements and SQL Injection

Analogy categoryAnalogies

SQL injection occurs when untrusted input is concatenated directly into a SQL query string, allowing an attacker to alter the query's meaning. If a login query is built as "SELECT * FROM users WHERE email = '" . $email . "'", an attacker submitting an email value of x' OR '1'='1 turns the WHERE clause into something always true, potentially bypassing authentication entirely. Prepared statements solve this by strictly separating the SQL command's structure from the data supplied to it, so user input can never be reinterpreted as SQL syntax.

🏏

Cricket analogy: Concatenating raw input into a query is like letting a scorer accept any handwritten note as an official result; an attacker submitting x' OR '1'='1 is like slipping in a forged scorecard reading 'match abandoned, all results void', overturning the real outcome.

How prepared statements prevent injection

With a prepared statement, you first send the query template containing placeholders (either ? for positional parameters or :name for named parameters) to the database, which parses and compiles it. Only afterward do you bind actual values to those placeholders and execute. Because the database already knows the query's structure before it ever sees the data, a value like x' OR '1'='1 is treated purely as a literal string to compare against, not as SQL code — there is no possible input that can change the query's shape.

🏏

Cricket analogy: A prepared statement sending the query template with placeholders first is like the umpire fixing the match rules (overs, powerplay boundaries) before the toss, so no matter what a captain later chooses (:name), it's just a decision within fixed rules, never a rewrite of the rulebook.

php

<?php
declare(strict_types=1);

// VULNERABLE — never do this:
$unsafeSql = "SELECT * FROM users WHERE email = '" . $_POST['email'] . "'";

// SAFE — parameters are bound, not concatenated:
$stmt = $pdo->prepare('SELECT id, email, password_hash FROM users WHERE email = :email');
$stmt->execute(['email' => $_POST['email']]);
$user = $stmt->fetch();

if ($user !== false && password_verify($_POST['password'], $user['password_hash'])) {
    session_regenerate_id(true);
    $_SESSION['user_id'] = $user['id'];
}

// Positional placeholders work the same way
$stmt = $pdo->prepare('UPDATE users SET last_login = NOW() WHERE id = ?');
$stmt->execute([$user['id']]);

What prepared statements do NOT protect against

Placeholders can only stand in for data values — not for identifiers like table names, column names, or SQL keywords such as ASC/DESC. If your application needs a dynamic sort column from user input, you cannot bind it as a parameter; instead validate it against a strict allowlist of known-safe column names before interpolating it into the query string. Prepared statements also do nothing to protect against injection in other layers, such as unsanitized input used to build shell commands or file paths.

🏏

Cricket analogy: Placeholders standing in only for data, not identifiers, is like a scorer being able to fill in a player's run total via a form field, but never being allowed to rename 'overs' to 'innings' through that same field; a dynamic sort column needs an allowlist, like only permitting sorting by 'runs' or 'wickets', nothing else.

A common mistake is assuming that escaping input (e.g. with addslashes() or mysqli_real_escape_string()) is an equivalent alternative to prepared statements. Escaping is error-prone, context-dependent, and has historically been bypassed via encoding tricks; parameterized queries are structurally immune to injection and should always be preferred over manual escaping.

Binding types and bindValue vs bindParam

PDOStatement::bindValue() binds a specific value immediately, while bindParam() binds a variable by reference, meaning the value is only read at execute() time — useful when the variable's value changes inside a loop before each execution. Both accept an optional PDO::PARAM_* type constant (PARAM_INT, PARAM_STR, PARAM_BOOL, etc.) to hint the underlying data type to the driver, though passing an associative array directly to execute() is the most common and readable approach for straightforward queries.

🏏

Cricket analogy: bindValue() capturing a run count immediately is like recording a batter's score the moment it's confirmed, while bindParam() binding by reference is like a scorer tracking a running partnership total that keeps updating in a loop until the moment it's finally submitted to the scoreboard at execute() time.

Prepared statements offer a secondary performance benefit: when the same query template is executed repeatedly with different parameters (e.g. inserting many rows), the database can reuse its parsed and optimized execution plan instead of re-parsing the SQL text each time.

SQL injection happens when untrusted input is concatenated into a query string and reinterpreted as SQL syntax.
Prepared statements separate the query structure from the data, sent to the database in two steps.
Placeholders can only bind data values, never identifiers like table or column names — use an allowlist for those.
bindValue() binds immediately; bindParam() binds by reference and reads the value at execute() time.
Escaping functions are not an equivalent substitute for parameterized queries.
Reused prepared statement templates can also improve performance via query plan reuse.

Practice what you learned

1. What fundamentally makes prepared statements immune to SQL injection for bound values?

2. Can a prepared statement placeholder be used to parameterize a table name chosen dynamically from user input?

3. What is the key difference between bindParam() and bindValue()?

4. Why is addslashes() or manual string escaping considered an inferior alternative to prepared statements?

5. In the vulnerable query "SELECT * FROM users WHERE email = '" . $_POST['email'] . "'", what could an attacker submit to bypass the WHERE clause?

Was this page helpful?

PDO Database Basics

Working with JSON in PHP

Related Study Notes in Programming

Browse all study notes

Programming

Apache Spark Study Notes

Programming · 30 topics

Programming

Apache Flink Study Notes

Programming · 30 topics

Programming

Hadoop Study Notes

Programming · 30 topics

Programming

Snowflake Study Notes

Programming · 30 topics

Programming

Apache Airflow Study Notes

Programming · 30 topics

Programming

dbt (Data Build Tool) Study Notes

Programming · 30 topics

Topics covered

#PHP#PHPProgrammingStudyNotes#Programming#PreparedStatementsAndSQLInjection#Prepared#Statements#SQL#Injection#StudyNotes#SkillVeris

Prepared Statements and SQL Injection