One stray apostrophe in a login form can hand an attacker your entire user table. SQL injection has been near the top of every serious vulnerability list for two decades, and the reason it survives is simple: the broken pattern is easy to write and the fix is easy to skip. This guide explains how it works and, more importantly, how to shut it down for good.
This is an educational and defensive walkthrough. Every vulnerable snippet below exists only so you can recognize the shape of the bug in your own code and replace it with a safe version. We will build a classic vulnerable login, watch how a single crafted string subverts it, and then work through the full stack of defenses, from the one fix that matters most to the layers you add around it. The examples use PHP and SQL because that pairing is where so much of this vulnerability was born, but the principles transfer to every language and every database.
What this guide covers
- What SQL injection is
- How it works: a vulnerable login
- The main types of injection
- The real impact of a breach
- Prevention: parameterized queries
- ORMs, query builders, and stored procedures
- Defense in depth: privileges, validation, WAF
- Escaping as a last resort
- The prevention checklist
- A step by step hardening plan
What SQL injection is
SQL injection happens when data supplied by a user is glued directly into a SQL statement, so that the user can change what the statement means. The database cannot tell the difference between the SQL your developer wrote and the SQL an attacker smuggled in through a form field. Both arrive as one string, and the engine faithfully runs all of it. The core sin is mixing two things that must stay separate: the fixed structure of a query (the code) and the values it operates on (the data).
The vulnerability is so common because the wrong way is often the most natural way to write it. Building a string with your language's normal concatenation operator feels obvious, reads cleanly, and works perfectly in every test where the input is polite. It only breaks when someone hostile supplies input designed to break out of the data and into the code. According to the OWASP community page on SQL injection, injection flaws remain one of the most prevalent and damaging classes of web application vulnerability, precisely because the pattern is easy to introduce and the payoff for attackers is enormous.
The one rule: data must never become code. Every prevention technique in this guide is a different way of enforcing that single sentence. If you remember nothing else, remember that user input belongs in a parameter, never in the text of the query. Our SQL security guide expands on the same idea across other threats.
How it works: a vulnerable login
Consider the most common piece of code on the web: a login form that checks an email and password against a users table. Here is the version that has leaked more databases than any other, written the obvious way with string concatenation.
// PHP: user input concatenated straight into SQL
$email = $_POST['email'];
$pass = $_POST['password'];
$sql = "SELECT * FROM users
WHERE email = '" . $email . "'
AND password = '" . $pass . "'";
$result = $conn->query($sql); // runs whatever the string now containsWith a normal email such as alice@example.com the query does exactly what you expect. Now watch what happens when an attacker types this into the email field instead:
// entered into the email field:
' OR '1'='1' --After concatenation, the string the database actually receives is no longer a lookup. The trailing -- comments out the password check entirely, and the OR makes the condition true for every row.
SELECT * FROM users
WHERE email = '' OR '1'='1' --'
AND password = '...'The condition '1'='1' is always true, so the query returns the first user in the table, often the administrator. The attacker is now logged in without a password. That is authentication bypass, and it took a single line of input. The same trick, extended, can read arbitrary tables, dump every password hash, or in the worst case run administrative commands on the host. The Wikipedia overview of SQL injection catalogs many real breaches that started exactly this way.
The main types of injection
Attackers do not always get a tidy error message or a visible result set to work with. The techniques are grouped by how the attacker gets information back out of the database. Knowing the categories helps you understand that even a query with no visible output can still leak data.
| Type | How it works | Signal the attacker reads |
|---|---|---|
| In band (classic) | Payload and stolen data travel over the same channel, often shown directly on the page. | Data appears in the page or an error message. |
| Union based | A UNION SELECT appends attacker-chosen columns to the legitimate result set. | Extra rows rendered alongside real results. |
| Error based | Deliberately malformed input forces the database to reveal data inside its error text. | Verbose database error messages. |
| Blind (boolean) | No data returns, but true and false conditions change the page, letting the attacker ask yes or no questions. | Different page behavior per condition. |
| Blind (time based) | The payload tells the database to sleep when a condition is true; the attacker times the response. | Slow vs fast response. |
The important takeaway is that hiding error messages and result sets is not a fix. Blind injection extracts data one bit at a time from an application that shows the attacker nothing but a slightly slower page. The only reliable defense is to stop the untrusted string from being parsed as SQL in the first place, which is what the prevention sections cover.
The real impact of a breach
It is tempting to treat injection as a theoretical bug because the demo looks like a party trick. The consequences are anything but theoretical. Because the application account usually has broad access to its own database, a successful injection often means the attacker can do everything the application can do, and sometimes more.
- Data theft. The whole point of a database is that it holds valuable data. Union and blind techniques let an attacker read customer records, email addresses, password hashes, payment tokens, and anything else the account can select.
- Authentication bypass. As the login example showed, injection can skip the password check and grant an attacker administrative access to the application itself.
- Data loss and tampering. Injection is not read only. An attacker can run UPDATE and DELETE statements, corrupt records, or drop tables, turning a data breach into a data disaster.
- Full server compromise. On some configurations, database features that read files or run shell commands turn a query flaw into a foothold on the operating system.
Beyond the technical damage sit the regulatory fines, breach notification costs, and reputational harm that follow a leak of personal data. A single unparameterized query can be the most expensive line of code your team ever ships. That is why injection is treated as a critical severity defect in every serious SQL code review.
Prevention: parameterized queries
There is a single fix that stops the overwhelming majority of injection, and everything else is defense in depth around it. That fix is the parameterized query, also called a prepared statement. Instead of building one string that contains both the query and the values, you send the database a query template with placeholders, then send the values separately. The driver guarantees that a value is only ever treated as data, no matter what characters it contains. An apostrophe, a semicolon, a DROP TABLE string: all of them are just literal text in a parameter.
Here is the vulnerable login rewritten with PDO prepared statements. The values never touch the SQL string.
// PHP with PDO: placeholders keep data out of the SQL
$sql = 'SELECT id, email, password_hash
FROM users WHERE email = :email';
$stmt = $pdo->prepare($sql);
$stmt->execute([':email' => $_POST['email']]);
$user = $stmt->fetch();
// verify the password hash in PHP, never in SQL
if ($user && password_verify($_POST['password'], $user['password_hash'])) {
// authenticated
}Now the malicious ' OR '1'='1 input is looked up as a literal email address. No such user exists, so login fails, exactly as it should. The same approach works with MySQLi bound parameters, and with positional placeholders if you prefer them.
// PHP with MySQLi: bind values by type
$stmt = $conn->prepare('SELECT id FROM users WHERE email = ? AND status = ?');
$stmt->bind_param('si', $email, $status); // s = string, i = int
$stmt->execute();
$stmt->bind_result($id);One caveat that trips people up: placeholders can only stand in for values, not for identifiers such as table names, column names, or the direction of an ORDER BY. Those cannot be parameterized, so if any part of your query structure comes from user input, you must validate it against an allow-list of known-safe names rather than passing it through.
// sort column comes from the URL: injectable, cannot be parameterized
$sql = "SELECT * FROM products ORDER BY " . $_GET['sort'];// map the untrusted key to a known-safe column name
$allowed = ['price' => 'price', 'name' => 'name'];
$col = $allowed[$_GET['sort']] ?? 'name';
$sql = "SELECT id, name, price FROM products ORDER BY " . $col;The definitive reference for getting this right in any language is the OWASP SQL Injection Prevention Cheat Sheet, which lays out the same priority order: parameterize first, and treat everything else as reinforcement.
ORMs, query builders, and stored procedures
Most teams do not write raw SQL for every query. Object relational mappers and query builders generate parameterized SQL for you, which is why they are safe by default. The danger is that almost every one of them also offers a raw escape hatch, and that hatch is where injection sneaks back in.
// Query builder raw fragment with concatenated input
$users = DB::table('users')
->whereRaw("email = '" . $email . "'")
->get();// Let the builder bind the value for you
$users = DB::table('users')
->where('email', $email)
->get();
// or, if you must use raw, pass bindings separately
$users = DB::table('users')
->whereRaw('email = ?', [$email])
->get();The rule with any ORM is the same as with raw drivers: never build a fragment by concatenating input, always hand the value to the library as a binding. When your team uses an ORM well, injection becomes very hard to write by accident, which is one reason we recommend them in our SQL best practices guide.
Stored procedures, used carefully
Stored procedures are often described as an injection defense, and they can be, but only if they use parameters internally. A procedure that builds and runs dynamic SQL by concatenating its own arguments is exactly as vulnerable as any other concatenated query. The protection comes from parameterization, not from the fact that the code lives in a procedure.
-- Dynamic SQL built from the argument: still injectable
CREATE PROCEDURE find_user(IN p_email VARCHAR(255))
BEGIN
SET @q = CONCAT('SELECT * FROM users WHERE email = ''', p_email, '''');
PREPARE stmt FROM @q; EXECUTE stmt;
END;-- Parameterized body: the value is bound, never concatenated
CREATE PROCEDURE find_user(IN p_email VARCHAR(255))
BEGIN
SELECT id, email FROM users WHERE email = p_email;
END;Defense in depth: privileges, validation, WAF
Parameterized queries stop injection. The layers below reduce the damage if a flaw slips through anyway, whether from a forgotten raw query, a third party library, or a novel bug. No single layer here is a substitute for parameterization; together they shrink the blast radius.
Least privilege database accounts
An injection is far more dangerous when the application connects as a database superuser. If the account can only read and write the handful of tables the feature needs, an attacker who breaks in inherits only those limited rights. Give each application the narrowest grant that lets it function.
GRANT ALL PRIVILEGES ON *.* TO 'app'@'%';GRANT SELECT, INSERT, UPDATE, DELETE
ON shop.* TO 'app'@'10.0.%';Input validation and allow-lists
Validation is not a primary defense against injection, but it is a valuable early filter and it catches many other bugs. Reject input that does not match what a field should contain: an integer id that is not numeric, an email that fails a format check, a status that is not in your set of known values. Prefer allow-lists (accept only what is known good) over deny-lists (block a list of bad characters), because attackers are endlessly creative about the characters you forgot.
// Validate shape before the value ever reaches the query
$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);
if ($id === false) {
http_response_code(400);
exit('Invalid id');
}
// still use a parameter below; validation is a supplement, not the fixA web application firewall
A web application firewall inspects incoming requests and blocks patterns that look like injection attempts before they reach your code. It is genuinely useful as an outer layer and buys time to patch, but it is not a fix. Attackers routinely encode and obfuscate payloads to slip past signature rules, so treat a WAF as a speed bump that complements correct code, never as a replacement for it.
Watch out: do not let a WAF, input validation, or an ORM lull you into shipping a concatenated query "because the other layers will catch it." Every one of those layers can be bypassed. Parameterization is the layer that cannot, so it is never optional. The rest only exist to limit the damage when something else goes wrong.
Escaping as a last resort
Before prepared statements were universal, the standard advice was to escape special characters in user input with a function such as mysqli_real_escape_string. Manual escaping still exists, but it is a fragile last resort, not a recommended technique. It is easy to forget on one field, easy to get wrong with different character sets, and useless for numeric contexts or identifiers where no quotes surround the value. If a parameterized API is available, and in modern PHP it always is, use it instead.
// Manual escaping: works until one field is missed or the charset shifts
$email = mysqli_real_escape_string($conn, $_POST['email']);
$sql = "SELECT id FROM users WHERE email = '" . $email . "'";// Prepared statement: nothing to remember, nothing to miss
$stmt = $conn->prepare('SELECT id FROM users WHERE email = ?');
$stmt->bind_param('s', $_POST['email']);
$stmt->execute();Reach for escaping only when you are stuck on a legacy interface that offers no parameters, and even then, plan to replace it. It is the seatbelt held on with tape: better than nothing, but not something you rely on by choice.
The prevention checklist
Bring the whole guide together as a scannable list. The first item is the fix; the rest are the layers that contain the damage when a mistake slips through. Severity tells you which findings block a release and which are follow ups.
| Control | What it does | Priority |
|---|---|---|
| Parameterized queries everywhere | Keeps user data out of the SQL text entirely | Required |
| Allow-list any dynamic identifier | Table, column, and ORDER BY cannot be bound | Required |
| ORM used without raw concatenation | Bindings passed to the library, never glued in | Required |
| Least-privilege database account | Limits what an attacker can reach | High |
| Input validated with allow-lists | Rejects malformed input early | High |
| Stored procedures parameterized internally | No CONCAT of arguments into dynamic SQL | High |
| Generic error messages in production | Denies attackers error-based feedback | Medium |
| Web application firewall in front | Blocks known payload patterns as a speed bump | Medium |
| Manual escaping only on legacy code | Last resort, scheduled for replacement | Low |
Injection is consistently ranked among the most serious web application risks by security researchers. The chart below reflects how reliably it appears near the top of vulnerability findings, a pattern OWASP has documented across successive editions of its top ten.
The relative heights are illustrative, but the message is not: injection stays near the top year after year, which is remarkable given how completely parameterized queries solve it. The gap between "solved in theory" and "still exploited in practice" is exactly the concatenated query nobody caught in review.
A step by step hardening plan
If you are auditing an existing codebase, work these steps in order. The first three eliminate the vulnerability; the rest reduce the fallout of any bug you miss.
Find every concatenated query
Grep for string building around SQL keywords and raw ORM calls. Each hit is a candidate injection point until proven otherwise.
Convert them to parameters
Rewrite each one as a prepared statement or a bound ORM call. Move password checks and other logic out of SQL and into code.
Allow-list dynamic identifiers
Any table, column, or sort direction from user input must map through a fixed list of safe values, never pass through raw.
Tighten the database grants
Drop the application to least privilege so a future flaw reaches only what the feature genuinely needs.
Add validation and a WAF
Validate input shapes with allow-lists and put a firewall in front as an outer layer, both as supplements to parameterization.
SQL injection is a solved problem in the sense that the fix is well known, cheap, and universally available. It persists only because the broken pattern is easy to write and easy to overlook. Make parameterized queries the default in your team, catch the exceptions in review, and layer least privilege and validation behind them, and this decades-old vulnerability simply stops finding a way in. For structured practice on writing safe, correct SQL, our advanced SQL course and the roundup of common SQL mistakes are good next reads, and the SQL developer path ties these habits together.