What is SQL injection? How it works and how to stop it

What SQL injection is

SQL injection (SQLi) is a vulnerability where text supplied by a user is pasted into a database query and the database ends up running it as SQL. The application meant to send a value, such as an email address or a product ID; the attacker sends a fragment of query language instead, and the meaning of the query changes.

The root cause is always the same: code that builds a query by string concatenation, mixing two kinds of data in one string. The query text is instructions you wrote. The value is data someone else wrote. Once they are glued together, the database has no way to tell where your instructions stop and the visitor's input begins.

That is why SQL injection is not specific to MySQL, PostgreSQL, SQL Server or SQLite, and not specific to PHP either. Any language, any driver and any database is vulnerable the moment a query is assembled from untrusted strings. It is catalogued as CWE-89, and injection has appeared on every edition of the OWASP Top 10. The cluster of searches around it, "SQL attack", "SQL vulnerability", "SQL injection attack", all describe this one mistake.

The good news is that the fix is equally uniform, decades old and built into every database driver in use today: send the query and the values separately.

How it works: the textbook example

Take a login check written the way countless tutorials once wrote it. The username and password come from a form and are dropped straight into the SQL string.

With normal input the query does what it should. Now imagine a visitor types ' OR '1'='1 into the password field. The quote closes the string literal the developer opened, and the rest becomes part of the WHERE clause. Because '1'='1' is always true and AND binds tighter than OR, the condition matches every row, and the code logs the visitor in as the first user in the table, which is often the administrator.

That one quote is the whole concept. Everything else in this guide, the different attack types, the impact and the defences, follows from the fact that the database received a single string and parsed the attacker's text as code.

(Storing plain-text passwords and comparing them in SQL is a second bug in this example. Real code fetches the user by name and checks the password with password_verify() against a hash. It is kept here because it is the shortest way to show the injection.)

Vulnerable: user input concatenated into the query (PHP)

<?php
// DO NOT DO THIS
$user = $_POST['username'];
$pass = $_POST['password'];

$sql = "SELECT id FROM users
        WHERE username = '$user' AND password = '$pass'";
$row = $pdo->query($sql)->fetch();

// With password  ' OR '1'='1  the database receives:
// SELECT id FROM users
// WHERE username = 'admin' AND password = '' OR '1'='1'
// ...which is true for every row.

The fix: parameterised queries, in PHP and Python

A parameterised query (also called a prepared statement or bound parameters) sends the SQL text with placeholders, ? or :name or %s depending on the driver, and sends the values separately. The database parses and plans the query first, then plugs the values in as data. A quote in a value is just a character in a string; it can never close a literal or add a clause, because parsing is already over.

This is not a cleaning step that might miss a case. It removes the mechanism entirely, which is why it sits at the top of every prevention list.

In PHP, use PDO or mysqli with placeholders. With PDO, set the character set in the DSN and turn off emulated prepares so the driver sends real server-side prepared statements, and switch on exceptions so failures are not silently ignored. In Python, every DB-API driver (sqlite3, psycopg, mysqlclient, PyMySQL) takes the values as a separate argument to execute(). The trap in Python is formatting the string yourself with an f-string or % before calling execute(): the result looks parameterised but is concatenation.

The same rule holds in every other stack: PreparedStatement in Java, SqlParameter in .NET, $1 placeholders in node-postgres, ? in Go's database/sql.

Safe: placeholders, values sent separately (PHP PDO and Python)

<?php
$pdo = new PDO('mysql:host=db;dbname=shop;charset=utf8mb4', $dbUser, $dbPass, [
    PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

$stmt = $pdo->prepare('SELECT id, password_hash FROM users WHERE username = ?');
$stmt->execute([$_POST['username']]);
$row = $stmt->fetch();
$ok  = $row && password_verify($_POST['password'], $row['password_hash']);

# Python (psycopg 3, PostgreSQL)
cur.execute(
    "SELECT id, password_hash FROM users WHERE username = %s",
    (username,),            # values go here, never into the string
)

# WRONG, still injectable: the string is built before execute() sees it
cur.execute(f"SELECT id FROM users WHERE username = '{username}'")

Types of SQL injection

Security write-ups sort SQL injection by how the attacker gets data back. The vulnerability is the same in each case; what differs is what the application reveals.

In-band, UNION-based. The result of the injected query comes back in the page itself. By appending a UNION SELECT, the attacker adds rows from another table, say the users table, to the product list the page was already showing. This is the fastest kind to exploit and the easiest to spot in testing.

In-band, error-based. The application prints database error messages. The attacker provokes errors whose text contains the data they want, such as a table name or a value. Showing raw SQL errors to visitors turns a blind bug into a readable one, which is a good reason to log errors server-side and show users a generic page.

Blind, boolean-based. The page shows no data and no errors, but it behaves differently when a condition is true or false: a product appears or does not, a page is 200 or 404. By asking yes-or-no questions one at a time, an attacker reads data a bit at a time. Slow, but tools automate it.

Blind, time-based. Even the page content is identical, so the attacker makes the database wait when a condition is true and measures the response time. If every response looks the same, timing is still a channel.

Out-of-band. The attacker makes the database server itself contact a system they control, typically through a DNS lookup or HTTP request triggered by a database function. It depends on database features and network egress being available, which is one more reason a database server should not be able to open outbound connections it has no use for.

Second-order (stored). The input is stored safely the first time, correctly parameterised, and then read back later and concatenated into a different query, often in an admin report or a background job. Developers trust data that came from their own database; that trust is the bug. The defence is the same as everywhere else: parameterise every query, including those built from values you stored yourself.

What an attacker actually gets

The impact is bounded by what the database account the application uses is allowed to do, which is why least privilege matters so much later in this guide.

Reading data. The common outcome: customer records, email addresses, password hashes, orders, API keys stored in settings tables. Any table that account can SELECT from is readable, not only the one the vulnerable query touches.

Bypassing authentication. As in the login example, a condition that should be specific becomes always-true.

Changing or destroying data. If the account can UPDATE, INSERT or DELETE, so can the attacker: changing prices, creating admin users, wiping tables. Some driver and database combinations allow several statements in one call, which widens this further.

Reaching the server. With powerful privileges, some databases can read or write files on the host or run operating system commands. On a database account with administrative rights, an SQL injection can become a full server compromise.

This is not theoretical. SQL injection was the entry point in the 2008 Heartland Payment Systems breach, where card data for well over 100 million cards was stolen; in the 2015 TalkTalk breach in the UK, which led to a regulatory fine; and in the 2023 MOVEit Transfer mass exploitation (CVE-2023-34362), where one injection flaw in a file transfer product was used against thousands of organisations. Old bug, current headlines.

Prevention, in order of importance

1. Parameterised queries everywhere. Every query, every value, including values from your own database, cookies, headers and internal services. This alone closes the vulnerability. The other steps limit the damage when someone, somewhere, forgets.

2. Use your ORM or query builder properly, and know its escape hatches. Eloquent, Doctrine, Django ORM, SQLAlchemy, Hibernate and Entity Framework parameterise ordinary queries for you. They do not protect raw fragments: whereRaw, DB::raw, selectRaw and orderByRaw in Laravel, .extra() and .raw() in Django, text() in SQLAlchemy, FromSqlRaw in EF Core. All of them accept bindings; use them. Grepping for those method names is the fastest code review you can do.

3. Allowlist what cannot be a parameter. Placeholders hold values, not identifiers. A table name, a column in ORDER BY or the words ASC/DESC cannot be bound, so a "sort by" parameter has to be mapped to a fixed list of column names in code. Never pass it through.

4. Least privilege for the database user. The web application should connect as a user that can SELECT, INSERT, UPDATE and DELETE on its own schema and nothing else: no DROP, no GRANT, no file access, no other databases, and never the root or sa account. Migrations run with a separate, stronger user. If an injection slips through, this decides whether it leaks one schema or owns the server.

5. Input validation as defence in depth. An order ID should be an integer, a date should parse as a date, a country code is two letters. Validating types and formats at the edge of your application rejects a lot of malicious input early and catches bugs. It is not the fix: a name field has to accept O'Brien, and a comment field accepts almost anything.

6. Do not leak errors. Log database errors server-side with the query and context; show the visitor a generic error page. In PHP that means display_errors=Off in production.

Raw fragments with bindings, an allowlisted sort, and a least-privilege user

// Laravel: raw expressions must carry their own bindings
$orders = DB::table('orders')
    ->whereRaw('total > ? AND status = ?', [$min, $status])
    ->get();

// ORDER BY cannot be bound: map input to known columns
$sortable = ['created_at', 'total', 'status'];
$column   = in_array($request->sort, $sortable, true) ? $request->sort : 'created_at';
$dir      = $request->dir === 'asc' ? 'asc' : 'desc';
$orders   = Order::orderBy($column, $dir)->paginate(50);

-- MySQL: the app user gets data access to its own schema only
CREATE USER 'shop_app'@'10.0.%' IDENTIFIED BY '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'10.0.%';

Why escaping is not enough

Before prepared statements were common, the advice was to escape quotes: addslashes(), then mysql_real_escape_string(), then mysqli::real_escape_string(). Escaping is still in a lot of code, and it fails in predictable ways.

It only protects quoted contexts. WHERE id = $id has no quotes around the value, so there is nothing to escape, and input such as 1 OR 1=1 passes through untouched. The same goes for LIMIT, ORDER BY and anything numeric.

It depends on the character set. Escaping functions have to agree with the connection's encoding. Mismatches between the client and server character set have allowed multi-byte sequences to swallow the escaping backslash; addslashes() never knew the encoding at all.

It has to be remembered every single time. One forgotten call in one query, or a value escaped for HTML instead of SQL, and the hole is back. Parameterised queries make the safe path the default path.

The same reasoning applies to blacklists and "sanitising" functions that strip words like SELECT or --. Attackers have spent twenty years finding encodings, comments and case variations that slip past such filters. Treat any home-made filter as a convenience, never as the protection.

Testing your own application safely

Start with the code, not the attack. Most SQL injection is visible in a code search: queries assembled with ., +, string interpolation or sprintf, and ORM raw methods called with a variable. Static analysis tools such as Semgrep and CodeQL ship rules for exactly this pattern and can run in CI on every pull request.

Then write tests that pass hostile-looking but harmless values, a single quote, a name like O'Brien, a very long string, through your forms and APIs, and assert that the application returns a normal result or a validation error, never a 500 and never a database error message. These tests stay in the suite and protect against regressions.

For dynamic testing, open-source scanners such as OWASP ZAP and sqlmap probe running applications for injectable parameters. sqlmap in particular is an exploitation tool: it extracts data when it finds a hole. Run it only against systems you own or have written permission to test, preferably a staging copy with fake data, because its probes write to logs, can change data and can trip your monitoring. Scanning someone else's site without permission is illegal in most countries, whatever the intent.

If you test through your CDN, expect the WAF to block many of the probes. That is the WAF working, but it also hides the application's real behaviour, so test the application itself on staging and treat the WAF as a separate layer.

# PHP: query calls that mention a variable (review each hit)
grep -rnE '(query|exec|prepare)\(.*\$[A-Za-z_]' --include='*.php' app/

# Laravel / Eloquent raw fragments: check each one has bindings
grep -rnE '(whereRaw|selectRaw|orderByRaw|havingRaw|DB::raw)\(' app/

# Python: f-strings or % formatting inside execute()
grep -rnE "execute\(\s*f['\"]|execute\(.*['\"]\s*%\s" --include='*.py' .

Where a WAF fits: a layer, not the fix

A web application firewall inspects each request before it reaches your application and blocks the ones that look like attacks. SQL injection leaves recognisable shapes in query strings and form bodies, so this is one of the things a WAF is good at: it stops the automated scanners that probe every site on the internet, and it buys time when a vulnerable plugin is disclosed before you can patch it.

It is still pattern matching against a vulnerability that lives in your code. A targeted attacker shapes the payload to avoid the patterns, and some injection points, such as JSON fields in an unusual encoding, values that reach a query through a background job, or second-order injection, never look suspicious in a request at all. The OWASP Top 10 guide maps what a WAF can and cannot cover. Parameterised queries remain the fix.

The operational cost is false positives. Any rule strict enough to catch injection will sometimes block a person: an editor saving a post with a code sample, a support form where someone pastes an error message containing SQL. The usual practice is to run new rules in a detection (log-only) mode first, read what would have been blocked, and then enforce, with narrow exceptions where a field legitimately carries SQL-like text.

On CDN.com.tr the WAF is ModSecurity with the OWASP Core Rule Set, turned on per account from the delivery rules page. In that rule set the 942 family is the SQL injection rules. A blocked visitor gets a branded 403 page with a reference ID, which is the request identifier in the audit log, so you can see exactly which rule matched and on which field. The WAF log page in the panel lists blocked events with the attack category, country, IP and rule, exports to CSV or XLSX, and cdnctl waf logs shows the same from the command line. When a legitimate field trips a rule, the fix should be narrow: that rule, on that field, on that path, rather than switching protection off for the site. Exceptions for single paths are not in the panel yet, so send the request's reference ID to support. For login forms, rate limiting per route sits on the same page and slows the brute-force traffic that often comes with injection scans.