What SQL injection is, and how sites get hit

SQL injection is one of the oldest vulnerabilities in web development, documented since the late 1990s, and it still turns up in new code today. Not because it is hard to prevent, but because it is easy to introduce by accident. It happens when an application builds a database query by joining fixed SQL text and user-supplied input together, without keeping the two properly separated. If that input is not handled correctly, a visitor can change what the query actually does, rather than simply supplying a value for it to act on.

This article explains the mechanism well enough to recognise it in existing code, and covers the practices that prevent it reliably. It is written for defence: there is no working attack example here, and none is needed to understand or fix the problem.

What "injection" actually means

A web application typically takes something a visitor supplies — a search box, a login email, a product ID in a URL — and uses it to build a database query. A vulnerable application does this by treating that input as though it were part of the trusted SQL text itself, gluing the two together into one string before sending it to the database server.

Once they are merged into a single piece of text, the database has no way to tell where the developer's intended instruction ends and the visitor's data begins. If that input contains characters that carry meaning in SQL, such as a quote that closes a string early, the database can end up interpreting part of what was meant to be plain data as an instruction instead. A field that was only ever meant to hold a search term can, in a vulnerable application, be used to change the logic of the query built around it: turning a check that should match one record into one that matches every record, or appending an entirely separate instruction after the original one.

What an attacker can actually do with it

The impact depends on what the vulnerable query does and what privileges the database user running it has, which is exactly why the defences below work at more than one level. In practice, a successful injection can be used to:

• Read data the application was never meant to expose, including other customers' records, stored password hashes, or entirely unrelated tables.
• Bypass a login check by manipulating the logic of the query that is supposed to verify a username and password.
• Modify or delete data, including data belonging to other parts of the same site.
• In the worst case, reach further into the database server itself, if the connecting user has broad enough privileges to allow it.

That last point is why granting a database user only the privileges it actually needs matters even when the application code is written correctly. It limits the damage a bug can do, rather than relying on the code being perfect forever.

The real fix: keep code and data separate

The reliable defence is not cleverer string-handling. It is not building queries out of strings at all. Every modern database driver supports prepared statements, also called parameterised queries: you write the SQL with placeholders where the values go, hand the actual values to the driver separately, and the driver sends them to the database in a way that can never be reinterpreted as part of the query's structure. The input is always treated as data, never as code, whatever characters it contains.

In PHP using PDO, that looks like this:

$stmt = $pdo->prepare('SELECT id, name FROM customers WHERE email = ?');
$stmt->execute([$email]);
$row = $stmt->fetch();

The same idea with mysqli:

$stmt = $mysqli->prepare('SELECT id, name FROM customers WHERE email = ?');
$stmt->bind_param('s', $email);
$stmt->execute();
$result = $stmt->get_result();

Notice that $email never appears inside the SQL text in either example. It is supplied afterwards, through a placeholder, and it is the driver, not string concatenation, that puts it in front of the database. Most frameworks and query builders use prepared statements under the hood by default, which is one of the reasons building on an established framework rather than hand-rolled query building removes an entire category of risk.

Least-privilege database users as a second layer

Prepared statements should stop injection from working at all, but defence in depth matters because code changes over time and mistakes happen. A database user that only has the privileges its application genuinely needs, typically the ability to select, insert, update and delete rows on its own database and nothing at all on any other database on the account, limits what a successful attack could achieve, even in the unlikely event one gets through. See how to grant a user access to a database for setting this up correctly rather than defaulting every user to full administrative rights out of convenience.

Validate input anyway

Prepared statements handle the SQL side of the problem on their own, but validating input at the point it is received is still worth doing for its own sake. Rejecting an email address that is not shaped like an email address, or a numeric ID that contains letters, catches mistakes and unrelated abuse early, and reduces the amount of unexpected data your application has to handle further downstream. Treat it as good practice alongside parameterised queries, not as a replacement for them.

Signs a database may already have been affected

If you suspect an existing application has actually been exploited, rather than simply wanting to prevent it going forward, look for the usual signs of any compromise: administrator accounts nobody on your team created, content or links appearing in database tables that were never entered through the application itself, or data changes with no matching activity in your own records. How to tell whether your website has been hacked covers this in more depth and is the right next step if anything here sounds familiar.

Auditing code you did not write

If you have inherited an older application and are unsure whether its queries are built safely, search the codebase for anywhere a variable is inserted directly into a SQL string, whether through concatenation or interpolated straight into a quoted string, rather than passed through a prepared statement's placeholder. Any query built that way from data a visitor can influence, however indirectly, is worth rewriting. It is tedious work on an old codebase, but it is a one-time fix, whereas leaving it in place is an ongoing risk every single time that code runs.

Between prepared statements in the application, a database user restricted to what it actually needs, and basic input validation, SQL injection is a solved problem for any application prepared to apply all three consistently. A practical website security checklist covers where this fits alongside the rest of a site's security, and contact us if you would like a second opinion on anything you find while auditing your own code.