SQL Injection: Understanding the Classic Web Attack

SQL injection (or SQLi) has ranked among the most widespread web vulnerabilities for over twenty years, and it continues to wreak havoc. It holds a prominent place in the OWASP Top 10 under the "Injection" category. Yet its concept is simple to understand and, good news, easy to fix once you grasp the mechanics.

In this guide, you will discover how an SQL injection happens, explore an educational authentication bypass example, and spend the second half of your reading on what really matters: defending yourself effectively.

1. Where does the problem come from?

A web application almost always stores its data in a relational database (MySQL, PostgreSQL, SQLite...) queried via SQL. The issue arises when the developer builds queries by concatenating user input directly into the SQL string.

The server can no longer distinguish between instructions (the SQL code) and data (what the user types). An attacker exploits this confusion by slipping SQL fragments into a text field. It is the exact same type of flaw as XSS, but applied to the database language rather than the browser.

2. Anatomy of a vulnerable query

Imagine a login form. On the server side, a developer in a hurry writes something like this (example in PHP, but the problem is identical in all languages):

// CODE VULNÉRABLE — ne jamais faire ça
$user = $_POST['username'];
$pass = $_POST['password'];

$sql = "SELECT * FROM users
        WHERE username = '$user' AND password = '$pass'";

$result = $db->query($sql);

If the user enters alice and secret123, the query sent to the database is completely normal:

SELECT * FROM users
WHERE username = 'alice' AND password = 'secret123';

The problem: the single quotes delimiting the data can also be entered by the user. Nothing prevents them from injecting one.

3. Example: login bypass

This is the most famous illustration of SQLi. In the username field, an attacker enters the following string (the password can be left blank):

' OR 1=1 --

The query actually executed by the database then becomes:

SELECT * FROM users
WHERE username = '' OR 1=1 --' AND password = '';

Let's break down what happens:

• The first ' closes the username string prematurely.
• OR 1=1 adds an always-true condition: the WHERE clause therefore returns every row in the table.
• -- starts an SQL comment, which cancels the rest of the query, including the password check.

Result: the database returns the first user (often the administrator), and the application, which merely checks 'at least one row found?', validates the login. The attacker gets in without knowing any password.

Beyond this textbook case, depending on the context, the same vulnerability allows reading entire tables via UNION queries, silently extracting data using 'blind' injection (blind SQLi), or even executing system commands on poorly locked-down configurations. Consequences range from massive personal data leaks to full server takeover.

4. Defence: cutting the problem off at the root

The good news is that SQL injection is one of the best-understood vulnerabilities and easiest to eliminate for good. Here are the defences, from the most fundamental to the most complementary.

4.1 Prepared statements (the number one solution)

The decisive countermeasure is to separate SQL code from data. With a prepared query (prepared statement), you first send the query structure with placeholders (? or :param), then pass the values separately. The database engine never interprets them as SQL again:

// CODE SÉCURISÉ — requête préparée (PHP / PDO)
$stmt = $db->prepare(
    "SELECT * FROM users WHERE username = ? AND password = ?"
);
$stmt->execute([$user, $pass]);

With this approach, the ' OR 1=1 -- input is treated as a simple string: the database literally searches for a user named ' OR 1=1 --, finds none, and refuses the login. The attack is neutralised by design. Here is the golden rule: never concatenate user input into an SQL query.

4.2 ORMs: built-in safeguards

ORMs (Doctrine, Prisma, Hibernate, Eloquent, Python's SQLAlchemy module, etc.) generate parameterised queries automatically. You handle objects; they produce secure SQL. Be careful, however: most ORMs provide a 'raw SQL' escape hatch (raw query). As soon as you use it, you become responsible for parameterising your values yourself.

4.3 Input validation and type casting

Alongside prepared statements (and never as a replacement), systematically validate what you receive. If a parameter is supposed to be an integer (a product ID, for example), enforce typing and reject everything else:

// On attend un identifiant numérique : on l'impose
$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);
if ($id === false) {
    http_response_code(400);
    exit('Identifiant invalide');
}

Always favour an allow-list approach (only allow what is explicitly expected) over a deny-list (trying to block dangerous characters), as blocklists can almost always be bypassed.

4.4 Principle of least privilege on the database side

Even a residual vulnerability causes far less damage if the account used by the application only has the strictly necessary permissions. The web application must never connect to the database with an administrator account (root, postgres). Create a dedicated account limited to required operations:

-- Compte applicatif minimal
CREATE USER 'app_web'@'localhost' IDENTIFIED BY '...';
GRANT SELECT, INSERT, UPDATE, DELETE
  ON boutique.* TO 'app_web'@'localhost';
-- Pas de DROP, pas de GRANT, pas d'accès aux autres bases

This way, even if an injection gets through, the attacker will not be able to drop tables, read neighbouring databases, or create new accounts.

4.5 WAF: defence in depth

A web application firewall (WAF) such as ModSecurity or Cloudflare's protections detects and blocks known attack patterns before they reach your code. This is useful, especially to buy time against automated scans, but you must never rely on it alone: a WAF can be bypassed, whereas a prepared statement shuts the door for good. Treat it as an extra lock, not as the load-bearing wall.

5. Key takeaways

• SQL injection stems from confusing code with data.
• Prepared statements (or a properly used ORM) eliminate the problem at the root: this should be your first reflex.
• Input validation, database least privilege and a WAF form a defence-in-depth strategy that limits the impact of any mistake.
• Only one rule to remember: never concatenate user input into an SQL query.

Want to see an injection happen live, then fix it with your own hands? That is precisely what Lockbay Academy's SQLi labs offer: one environment per student, legal and reproducible, where you attack to understand better, then defend for real.