SQL Injection Demo

Database Schema

A simple MySQL database mimicking a user authentication system.

CREATE DATABASE IF NOT EXISTS injection_demo;
USE injection_demo;

CREATE TABLE users (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    username  VARCHAR(50)  NOT NULL UNIQUE,
    password  VARCHAR(255) NOT NULL,
    email     VARCHAR(100) NOT NULL,
    role      ENUM('user', 'admin') DEFAULT 'user'
);

CREATE TABLE orders (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    user_id  INT NOT NULL,
    product  VARCHAR(100) NOT NULL,
    amount   DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO users (username, password, email, role) VALUES
    ('alice', 'hashed_pw_1', 'alice@example.com', 'user'),
    ('bob',   'hashed_pw_2', 'bob@example.com',   'user'),
    ('admin', 'hashed_pw_3', 'admin@example.com', 'admin');

INSERT INTO orders (user_id, product, amount) VALUES
    (1, 'Laptop',  999.99),
    (2, 'Monitor', 349.50),
    (3, 'Server',  4200.00);

Project Setup

pip install pymysql python-dotenv

project/
├── .env            # Local credentials — never commit
├── .env.example    # Placeholder template — safe to commit
├── .gitignore
├── db.py           # Connection helper
├── vulnerable.py   # Insecure implementation
└── secure.py       # Fixed implementation

.env

Create this file at the project root. It is loaded at runtime and keeps credentials out of source code.

# .env — never commit this file
DB_HOST=localhost
DB_USER=appuser
DB_PASSWORD=password
DB_NAME=injection_demo
DB_PORT=3306

.env.example

Commit this instead — it documents which variables are required without exposing real values.

# .env.example — safe to commit
DB_HOST=
DB_USER=
DB_PASSWORD=
DB_NAME=
DB_PORT=3306

.gitignore

.env

db.py

load_dotenv() is called here so it runs automatically whenever get_connection() is first imported. It reads .env into os.environ but does not override variables that are already set, so production environment variables always take precedence.

import os
import pymysql
import pymysql.cursors
from dotenv import load_dotenv

load_dotenv()  # Loads .env into os.environ; no-op if variables are already set

def get_connection():
    return pymysql.connect(
        host=os.environ["DB_HOST"],
        user=os.environ["DB_USER"],
        password=os.environ["DB_PASSWORD"],
        database=os.environ["DB_NAME"],
        port=int(os.environ.get("DB_PORT", 3306)),
        cursorclass=pymysql.cursors.DictCursor,
    )

The Vulnerable Script

The query is built by dropping user input directly into a Python f-string.

# vulnerable.py
from db import get_connection

def find_user(username):
    connection = get_connection()
    try:
        with connection.cursor() as cursor:
            query = f"SELECT id, username, email, role FROM users WHERE username = '{username}'"

            print(f"\n[SQL] {query}\n")

            cursor.execute(query)
            rows = cursor.fetchall()

            if not rows:
                print("No user found.")
                return

            for row in rows:
                print(f"id={row['id']}  username={row['username']}  email={row['email']}  role={row['role']}")
    finally:
        connection.close()

if __name__ == "__main__":
    username = input("Enter username: ")
    find_user(username)

Running the Attacks

Normal Usage

python vulnerable.py

Enter username: alice

[SQL] SELECT id, username, email, role FROM users WHERE username = 'alice'

id=1  username=alice  email=alice@example.com  role=user

Works as expected. Now the attacks.

Attack 1 — Dump All Users

Payload:

' OR '1'='1

python vulnerable.py

Enter username: ' OR '1'='1

Resulting SQL:

SELECT id, username, email, role FROM users WHERE username = '' OR '1'='1'

Output:

id=1  username=alice  email=alice@example.com  role=user
id=2  username=bob    email=bob@example.com    role=user
id=3  username=admin  email=admin@example.com  role=admin

The WHERE clause is bypassed completely. '1'='1' is always true, so every row is returned.

Attack 2 — Extract Data from Another Table

Payload:

' UNION SELECT id, product, amount, 'stolen' FROM orders --

python vulnerable.py

Enter username: ' UNION SELECT id, product, amount, 'stolen' FROM orders --

Resulting SQL:

SELECT id, username, email, role FROM users WHERE username = ''
UNION
SELECT id, product, amount, 'stolen' FROM orders --'

Output:

id=1  username=Laptop   email=999.99   role=stolen
id=2  username=Monitor  email=349.50   role=stolen
id=3  username=Server   email=4200.00  role=stolen

The attacker pivoted into the orders table without any direct access to it. The -- comments out the trailing quote, keeping the SQL valid.

Attack 3 — Target the Admin Account

Payload:

' UNION SELECT id, username, password, role FROM users WHERE role = 'admin' --

Resulting SQL:

SELECT id, username, email, role FROM users WHERE username = ''
UNION
SELECT id, username, password, role FROM users WHERE role = 'admin' --'

Output:

id=3  username=admin  email=hashed_pw_3  role=admin

The admin's username and password hash are now exposed. The hash can be taken offline for cracking.

Attack 4 — Delete Data

Destructive

Do not run this against a real database. It will permanently delete rows.

Payload:

'; DELETE FROM orders; --

Resulting SQL:

SELECT id, username, email, role FROM users WHERE username = '';
DELETE FROM orders;
--'

The SELECT returns nothing. The DELETE wipes the entire orders table.

Note: PyMySQL's cursor.execute() only runs the first statement in a string by default, so this specific multi-statement attack requires connection.autocommit(True) and a driver configured to allow multiple statements. The vulnerability still fully applies for single-statement attacks (UNION, OR 1=1, etc.) and in environments where multi-statement execution is enabled.

The Fix

The only change: replace the f-string with a bound parameter using %s.

# secure.py
from db import get_connection

def find_user(username):
    connection = get_connection()
    try:
        with connection.cursor() as cursor:
            query = "SELECT id, username, email, role FROM users WHERE username = %s"

            print(f"\n[SQL] {query}")
            print(f"[Param] username = {username!r}\n")

            cursor.execute(query, (username,))
            rows = cursor.fetchall()

            if not rows:
                print("No user found.")
                return

            for row in rows:
                print(f"id={row['id']}  username={row['username']}  email={row['email']}  role={row['role']}")
    finally:
        connection.close()

if __name__ == "__main__":
    username = input("Enter username: ")
    find_user(username)

The Same Attacks Against the Fixed Version

python secure.py

Enter username: ' OR '1'='1

[SQL] SELECT id, username, email, role FROM users WHERE username = %s
[Param] username = "' OR '1'='1"

No user found.

python secure.py

Enter username: ' UNION SELECT id, product, amount, 'stolen' FROM orders --

[SQL] SELECT id, username, email, role FROM users WHERE username = %s
[Param] username = "' UNION SELECT id, product, amount, 'stolen' FROM orders --"

No user found.

Every attack payload is treated as a plain string value. The database searches for a user whose username is literally ' OR '1'='1 — finds none, and returns nothing.

Why Bound Parameters Work

When you use an f-string, Python merges the SQL and the user input into a single string before it reaches the database:

SELECT ... WHERE username = '' OR '1'='1'

The database receives one string and has no way to tell which part is SQL and which part is data.

With a bound parameter, PyMySQL sends two separate things to the database server:

1. The query template: SELECT ... WHERE username = %s
2. The value: ' OR '1'='1 as raw data

The server compiles the template first, then slots in the value as data — it is never parsed as SQL syntax. Quotes, semicolons, and SQL keywords inside the value are all inert.

%s vs '%s'

Do not quote the placeholder in your query string. Write WHERE username = %s, not WHERE username = '%s'. PyMySQL adds the correct quoting itself. Wrapping the placeholder in quotes turns it into a literal string '%s' and breaks parameterization entirely.

Least-Privilege as a Safety Net

Even with parameterization in place, restrict what the database user can do. If injection were somehow possible, a read-only user limits the blast radius considerably.

-- Application user: read and write data only
GRANT SELECT, INSERT, UPDATE, DELETE ON injection_demo.* TO 'appuser'@'%';

-- No DROP, ALTER, CREATE, or FILE

Run schema migrations as a separate user with elevated privileges, separate from the application runtime user.

Summary

Method	Safe	Notes
f-string formatting	No	Never use with user input
% string formatting	No	Same problem, different syntax
cursor.execute(query, (val,))	Yes	PyMySQL bound parameters — always use this
cursor.execute(query, {"key": val})	Yes	Named parameter variant — also safe