SQL Injection

SQL injection (SQLi) has been on every OWASP Top 10 list since the first one, and it still appears in real applications every year. It happens when user input is joined into an SQL query as text, so the input can change the meaning of the query itself. One vulnerable search box can expose an entire customer database.

In this lesson you will see SQL injection happen against a tiny database that exists only in memory on your own computer, learn the main types testers talk about, see where to practise in your lab, and, most importantly, learn the fix that stops it completely: parameterised queries.

How SQL injection happens

Suppose a shop looks up a customer by email. The developer builds the query with an f-string. Run this safe local demo; the database is created in memory and disappears when the program ends:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (id INTEGER, email TEXT, role TEXT)")
db.executemany("INSERT INTO users VALUES (?, ?, ?)", [
    (1, "aarav@shop.example", "customer"),
    (2, "priya@shop.example", "admin"),
    (3, "kabir@shop.example", "customer"),
])

def find_user_unsafe(email):
    query = f"SELECT id, email, role FROM users WHERE email = '{email}'"   # VULNERABLE
    print("SQL sent:", query)
    return db.execute(query).fetchall()

print(find_user_unsafe("kabir@shop.example"))
print(find_user_unsafe("x' OR '1'='1"))

SQL sent: SELECT id, email, role FROM users WHERE email = 'kabir@shop.example'
[(3, 'kabir@shop.example', 'customer')]
SQL sent: SELECT id, email, role FROM users WHERE email = 'x' OR '1'='1'
[(1, 'aarav@shop.example', 'customer'), (2, 'priya@shop.example', 'admin'), (3, 'kabir@shop.example', 'customer')]

The quote in the second input closes the string early, and the rest, OR '1'='1', becomes part of the SQL. Since '1'='1' is always true, the WHERE clause matches every row. In a login form, the same trick can make a check like "find a user with this email and password" succeed without a password. With other inputs, an attacker may read other tables, change data or, on some database setups, run operating system commands.

The first clue: a quote breaks the query

Testers usually start by entering a single quote, which is also a perfectly normal character in names like O'Brien. If the application builds SQL from text, the extra quote leaves the query unbalanced and the database raises an error:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (id INTEGER, email TEXT)")

def find_user_unsafe(email):
    return db.execute(f"SELECT id FROM users WHERE email = '{email}'").fetchall()

for email in ["meera@shop.example", "o'brien@shop.example"]:
    try:
        print(email, "->", find_user_unsafe(email))
    except sqlite3.OperationalError as e:
        print(email, "-> database error:", e)

meera@shop.example -> []
o'brien@shop.example -> database error: near "brien": syntax error

A real customer called O'Brien would get an error page here, which shows that SQL injection is a correctness bug as well as a security hole. If the error message reaches the browser, it also confirms to an attacker that their input reaches the database.

Types of SQL injection

You will see these names in reports and in the OWASP Testing Guide:

Blind SQL injection is slow but still very damaging: data can be extracted one yes/no question at a time, which is exactly what automated tools do.

Practising in your lab

DVWA has a page called SQL Injection under its list of vulnerabilities. Log in to your lab copy at http://192.168.56.101:4280, set the security level to low, and try a normal user ID, then a single quote, and compare the responses. Then set the level to impossible and read the page's PHP source with the "View Source" button: it uses prepared statements, and the same inputs no longer change the query. Juice Shop also has a login form challenge based on SQL injection.

Kali includes sqlmap, a tool that automates detection and extraction. It sends a very large number of requests and can change data, so use it only on your own lab apps, and only after you understand the manual steps above. On a real engagement, automated injection tools are used only if the rules of engagement allow them, and never against production data without explicit agreement.

The fix: parameterised queries

The complete fix is to never build SQL from strings. Send the query and the values separately, using placeholders. The database then treats the input strictly as data, whatever characters it contains:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (id INTEGER, email TEXT, role TEXT)")
db.executemany("INSERT INTO users VALUES (?, ?, ?)", [
    (1, "aarav@shop.example", "customer"),
    (2, "o'brien@shop.example", "customer"),
])

def find_user_safe(email):
    return db.execute("SELECT id, email, role FROM users WHERE email = ?", (email,)).fetchall()

print(find_user_safe("o'brien@shop.example"))
print(find_user_safe("x' OR '1'='1"))

[(2, "o'brien@shop.example", 'customer')]
[]

The attack string is now just an unusual email address that matches nobody, and O'Brien can log in normally. Every language has the same feature. In PHP with PDO (see CRUD with Prepared Statements):

$stmt = $pdo->prepare('SELECT id, email, role FROM users WHERE email = :email');
$stmt->execute(['email' => $email]);
$user = $stmt->fetch();

In Java it is PreparedStatement with ?, and ORMs such as Django's, Laravel's Eloquent and Hibernate use parameters automatically, unless you pass raw SQL strings to them.

Things placeholders cannot hold

Placeholders work for values, not for table names, column names or keywords such as ASC and DESC. For a "sort by" option, use an allow-list: map what the user may choose to fixed SQL text that you wrote.

SORT_COLUMNS = {"price": "price", "newest": "created_at", "name": "title"}
DIRECTIONS = {"asc": "ASC", "desc": "DESC"}

def build_order_by(sort, direction):
    column = SORT_COLUMNS.get(sort, "created_at")
    order = DIRECTIONS.get(direction.lower(), "DESC")
    return f"ORDER BY {column} {order}"

print(build_order_by("price", "asc"))
print(build_order_by("price; DROP TABLE products", "asc"))
print(build_order_by("name", "sideways"))

ORDER BY price ASC
ORDER BY created_at ASC
ORDER BY title DESC

Only text from your own dictionaries ever reaches the query, so unexpected input falls back to a safe default.

Defence in depth

Parameterised queries are the fix. These extra layers reduce the damage if something is missed:

• Least privilege: the web app's database user should not be an admin and should only access its own tables (see SQL Users & Security).
• Input validation: check that an ID is a number and an email looks like an email, before it reaches the database.
• Generic error pages: log database errors on the server; show users a plain message.
• A web application firewall (WAF) can block common attack patterns, but attackers often find ways around WAF rules, so it never replaces fixing the code.

Common mistakes

• Escaping quotes by hand (email.replace("'", "''")). It is easy to miss a case, such as numeric parameters or a different database's rules. Use placeholders.
• Parameterising most queries but not all. One forgotten report page or admin search is enough.
• Putting user input into ORDER BY or table names after "checking" it with a block-list. Use an allow-list instead.
• Trusting data from your own database. Second-order injection uses values that were stored earlier.

Key points

• SQL injection happens when input is joined into SQL text and changes the query's meaning.
• A single quote causing an error is the classic first sign; error, UNION, blind and second-order are the main types.
• Practise only in your lab: DVWA's SQL Injection page and Juice Shop.
• Parameterised queries (placeholders) are the complete fix for values.
• Use allow-lists for column names and sort directions, plus least privilege and generic errors as extra layers.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE products (name TEXT, price INTEGER)")
db.executemany("INSERT INTO products VALUES (?, ?)", [
    ("Silk saree", 4500), ("Cotton saree", 1299), ("Kurta", 899),
])

def search_products(db, term, max_price):
    sql = "SELECT name, price FROM products WHERE name LIKE ? AND price <= ? ORDER BY price"
    return db.execute(sql, (f"%{term}%", max_price)).fetchall()

print(search_products(db, "saree", 2000))
print(search_products(db, "' OR 1=1 --", 10000))