SQL Injection Explained: Attacks and Parameterised Queries

By Engr. Henry Akpan

The Bug That Lets Attackers Rewrite Your Queries

A login form asks for a username and password, and the program checks them against a database. If the program builds its SQL query by pasting the user's text straight into the query string, an attacker can type something that changes the meaning of the query itself, and log in without a password, read other users' data or delete tables. This is SQL injection, one of the oldest and most damaging web security flaws, and it still appears in student projects and real systems. This tutorial shows exactly how SQL injection works using Python and SQLite, then how to prevent it with parameterised queries, how to handle cases parameters can't cover (such as column names), and the extra layers of defence. Everything runs on a throwaway in-memory database, and the outputs shown are real.

Ethics note: only test these techniques on systems you own or have written permission to test. Attacking other people's systems is illegal in Nigeria under the Cybercrimes Act and in almost every other country.

Setting Up a Practice Database

import sqlite3
import hashlib

def h(pw):
    return hashlib.sha256(pw.encode()).hexdigest()[:12]   # shortened only for display

conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT UNIQUE, pw_hash TEXT, role TEXT);
    CREATE TABLE results (student TEXT, course TEXT, score INTEGER);
""")
conn.executemany("INSERT INTO users (username, pw_hash, role) VALUES (?, ?, ?)", [
    ("admin", h("S3cure!Admin"), "admin"),
    ("ada", h("ada-pass-2024"), "student"),
    ("musa", h("musa#pass"), "student"),
])
conn.executemany("INSERT INTO results VALUES (?, ?, ?)", [
    ("ada", "MTH101", 72), ("ada", "PHY101", 65), ("musa", "MTH101", 58),
])
print(conn.execute("SELECT COUNT(*) FROM users").fetchone()[0], "users")

Output:

3 users

(Real applications should store passwords with a slow, salted algorithm such as bcrypt or Argon2, not plain SHA-256; see the tutorial on hashing and passwords. The short hashes here just keep the example simple.)

A Vulnerable Login

This function builds the query with an f-string, putting the user's input directly into the SQL text:

def login_unsafe(username, password):
    query = (f"SELECT username, role FROM users "
             f"WHERE username = '{username}' AND pw_hash = '{h(password)}'")
    print("SQL:", query)
    return conn.execute(query).fetchone()

print(login_unsafe("ada", "ada-pass-2024"))
print(login_unsafe("ada", "wrong"))

Output:

SQL: SELECT username, role FROM users WHERE username = 'ada' AND pw_hash = '43b68326521f'
('ada', 'student')
SQL: SELECT username, role FROM users WHERE username = 'ada' AND pw_hash = '8810ad581e59'
None

It appears to work. Now an attacker types a username containing a quote and an SQL comment:

print(login_unsafe("admin' --", "anything"))

Output:

SQL: SELECT username, role FROM users WHERE username = 'admin' --' AND pw_hash = 'ee0874170b7f'
('admin', 'admin')

The quote closes the username string early, and -- starts an SQL comment, so the password check is ignored entirely. The attacker is logged in as admin without knowing the password. Another classic input, ' OR '1'='1, makes the WHERE clause true for every row.

Stealing Data with SQL Injection

Injection is not limited to logins. A search box that lists a student's results can be made to reveal other tables using UNION:

def results_unsafe(student):
    return conn.execute(
        f"SELECT course, score FROM results WHERE student = '{student}'").fetchall()

print(results_unsafe("ada"))
print(results_unsafe("x' UNION SELECT username, pw_hash FROM users --"))

Output:

[('MTH101', 72), ('PHY101', 65)]
[('ada', '43b68326521f'), ('admin', '051a03858613'), ('musa', '3e50ba2f402d')]

Instead of results, the page now displays every username and password hash. With other database systems, injection can also modify or delete data, and sometimes run commands on the server.

The Fix: Parameterised Queries

Never build SQL by joining strings with user input. Use placeholders in the query and pass the values separately (this is often called using prepared statements). Python's sqlite3 uses ? (or named placeholders like :name); other libraries use %s or $1. The database receives the SQL and the data separately, so the data can never be interpreted as SQL, whatever characters it contains.

def login_safe(username, password):
    return conn.execute(
        "SELECT username, role FROM users WHERE username = ? AND pw_hash = ?",
        (username, h(password)),
    ).fetchone()

def results_safe(student):
    return conn.execute(
        "SELECT course, score FROM results WHERE student = :s", {"s": student}
    ).fetchall()

print(login_safe("ada", "ada-pass-2024"))
print(login_safe("admin' --", "anything"))
print(results_safe("x' UNION SELECT username, pw_hash FROM users --"))
print(results_safe("O'Brien"))     # names with apostrophes now just work

Output:

('ada', 'student')
None
[]
[]

The attack strings are now treated as ordinary (strange) usernames that don't exist. As a bonus, legitimate names containing apostrophes, such as O'Brien, no longer break the query.

Note that escaping quotes by hand (for example replacing ' with '') is not a reliable fix: it is easy to miss a case, and different databases have different rules. Use parameters.

What Parameters Can't Do: Table and Column Names

Placeholders can only stand for values, not for SQL keywords or identifiers such as column names or sort order. If users choose the sort column, check their choice against an allow-list of known names:

ALLOWED_SORT = {"course", "score"}

def results_sorted(student, sort_by, descending=False):
    if sort_by not in ALLOWED_SORT:
        raise ValueError(f"Cannot sort by {sort_by!r}")
    order = "DESC" if descending else "ASC"            # chosen by code, not the user
    sql = f"SELECT course, score FROM results WHERE student = ? ORDER BY {sort_by} {order}"
    return conn.execute(sql, (student,)).fetchall()

print(results_sorted("ada", "score", descending=True))
try:
    results_sorted("ada", "score; DROP TABLE users")
except ValueError as e:
    print("Rejected:", e)

Output:

[('MTH101', 72), ('PHY101', 65)]
Rejected: Cannot sort by 'score; DROP TABLE users'

The same applies to IN (...) lists: generate one ? per value, such as ",".join("?" * len(ids)), and pass the values as parameters.

Defence in Depth

Parameterised queries are the main fix, but good systems add further layers in case one fails:

  • Use an ORM or query builder (such as SQLAlchemy, Django's ORM, or Mongoose for MongoDB) that parameterises for you, and be careful with their "raw SQL" features.
  • Least privilege: the database account used by a web app should only have the permissions it needs. A results page doesn't need permission to drop tables.
  • Validate input: check types, lengths and formats (a matric number has a known pattern). Validation reduces risk but is not a substitute for parameters.
  • Don't show raw database errors to users; they help attackers. Log them on the server instead.
  • Hash passwords properly, so that even leaked hashes are hard to crack.
  • NoSQL databases are not immune. MongoDB queries can be manipulated if user-supplied objects like {"$ne": null} are passed straight into a query; sanitise or validate them.

Summary

  • SQL injection happens when user input is pasted into SQL text, letting attackers change the query's meaning.
  • Quotes and comments (' --) can bypass logins; UNION SELECT can leak other tables.
  • The fix is parameterised queries: placeholders like ?, with values passed separately.
  • Identifiers such as column names can't be parameters; use an allow-list.
  • Add least-privilege accounts, validation, careful error handling and proper password hashing as extra layers.

Related Tutorials