SQL Injection Prevention

Everything in this lesson traces back to one idea: user-supplied data must never be interpreted as part of the SQL statement's own structure. That separation has to happen at the exact moment the query is built not fixed up beforehand by cleaning the input, and not fixed up afterward by catching bad results.

This is why parameterized queries, not input validation, are the primary defense covered in this lesson. Validation happens before the query is built and can be bypassed or incomplete. Parameterization changes how the query itself is constructed, so there's structurally nothing for malicious input to hijack.

No single technique here is a complete solution by itself, which is why the lesson closes on Defense in Depth the real-world answer is layering several of these together, not picking the "best" one.

Instead of building a query by inserting values directly into the string, you write the query with placeholders and hand the actual values to the database separately:

php

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
$stmt->execute([$username]);

Or with named placeholders, which read a bit more clearly with multiple values:

php

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND status = :status");
$stmt->execute(['username' => $username, 'status' => $status]);

Java

String sql = "SELECT * FROM users WHERE username = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, username);
ResultSet rs = stmt.executeQuery();

With multiple values

String sql = """
    SELECT * FROM users
    WHERE username = ?
    AND status = ?
    """;
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, username);
stmt.setString(2, status);
ResultSet rs = stmt.executeQuery();

The critical difference from the vulnerable pattern covered earlier in this course ("...WHERE name = '" . $input . "'") is that $username here is never woven into the query text at all it travels to the database as a distinct piece of data, bound to the ? placeholder after the query's structure is already fixed. Even if $username is admin' OR '1'='1' -- , it's treated as a literal string being compared against the username column the quotes, the OR, the -- are just characters in a string value, not SQL syntax.

Parameterized queries are the development practice of separating SQL statements from their input values. Prepared statements are the mechanism used by database drivers and database systems to implement this separation.

The process can be understood in two phases:

1. Prepare:
The SQL statement is sent with placeholders instead of actual values. The database prepares the structure of the statement, including the tables, columns, and query logic, without treating the parameter values as SQL syntax.

2. Execute:
The parameter values are supplied separately and bound to the placeholders. They are treated as data, rather than being interpreted as part of the SQL statement.

The key security benefit comes from maintaining this separation between SQL code and data. User-supplied values are not incorporated into the SQL statement and then re-parsed as SQL syntax.

Prepare the SQL structure first → bind the values separately → execute the statement

This is the broader category prepared statements live inside: Any database access library that treats parameter binding as a first-class feature rather than an afterthought.

In PHP, that's PDO and mysqli (used with bound parameters, not its older string-interpolation-style functions). Other ecosystems have their own equivalents: JDBC's PreparedStatement in Java, psycopg2 with parameterized queries in Python, and so on.

The common thread across all of them: the API gives you a way to separate the query structure from the data, and using that separation is what makes the API "safe" it's a property of how you use the library, not just which library you picked.

An ORM (object-relational mapper) is a common, high-level example of a safe database API. Laravel's Eloquent is example ORM, and its query-building methods use parameter binding internally by default:

php

User::where('username', $username)->first();

Under the hood, this compiles to a parameterized query exactly like the raw PDO example above you get the safety without writing SQL directly at all.

Checking that a value matches what's actually expected the right type, a same length, an expected format before it's used anywhere. A numeric ID field should reject "abc" outright. An email field should reject something with no @. This is a genuinely useful layer, but its job is catching malformed or unexpected input, not making an unsafe query safe.

Validation and parameterization solve different problems, and the lesson later covers exactly why validation can't substitute for the other.

The strongest form of validation: Instead of trying to enumerate and block every dangerous character or pattern (a deny-list), you define the complete set of acceptable values and reject anything outside it. This matters most in places parameterized queries can't help. Parameter binding protects values, but things like column names or sort direction in an ORDER BY clause aren't values, they're structure, and can't be bound as parameters at all. If a feature lets users choose a sort column, the safe approach is checking it against a fixed list:

php

$allowedColumns = ['name', 'created_at', 'price'];
$sortColumn = in_array($request->sort, $allowedColumns, true) ? $request->sort : 'name';

Anything not explicitly on the list is rejected, which is why allow-lists are much harder to bypass than deny-lists: there's no clever encoding or edge case to find, because the default is "no."

There are two important limitations to relying on input validation alone.

First, validation can be incomplete or bypassed. It is easy to overlook an unexpected input case, and attackers actively look for cases that validation rules do not anticipate.

Second, some legitimate input can contain characters that are meaningful to SQL syntax. For example, a person's last name may be O'Brien. Rejecting the value simply because it contains a single quote would incorrectly prevent legitimate input.

Validation controls what input is accepted; parameterized queries control how that input is interpreted when used in SQL.

Therefore, input validation can be useful for enforcing business rules and expected input formats, but it should not be treated as the primary defense against SQL injection. Parameterized queries/prepared statements provide the necessary separation between SQL code and data.

Escaping is the practice of modifying special characters in user input so that they are treated as literal data rather than SQL syntax when incorporated into a SQL statement.

For example, a single quote (') may have special meaning inside a SQL string. An escaping mechanism can transform it so that the database interprets it as part of the value rather than as the end of the SQL string.

Escaping has several important limitations:

First, it can be applied inconsistently. In a large application, developers may forget to escape one input field or use the wrong escaping mechanism for a particular database or context. A single unprotected input can leave the application vulnerable.

Second, escaping depends on the database engine, character encoding, and implementation. Historical vulnerabilities have demonstrated that incorrect handling of character encodings can sometimes undermine escaping mechanisms. For example, older MySQL configurations using certain multibyte character sets such as GBK had known escaping-related vulnerabilities.

Third, escaping only addresses values. It does not provide a general solution for SQL elements such as table names, column names, or ORDER BY direction, because these are part of the SQL statement's structure rather than ordinary string values.

Every application should connect to the database using an account with only the privileges required for the application's normal operations. The application should not use highly privileged accounts such as root or an account with unnecessary GRANT ALL privileges.

For example, if an application only needs to read and modify records in its own database, its account might be granted:


sql
GRANT SELECT, INSERT, UPDATE
ON security_learning.*
TO 'app_user'@'localhost';

If the application does not need to delete records, DELETE should not be granted. Similarly, the application account should not have unnecessary privileges such as DROP, nor should it have access to unrelated databases.

The benefit becomes especially important when another security control has already failed. If an SQL injection vulnerability is exploited, a least-privileged database account limits the operations available through that compromised application.

For example, an attacker may be able to access data that the application account can access, but may be prevented from performing operations such as dropping tables or accessing unrelated databases.

They may be able to perform only those database operations that the compromised account is authorized to perform.

Never let a raw database error message reach the end user, this is precisely the mechanism Error-based SQL injection depends on, since a leaked error can reveal table structure, column names, or even data.

The fix is straightforward: log the full error server-side where you can actually debug it, and show the user something generic instead. In Laravel terms, that's APP_DEBUG=false in production which this project already has covered — plus custom error views so users see a friendly message instead of a stack trace when something does go wrong.

Rather than letting raw SQL or ad-hoc query-building logic get written wherever a controller happens to need data, keep it in one place — models, a repository layer, or (as this project already does) an ORM's query builder used consistently throughout. The value here isn't stylistic: when there's exactly one place responsible for talking to the database, that's the one place you need to review and enforce safe patterns in. When query logic is scattered across dozens of controllers, securing the app means catching every single instance — miss one, and that's your vulnerability.

test

When reviewing an application for SQL injection vulnerabilities, look for the following patterns:

String concatenation or interpolation inside SQL statements, such as:

"SELECT * FROM users WHERE name = '" . $name . "'"

Raw SQL methods used with untrusted input, such as:

DB::raw()
whereRaw()
DB::statement()

Dynamically constructed table or column names that are not restricted using an appropriate allow-list.
Excessively privileged database credentials, such as an application account with unnecessary administrative privileges.
Detailed database errors exposed to users, which may disclose information useful for identifying or exploiting SQL injection vulnerabilities.

The goal of code review is not simply to find raw SQL. It is to determine whether untrusted data can influence SQL structure rather than being safely treated as data.

A quick-reference pairing of what to avoid and its safe equivalent, pulling together patterns from earlier in this lesson:

Vulnerable string concatenation: "SELECT * FROM users WHERE name = '" . $input . "'"

Secure parameter binding: $pdo->prepare("SELECT * FROM users WHERE name = ?")->execute([$input])

Vulnerable user input controlling sort order directly: "...ORDER BY " . $request->sort

Secure validated against an allow-list: $sortColumn = in_array($request->sort, $allowedColumns, true) ? $request->sort : 'name';

Vulnerable raw SQL execution with concatenated input in an ORM: DB::raw("WHERE name = '" . $input . "'")

Secure the ORM's own parameter-bound methods: User::where('name', $input)->get()

No single security control is sufficient on its own. Defense in depth means deliberately combining multiple security controls so that the failure of one control does not automatically result in a complete compromise.

Consider a situation where a developer accidentally introduces a raw, concatenated SQL query that is missed during code review.

Parameterized queries are the primary control that should prevent the SQL injection.
Least-privileged database accounts can limit what an attacker can do if an injection vulnerability is successfully exploited.
Secure error handling can prevent detailed database errors from being exposed, reducing information available to an attacker.
Allow-list validation can restrict values for structural elements such as column names or sort directions where parameterization cannot be used.

These controls do not replace parameterized queries. For example, least privilege does not make a vulnerable query safe, and error handling does not prevent SQL injection.

Each layer addresses a different part of the risk.

After applying a fix, re-run the exact payloads that used to work the same ' OR '1'='1'-- or timing-based tests from earlier in this course and confirm they no longer succeed, rather than assuming the fix is correct because the code looks right.

It's also worth checking the rest of the codebase for the same unsafe pattern; if one query was built with string concatenation, there's a real chance the same mistake exists elsewhere, written by the same habit fixing the one you found doesn't mean the underlying practice has actually changed everywhere it was used.