Prevention and Mitigation
Why Prevention Matters for OSCP
Understanding how to prevent SQL injection is just as important as knowing how to exploit it. During OSCP:
- You’ll encounter both vulnerable and patched code — knowing the difference helps you identify attack surfaces.
- Report writing requires you to provide remediation recommendations — you need to know the correct fixes.
- Some machines have partially mitigated vulnerabilities — understanding defense gaps helps you find what’s still exploitable.
- The OSCP exam emphasizes ethical hacking methodology — showing you understand both sides demonstrates professionalism.
1. Parameterized Queries (Prepared Statements)
How Parameterized Queries Work
Parameterized queries (also called prepared statements) are the single most effective defense against SQL injection. They work by separating the SQL code from the data:
Traditional (Vulnerable):
Query = "SELECT * FROM users WHERE username = '" + user_input + "'"
→ Data becomes part of the SQL code
Parameterized (Secure):
Query = "SELECT * FROM users WHERE username = ?"
Parameters = [user_input]
→ Data is sent separately and treated as data, never as code
The Two-Phase Execution Model
-
Phase 1 — Prepare: The SQL template (with placeholders) is sent to the database. The database parses, compiles, and creates an execution plan. At this stage, no user data is present.
-
Phase 2 — Execute: The parameter values are sent separately. The database binds them to the placeholders. Because the execution plan is already fixed, the parameters cannot change the query structure — they are always treated as literal data values.
Step 1: Database receives template
"SELECT * FROM users WHERE id = ?"
→ Database compiles this structure
Step 2: Database receives parameters
Parameter 1: "1 OR 1=1"
→ Database searches for a user with id literally equal to "1 OR 1=1"
→ No injection occurs because the query structure cannot change
Why This Prevents SQLi
| Aspect | String Concatenation | Parameterized Query |
|---|---|---|
| User input role | Becomes part of SQL code | Treated as data only |
| Query structure | Can be modified by input | Fixed at prepare time |
| Special characters | Interpreted as SQL syntax | Escaped/treated as literals |
| Execution plan | Built with user input | Built before user input arrives |
PHP — PDO (Recommended)
Vulnerable Code (String Concatenation)
<?php
// VULNERABLE — Never do this
$username = $_POST['username'];
$password = $_POST['password'];
// Direct string concatenation — attacker controls the query structure
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysqli_query($conn, $query);
if (mysqli_num_rows($result) > 0) {
echo "Login successful!";
} else {
echo "Login failed!";
}
?>
Attack:
username: admin' --
password: anything
Resulting query:
SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything'
→ The -- comments out the password check, bypassing authentication
Secure Code — PDO with Prepared Statements
<?php
// SECURE — Using PDO with prepared statements
try {
// Create PDO connection with proper error mode
$pdo = new PDO(
'mysql:host=localhost;dbname=webapp;charset=utf8mb4',
'db_user',
'db_password',
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // Throw exceptions on errors
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // Return associative arrays
PDO::ATTR_EMULATE_PREPARES => false, // Use real prepared statements
]
);
} catch (PDOException $e) {
// Log the error internally, never expose to user
error_log("Database connection failed: " . $e->getMessage());
die("A system error occurred. Please try again later.");
}
$username = $_POST['username'];
$password = $_POST['password'];
// Prepare the statement with placeholders
$stmt = $pdo->prepare("SELECT id, username, role FROM users WHERE username = :username AND password = :password");
// Bind parameters explicitly (type-safe)
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->bindParam(':password', $password, PDO::PARAM_STR);
// Execute with the bound values
$stmt->execute();
// Fetch result
$user = $stmt->fetch();
if ($user) {
// In production, use password_hash() / password_verify() — never store plain text passwords
echo "Login successful! Welcome, " . htmlspecialchars($user['username']);
} else {
echo "Login failed!";
}
?>
Secure Code — PDO with execute() Array (Shorthand)
<?php
// Also SECURE — Passing parameters directly to execute()
$stmt = $pdo->prepare("SELECT id, username, role FROM users WHERE username = ? AND password = ?");
$stmt->execute([$username, $password]);
$user = $stmt->fetch();
?>
Secure Code — mysqli with Prepared Statements
<?php
// SECURE — Using mysqli prepared statements
$conn = new mysqli('localhost', 'db_user', 'db_password', 'webapp');
// Check connection
if ($conn->connect_error) {
error_log("MySQL connection failed: " . $conn->connect_error);
die("A system error occurred.");
}
$username = $_POST['username'];
$password = $_POST['password'];
// Prepare statement
$stmt = $conn->prepare("SELECT id, username, role FROM users WHERE username = ? AND password = ?");
// Bind parameters — 'ss' means two strings
$stmt->bind_param('ss', $username, $password);
// Execute
$stmt->execute();
// Get result
$result = $stmt->get_result();
$user = $result->fetch_assoc();
if ($user) {
echo "Login successful!";
} else {
echo "Login failed!";
}
// Close statement and connection
$stmt->close();
$conn->close();
?>
Critical PDO Configuration
<?php
// This setting is CRITICAL for security:
PDO::ATTR_EMULATE_PREPARES => false
// When true (default in some configs): PDO simulates prepared statements in PHP
// → Still vulnerable to certain injection types
// When false: MySQL driver handles preparation natively
// → True separation of code and data
// Always set this to false for real prepared statement protection
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_EMULATE_PREPARES => false, // REAL prepared statements
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
?>
Python
Vulnerable Code
# VULNERABLE — String formatting in SQL queries
import psycopg2
conn = psycopg2.connect(host="localhost", database="webapp", user="db_user", password="db_pass")
cursor = conn.cursor()
username = request.POST.get('username')
password = request.POST.get('password')
# DANGEROUS — f-string / format / % formatting
query = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"
cursor.execute(query)
# Also VULNERABLE — % string formatting
query = "SELECT * FROM users WHERE username = '%s' AND password = '%s'" % (username, password)
cursor.execute(query)
# Also VULNERABLE — .format()
query = "SELECT * FROM users WHERE username = '{}' AND password = '{}'".format(username, password)
cursor.execute(query)
Secure Code — psycopg2 (PostgreSQL)
# SECURE — Using parameterized queries with psycopg2
import psycopg2
import psycopg2.extras
conn = psycopg2.connect(
host="localhost",
database="webapp",
user="db_user",
password="db_pass"
)
cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
username = request.POST.get('username')
password = request.POST.get('password')
# Use %s placeholders — psycopg2 handles proper escaping
# NOTE: Always use %s, even for integers, dates, etc.
query = "SELECT id, username, role FROM users WHERE username = %s AND password = %s"
cursor.execute(query, (username, password))
user = cursor.fetchone()
if user:
print(f"Login successful! Welcome, {user['username']}")
else:
print("Login failed!")
cursor.close()
conn.close()
Secure Code — sqlite3
# SECURE — Using parameterized queries with sqlite3
import sqlite3
conn = sqlite3.connect('webapp.db')
cursor = conn.cursor()
username = request.POST.get('username')
password = request.POST.get('password')
# Use ? placeholders for sqlite3
query = "SELECT id, username, role FROM users WHERE username = ? AND password = ?"
cursor.execute(query, (username, password))
user = cursor.fetchone()
if user:
print("Login successful!")
else:
print("Login failed!")
conn.close()
Secure Code — pymysql (MySQL)
# SECURE — Using parameterized queries with pymysql
import pymysql
conn = pymysql.connect(
host="localhost",
database="webapp",
user="db_user",
password="db_pass",
cursorclass=pymysql.cursors.DictCursor
)
cursor = conn.cursor()
username = request.POST.get('username')
password = request.POST.get('password')
# Use %s placeholders — pymysql handles escaping
query = "SELECT id, username, role FROM users WHERE username = %s AND password = %s"
cursor.execute(query, (username, password))
user = cursor.fetchone()
cursor.close()
conn.close()
Python — Common Mistakes That Still Cause SQLi
# STILL VULNERABLE — Using format strings for ORDER BY, table names, etc.
# Prepared statements cannot parameterize identifiers!
# BAD — Column name injection
order_by = request.GET.get('sort', 'username')
query = f"SELECT * FROM users ORDER BY {order_by}" # Cannot use placeholder!
cursor.execute(query)
# BAD — Table name injection
table = request.GET.get('table')
query = f"SELECT * FROM {table} LIMIT 10" # Cannot use placeholder!
cursor.execute(query)
# SECURE — Whitelist validation for identifiers
ALLOWED_COLUMNS = ['username', 'email', 'created_at', 'id']
order_by = request.GET.get('sort', 'username')
if order_by not in ALLOWED_COLUMNS:
order_by = 'username' # Default to safe value
query = f"SELECT * FROM users ORDER BY {order_by}" # Now safe — value is validated
cursor.execute(query)
Java — JDBC PreparedStatement
Vulnerable Code
// VULNERABLE — String concatenation in JDBC
import java.sql.*;
public class VulnerableLogin {
public boolean authenticate(String username, String password) throws SQLException {
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/webapp", "db_user", "db_pass"
);
// DANGEROUS — String concatenation
String query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
boolean authenticated = rs.next();
rs.close();
stmt.close();
conn.close();
return authenticated;
}
}
Secure Code — PreparedStatement
// SECURE — Using PreparedStatement
import java.sql.*;
public class SecureLogin {
private static final String LOGIN_QUERY =
"SELECT id, username, role FROM users WHERE username = ? AND password = ?";
public User authenticate(String username, String password) throws SQLException {
// Use try-with-resources for automatic resource cleanup
try (Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/webapp", "db_user", "db_pass");
PreparedStatement pstmt = conn.prepareStatement(LOGIN_QUERY)) {
// Set parameters with explicit types
pstmt.setString(1, username); // First ? placeholder
pstmt.setString(2, password); // Second ? placeholder
// Execute and fetch
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
return new User(
rs.getInt("id"),
rs.getString("username"),
rs.getString("role")
);
}
}
}
return null; // Authentication failed
}
}
Secure Code — Batch Operations
// SECURE — Batch inserts with PreparedStatement
public void insertUsers(List<User> users) throws SQLException {
String insertQuery = "INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)";
try (Connection conn = DriverManager.getConnection(url, user, pass);
PreparedStatement pstmt = conn.prepareStatement(insertQuery)) {
for (User u : users) {
pstmt.setString(1, u.getUsername());
pstmt.setString(2, u.getEmail());
pstmt.setString(3, u.getPasswordHash());
pstmt.addBatch(); // Add to batch
}
pstmt.executeBatch(); // Execute all at once
}
}
Node.js
Vulnerable Code
// VULNERABLE — String concatenation with mysql2
const mysql = require('mysql2');
const conn = mysql.createConnection({
host: 'localhost',
user: 'db_user',
password: 'db_pass',
database: 'webapp'
});
// DANGEROUS — Template literals with user input
app.post('/login', (req, res) => {
const { username, password } = req.body;
const query = `SELECT * FROM users WHERE username = '${username}' AND password = '${password}'`;
conn.query(query, (err, results) => {
if (err) {
res.status(500).send('Error');
return;
}
if (results.length > 0) {
res.send('Login successful!');
} else {
res.send('Login failed!');
}
});
});
Secure Code — mysql2 with Parameterized Queries
// SECURE — Using parameterized queries with mysql2
const mysql = require('mysql2/promise'); // Use promise-based API
// Create connection pool (better for production)
const pool = mysql.createPool({
host: 'localhost',
user: 'db_user',
password: 'db_pass',
database: 'webapp',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
app.post('/login', async (req, res) => {
const { username, password } = req.body;
try {
// Use ? placeholders — mysql2 handles escaping
const [rows] = await pool.execute(
'SELECT id, username, role FROM users WHERE username = ? AND password = ?',
[username, password] // Parameters array
);
if (rows.length > 0) {
res.json({ success: true, user: rows[0] });
} else {
res.status(401).json({ success: false, message: 'Invalid credentials' });
}
} catch (error) {
console.error('Database error:', error.message);
res.status(500).json({ message: 'Internal server error' });
}
});
Secure Code — pg (PostgreSQL)
// SECURE — Using parameterized queries with pg
const { Pool } = require('pg');
const pool = new Pool({
host: 'localhost',
user: 'db_user',
password: 'db_pass',
database: 'webapp',
port: 5432
});
app.post('/login', async (req, res) => {
const { username, password } = req.body;
try {
// Use $1, $2, etc. placeholders for pg
const result = await pool.query(
'SELECT id, username, role FROM users WHERE username = $1 AND password = $2',
[username, password]
);
if (result.rows.length > 0) {
res.json({ success: true, user: result.rows[0] });
} else {
res.status(401).json({ success: false });
}
} catch (error) {
console.error('Query error:', error.message);
res.status(500).json({ message: 'Internal server error' });
}
});
Node.js — Named Parameters (mysql2)
// SECURE — Using named parameters for readability
const [rows] = await pool.execute(
'SELECT * FROM users WHERE username = :username AND email = :email',
{ username: userInput, email: emailInput }
);
C# — SqlParameter
Vulnerable Code
// VULNERABLE — String concatenation in ADO.NET
using System.Data.SqlClient;
public class VulnerableAuth
{
public bool Authenticate(string username, string password)
{
string connectionString = "Server=localhost;Database=webapp;User Id=db_user;Password=db_pass;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
// DANGEROUS — String interpolation
string query = $"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'";
using (SqlCommand cmd = new SqlCommand(query, conn))
using (SqlDataReader reader = cmd.ExecuteReader())
{
return reader.Read(); // True if user found
}
}
}
}
Secure Code — SqlParameter
// SECURE — Using SqlParameter with SqlCommand
using System.Data.SqlClient;
public class SecureAuth
{
private readonly string _connectionString =
"Server=localhost;Database=webapp;User Id=db_user;Password=db_pass;";
public User Authenticate(string username, string password)
{
string query = "SELECT id, username, role FROM users WHERE username = @username AND password = @password";
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(query, conn))
{
// Add parameters with explicit types and sizes
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@password", password);
// Better practice — specify type and size explicitly
// cmd.Parameters.Add("@username", SqlDbType.NVarChar, 50).Value = username;
// cmd.Parameters.Add("@password", SqlDbType.NVarChar, 255).Value = password;
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return new User
{
Id = reader.GetInt32(0),
Username = reader.GetString(1),
Role = reader.GetString(2)
};
}
}
}
}
return null; // Authentication failed
}
}
Secure Code — Dapper (Micro-ORM)
// SECURE — Using Dapper with parameterized queries
using Dapper;
public User Authenticate(string username, string password)
{
string query = "SELECT id, username, role FROM users WHERE username = @username AND password = @password";
using (var conn = new SqlConnection(_connectionString))
{
return conn.QueryFirstOrDefault<User>(query, new
{
username,
password
});
}
}
Go — database/sql
Vulnerable Code
// VULNERABLE — String formatting in Go SQL queries
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
func vulnerableLogin(db *sql.DB, username, password string) bool {
// DANGEROUS — fmt.Sprintf for query building
query := fmt.Sprintf("SELECT * FROM users WHERE username = '%s' AND password = '%s'", username, password)
rows, err := db.Query(query)
if err != nil {
return false
}
defer rows.Close()
return rows.Next()
}
Secure Code — Placeholder Parameters
// SECURE — Using placeholder parameters
package main
import (
"database/sql"
"log"
_ "github.com/go-sql-driver/mysql"
)
type User struct {
ID int
Username string
Role string
}
func secureLogin(db *sql.DB, username, password string) (*User, error) {
query := `SELECT id, username, role FROM users
WHERE username = ? AND password = ?`
row := db.QueryRow(query, username, password)
var user User
err := row.Scan(&user.ID, &user.Username, &user.Role)
if err != nil {
if err == sql.ErrNoRows {
return nil, nil // User not found
}
return nil, err // Database error
}
return &user, nil
}
// Database setup with connection pooling
func setupDB() (*sql.DB, error) {
db, err := sql.Open("mysql", "db_user:db_pass@tcp(localhost:3306)/webapp")
if err != nil {
return nil, err
}
// Configure connection pool
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(5 * time.Minute)
// Verify connection
if err := db.Ping(); err != nil {
return nil, err
}
return db, nil
}
Go — PostgreSQL with Named Placeholders
// For PostgreSQL, use $1, $2 style placeholders
query := `SELECT id, username, role FROM users
WHERE username = $1 AND password = $2`
row := db.QueryRow(query, username, password)
Summary: Parameter Placeholder Styles by Database/Driver
| Database | Driver/Language | Placeholder Style | Example |
|---|---|---|---|
| MySQL | PHP PDO | ? or :name |
WHERE id = ? or WHERE id = :id |
| MySQL | PHP mysqli | ? |
WHERE id = ? |
| MySQL | Python pymysql | %s |
WHERE id = %s |
| MySQL | Node.js mysql2 | ? |
WHERE id = ? |
| MySQL | Go | ? |
WHERE id = ? |
| MySQL | Java JDBC | ? |
WHERE id = ? |
| MySQL | C# | @name |
WHERE id = @id |
| PostgreSQL | Python psycopg2 | %s |
WHERE id = %s |
| PostgreSQL | Node.js pg | $1, $2 |
WHERE id = $1 |
| PostgreSQL | Java JDBC | ? |
WHERE id = ? |
| SQLite | Python sqlite3 | ? |
WHERE id = ? |
| SQLite | Node.js better-sqlite3 | ? or @name |
WHERE id = ? |
2. Input Validation
Input validation is a complementary defense layer. It should never replace parameterized queries but adds an extra barrier.
Whitelist Validation (Most Effective)
Whitelist validation accepts only known-good input. Everything else is rejected.
# SECURE — Whitelist validation for user role
ALLOWED_ROLES = ['admin', 'editor', 'viewer', 'guest']
def get_user_posts(role):
if role not in ALLOWED_ROLES:
raise ValueError(f"Invalid role: {role}")
# Now safe to use in query (though parameterized queries are still preferred)
query = f"SELECT * FROM posts WHERE author_role = '{role}'"
cursor.execute(query)
// SECURE — Whitelist for sort order
$allowedOrders = ['ASC', 'DESC'];
$orderBy = $_GET['order'] ?? 'ASC';
if (!in_array(strtoupper($orderBy), $allowedOrders, true)) {
$orderBy = 'ASC'; // Default fallback
}
// Safe because value is from whitelist
$stmt = $pdo->prepare("SELECT * FROM products ORDER BY price $orderBy");
$stmt->execute();
Blacklist Validation (Why It Fails)
Blacklist validation tries to block known-bad patterns. This approach is fundamentally flawed:
# INSECURE — Blacklist validation (easily bypassed)
def blacklist_filter(input_str):
dangerous = ["'", '"', "--", ";", "OR", "AND", "UNION", "SELECT", "DROP"]
for word in dangerous:
input_str = input_str.replace(word, "")
return input_str
# Bypass examples:
# Input: "admin' --"
# After filter: "admin --" → Still has -- comment
#
# Input: "adm''in" (double single quote)
# After filter: "adm''in" → Can still break out in some contexts
#
# Input: "aOrdEr" (mixed case)
# After filter: "aOrdEr" → Case not handled
#
# Input: "UN/**/ION SELECT" (comment-based bypass)
# After filter: "UN/**/ION SELECT" → Not detected
#
# Input: char(97)+char(100)+char(109)+char(105)+char(110) (encoding bypass)
# After filter: Not detected at all
Why Blacklists Fail — Common Bypass Techniques
| Bypass Technique | Example | Why Blacklist Fails |
|---|---|---|
| Case variation | SeLeCt, sElEcT |
Case-sensitive matching misses variants |
| Double encoding | %2527 → %27 → ' |
Single decode pass doesn’t catch nested encoding |
| Comment insertion | UN/**/ION |
Pattern doesn’t match with embedded comments |
| Whitespace variants | SELECT%09FROM |
Tab/newline/carriage return not in blacklist |
| String concatenation | CONCAT('SEL','ECT') |
Individual words pass the filter |
| Hex encoding | 0x73656c656374 |
Hex representation not detected |
| Backslash escaping | \' vs ' |
Escape character handling varies |
| Unicode normalization | \u0027 → ' |
Unicode forms not always normalized |
Type Checking
# SECURE — Type checking for numeric inputs
def get_user_by_id(user_id):
# Ensure the input is an integer
try:
user_id = int(user_id)
except (ValueError, TypeError):
raise ValueError("User ID must be a number")
# Now safe — guaranteed to be an integer
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
return cursor.fetchone()
// SECURE — Type checking in PHP
$page = $_GET['page'] ?? 1;
// Cast to integer — non-numeric strings become 0
$page = filter_var($page, FILTER_VALIDATE_INT, [
'options' => [
'min_range' => 1,
'max_range' => 1000,
]
]);
if ($page === false) {
$page = 1; // Default
}
// Safe — guaranteed to be a valid integer in range
$offset = ($page - 1) * 10;
$stmt = $pdo->prepare("SELECT * FROM products LIMIT 10 OFFSET ?");
$stmt->execute([$offset]);
Length Limits
# SECURE — Length validation
def validate_input(name, max_length=255):
if len(name) > max_length:
raise ValueError(f"Input exceeds maximum length of {max_length}")
return name
username = validate_input(request.POST.get('username'), max_length=50)
email = validate_input(request.POST.get('email'), max_length=255)
Regex Validation
import re
# SECURE — Regex patterns for common input types
VALIDATION_PATTERNS = {
'email': r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$',
'username': r'^[a-zA-Z0-9_]{3,30}$',
'phone': r'^\+?[1-9]\d{1,14}$',
'zip_code': r'^\d{5}(-\d{4})?$',
'url': r'^https?://[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}(/[\w.-]*)*$',
'integer': r'^-?\d+$',
'positive_integer': r'^\d+$',
'hex_color': r'^#[0-9a-fA-F]{6}$',
}
def validate_field(field_name, value):
if field_name not in VALIDATION_PATTERNS:
raise ValueError(f"No validation pattern for: {field_name}")
pattern = VALIDATION_PATTERNS[field_name]
if re.fullmatch(pattern, value):
return True
return False
# Usage
if validate_field('email', user_email):
# Proceed with validated email
pass
else:
raise ValueError("Invalid email format")
Complete Input Validation Comparison Table
| Method | Security Level | Bypass Difficulty | Best Use Case | Performance |
|---|---|---|---|---|
| Whitelist | Excellent | Very Hard | Enums, fixed sets, known values | Fast |
| Type Checking | Excellent | Very Hard | Numeric IDs, boolean flags | Fast |
| Length Limits | Good (complementary) | Medium | Preventing buffer overflows, DoS | Fast |
| Regex Validation | Good | Medium-Hard | Email, phone, format-specific | Moderate |
| Blacklist | Poor | Easy | Never use alone | Fast |
| Parameterized Queries | Excellent | Impossible (for values) | All SQL value parameters | Minimal overhead |
3. Defense in Depth Architecture
Defense in depth means multiple independent layers of protection. If one layer fails, others still provide security.
┌─────────────────────────────────────────────────────────────┐
│ ATTACKER / USER │
└──────────────────────────┬──────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ Layer 1: TRANSPORT LAYER │
│ • HTTPS/TLS encryption │
│ • HSTS headers │
│ • Certificate pinning │
└──────────────────────────┬──────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ Layer 2: WEB APPLICATION FIREWALL (WAF) │
│ • SQLi pattern detection │
│ • Rate limiting │
│ • Geographic blocking │
└──────────────────────────┬──────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ Layer 3: INPUT VALIDATION │
│ • Type checking │
│ • Whitelist validation │
│ • Length limits │
│ • Format validation (regex) │
└──────────────────────────┬──────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ Layer 4: QUERY LAYER │
│ • Parameterized queries (PRIMARY DEFENSE) │
│ • ORM usage │
│ • Query allowlisting │
└──────────────────────────┬──────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ Layer 5: DATABASE LAYER │
│ • Least privilege accounts │
│ • Row-level security │
│ • Dangerous functions disabled │
│ • Query logging │
└──────────────────────────┬──────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ Layer 6: MONITORING & DETECTION │
│ • Real-time alerting │
│ • Anomaly detection │
│ • Audit logging │
│ • SIEM integration │
└─────────────────────────────────────────────────────────────┘
Layer 1: Transport Layer
# Nginx — Force HTTPS
server {
listen 80;
server_name example.com;
return 301 https://$server_name$request_uri;
}
server {
listen 443 ssl http2;
server_name example.com;
ssl_certificate /etc/ssl/certs/example.com.crt;
ssl_certificate_key /etc/ssl/private/example.com.key;
# Strong TLS configuration
ssl_protocols TLSv1.2 TLSv1.3;
ssl_ciphers HIGH:!aNULL:!MD5:!3DES;
ssl_prefer_server_ciphers on;
# Security headers
add_header Strict-Transport-Security "max-age=31536000; includeSubDomains" always;
add_header X-Content-Type-Options "nosniff" always;
add_header X-Frame-Options "DENY" always;
add_header Content-Security-Policy "default-src 'self'" always;
}
Layer 2: WAF (See Section 6 for details)
Layer 3: Input Validation (See Section 2 for details)
Layer 4: Query Layer (See Section 1 for details)
Layer 5: Database Layer
-- Create a restricted application user with minimal privileges
CREATE USER 'webapp_readonly'@'localhost' IDENTIFIED BY 'strong_password_here';
GRANT SELECT ON webapp.public_data TO 'webapp_readonly'@'localhost';
CREATE USER 'webapp_app'@'localhost' IDENTIFIED BY 'strong_password_here';
GRANT SELECT, INSERT, UPDATE ON webapp.users TO 'webapp_app'@'localhost';
GRANT SELECT, INSERT, UPDATE ON webapp.sessions TO 'webapp_app'@'localhost';
-- NO DROP, NO ALTER, NO CREATE — application doesn't need schema modification
-- NO access to other databases
-- Revoke dangerous privileges
REVOKE FILE ON *.* FROM 'webapp_app'@'localhost';
REVOKE PROCESS ON *.* FROM 'webapp_app'@'localhost';
REVOKE SUPER ON *.* FROM 'webapp_app'@'localhost';
FLUSH PRIVILEGES;
Layer 6: Monitoring and Detection
# SQL injection detection in application logging
import re
import logging
SQLI_PATTERNS = [
r"(?i)(\b(union|select|insert|update|delete|drop|alter|create|exec)\b.*\b(from|into|table|database|where)\b)",
r"(?i)(--|;|/\*|\*/)",
r"(?i)(or|and)\s+\d+\s*=\s*\d+",
r"(?i)(char|concat|hex|unhex)\s*\(",
r"(?i)(sleep|benchmark|waitfor)\s*\(",
r"(?i)(information_schema|sys\.|mysql\.)",
]
logger = logging.getLogger('security')
def detect_sqli_attempt(input_data, field_name):
for pattern in SQLI_PATTERNS:
if re.search(pattern, input_data):
logger.warning(
f"Potential SQLi detected in field '{field_name}': "
f"pattern='{pattern}' input_length={len(input_data)}"
)
return True
return False
4. Database Hardening
Least Privilege Principle
The application database account should have only the permissions it needs:
-- BAD — Giving the app account too many privileges
GRANT ALL PRIVILEGES ON webapp.* TO 'webapp'@'localhost';
-- GOOD — Minimal required privileges
GRANT SELECT, INSERT, UPDATE ON webapp.users TO 'webapp'@'localhost';
GRANT SELECT ON webapp.products TO 'webapp'@'localhost';
GRANT SELECT, INSERT ON webapp.orders TO 'webapp'@'localhost';
| Privilege | When to Grant | Risk if Misused |
|---|---|---|
SELECT |
Reading data | Data exfiltration |
INSERT |
Creating records | Data injection, log poisoning |
UPDATE |
Modifying records | Data tampering |
DELETE |
Removing records | Data destruction |
CREATE |
Schema changes | Table creation, backdoors |
DROP |
Schema removal | Data destruction |
ALTER |
Schema modification | Column/table manipulation |
FILE |
File operations | Read/write arbitrary files |
EXECUTE |
Stored procedures | Execute arbitrary code |
SUPER |
Admin operations | Full server control |
Removing Dangerous Functions
MySQL / MariaDB
-- Disable xp_cmdshell equivalent in MySQL
-- Revoke FILE privilege (prevents LOAD_FILE, INTO OUTFILE)
REVOKE FILE ON *.* FROM 'webapp'@'localhost';
-- Disable LOAD DATA LOCAL INFILE
-- In my.cnf:
-- [mysqld]
-- local_infile=0
-- Disable UDF (User Defined Functions) — prevent arbitrary code execution
-- Ensure only DBA can create functions
REVOKE CREATE ROUTINE ON *.* FROM 'webapp'@'localhost';
-- Disable secure_file_priv to restrict file operations
-- In my.cnf:
-- [mysqld]
-- secure_file_priv="/var/lib/mysql-files/" -- Restrict to specific directory
SQL Server
-- Disable xp_cmdshell (prevents OS command execution)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
-- Disable OLE Automation
EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE;
-- Disable Ad Hoc Distributed Queries
EXEC sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
Error Suppression
<?php
// BAD — Exposing database errors to users
try {
$result = $pdo->query($user_query);
} catch (PDOException $e) {
echo "Error: " . $e->getMessage(); // Shows query, table names, structure!
}
// GOOD — Generic error message, log internally
try {
$result = $pdo->query($user_query);
} catch (PDOException $e) {
// Log full error for developers
error_log("Database error: " . $e->getMessage());
error_log("Stack trace: " . $e->getTraceAsString());
// Show generic message to user
echo "A system error occurred. Please try again later.";
}
?>
# GOOD — Python error handling
import logging
logger = logging.getLogger(__name__)
try:
cursor.execute(query, params)
except psycopg2.Error as e:
logger.error(f"Database error: {e}")
logger.error(f"Query context: params={params}")
return {"error": "A system error occurred. Please try again later."}
Regular Audits
-- Audit: Check for users with excessive privileges
SELECT user, host,
Select_priv, Insert_priv, Update_priv, Delete_priv,
Create_priv, Drop_priv, Shutdown_priv, File_priv,
Grant_priv, Super_priv
FROM mysql.user
WHERE Super_priv = 'Y' OR File_priv = 'Y' OR Grant_priv = 'Y';
-- Audit: Check for stored procedures that might be dangerous
SELECT routine_name, routine_type, created, last_altered
FROM information_schema.routines
WHERE routine_schema = 'webapp';
-- Audit: Check for triggers
SELECT trigger_name, event_manipulation, action_statement
FROM information_schema.triggers
WHERE trigger_schema = 'webapp';
Keeping Software Updated
# Check current MySQL version
mysql --version
# Check for known vulnerabilities
# Visit: https://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=mysql
# Update MySQL (Ubuntu/Debian)
sudo apt update
sudo apt upgrade mysql-server
# Update PHP
sudo apt upgrade php8.2 php8.2-mysql
# Update Python packages
pip install --upgrade psycopg2-binary pymysql
# Update Node.js packages
npm audit
npm audit fix
5. ORM (Object-Relational Mapping)
ORMs abstract database operations and automatically use parameterized queries when used correctly.
Django ORM
Secure Usage
# models.py
from django.db import models
class User(models.Model):
username = models.CharField(max_length=100)
email = models.EmailField()
role = models.CharField(max_length=50)
is_active = models.BooleanField(default=True)
class Meta:
db_table = 'users'
# views.py — SECURE ORM queries
from django.shortcuts import render
from .models import User
def login_view(request):
username = request.POST.get('username')
password = request.POST.get('password')
# SECURE — Django ORM uses parameterized queries internally
user = User.objects.filter(username=username).first()
if user and user.check_password(password):
# Login successful
pass
# SECURE — Chained filters are safe
active_admins = User.objects.filter(role='admin', is_active=True)
# SECURE — Using Q objects for complex queries
from django.db.models import Q
results = User.objects.filter(
Q(username__icontains=search_term) |
Q(email__icontains=search_term)
)
return render(request, 'login.html')
Vulnerable Django — raw() SQL
# VULNERABLE — Using raw() with string formatting
search = request.GET.get('q')
# DANGEROUS — raw() with f-string
users = User.objects.raw(f"SELECT * FROM users WHERE username LIKE '%{search}%'")
# SECURE — raw() with parameterization
users = User.objects.raw(
"SELECT * FROM users WHERE username LIKE %s",
[f'%{search}%']
)
# SECURE — Better: Use ORM filter instead of raw()
users = User.objects.filter(username__icontains=search)
SQLAlchemy
Secure Usage
from sqlalchemy import create_engine, Column, Integer, String, select
from sqlalchemy.orm import sessionmaker, declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
username = Column(String(100))
email = Column(String(255))
role = Column(String(50))
engine = create_engine('mysql+pymysql://db_user:db_pass@localhost/webapp')
Session = sessionmaker(bind=engine)
session = Session()
# SECURE — SQLAlchemy Core with bound parameters
stmt = select(User).where(User.username == username)
user = session.execute(stmt).scalar_one_or_none()
# SECURE — SQLAlchemy ORM
user = session.query(User).filter(User.username == username).first()
# SECURE — Complex queries with and_/or_
from sqlalchemy import and_, or_
results = session.query(User).filter(
or_(
User.username.like(f'%{search}%'),
User.email.like(f'%{search}%')
)
).all()
Vulnerable SQLAlchemy — text() with formatting
from sqlalchemy import text
# VULNERABLE — text() with string formatting
search = request.GET.get('q')
results = session.execute(text(f"SELECT * FROM users WHERE username LIKE '%{search}%'"))
# SECURE — text() with parameter binding
results = session.execute(
text("SELECT * FROM users WHERE username LIKE :search"),
{"search": f"%{search}%"}
)
# SECURE — text() with positional parameters
results = session.execute(
text("SELECT * FROM users WHERE username LIKE '%' || :search || '%'"),
{"search": search}
)
Hibernate (Java)
Secure Usage
// SECURE — Using Hibernate Criteria API
CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<User> query = cb.createQuery(User.class);
Root<User> root = query.from(User.class);
query.where(cb.equal(root.get("username"), username));
User user = session.createQuery(query).getResultStream().findFirst().orElse(null);
// SECURE — Using HQL with parameters
String hql = "FROM User u WHERE u.username = :uname AND u.role = :role";
User user = session.createQuery(hql, User.class)
.setParameter("uname", username)
.setParameter("role", role)
.uniqueResult();
// SECURE — Using JPA named queries
@NamedQuery(
name = "User.findByUsername",
query = "SELECT u FROM User u WHERE u.username = :username"
)
public class User { ... }
// Usage:
User user = entityManager.createNamedQuery("User.findByUsername", User.class)
.setParameter("username", username)
.getSingleResult();
Vulnerable Hibernate
// VULNERABLE — String concatenation in HQL
String hql = "FROM User u WHERE u.username = '" + username + "'";
List<User> users = session.createQuery(hql, User.class).getResultList();
// VULNERABLE — Native SQL with concatenation
String sql = "SELECT * FROM users WHERE username = '" + username + "'";
List<User> users = session.createNativeQuery(sql, User.class).getResultList();
Entity Framework (C#)
Secure Usage
// SECURE — LINQ to Entities (automatically parameterized)
using (var context = new AppDbContext())
{
// Lambda expressions are safe — EF generates parameterized SQL
var user = context.Users
.FirstOrDefault(u => u.Username == username && u.Password == password);
// SECURE — Complex queries
var results = context.Users
.Where(u => u.Username.Contains(searchTerm) || u.Email.Contains(searchTerm))
.ToList();
// SECURE — Using SqlQuery with parameters
var users = context.Users
.FromSqlRaw("SELECT * FROM users WHERE username = {0}", username)
.ToList();
}
Vulnerable Entity Framework
// VULNERABLE — String interpolation in FromSqlRaw
var users = context.Users
.FromSqlRaw($"SELECT * FROM users WHERE username = '{username}'")
.ToList();
// SECURE — Use parameterized FromSqlRaw
var users = context.Users
.FromSqlRaw("SELECT * FROM users WHERE username = {0}", username)
.ToList();
// SECURE — Use FromSqlInterpolated (auto-parameterizes)
var users = context.Users
.FromSqlInterpolated($"SELECT * FROM users WHERE username = {username}")
.ToList();
When ORM Can Still Be Vulnerable
| Scenario | Why It’s Vulnerable | Fix |
|---|---|---|
raw() / text() SQL |
Bypasses ORM parameterization | Use parameter binding in raw queries |
| Dynamic table/column names | ORM can’t parameterize identifiers | Whitelist validation |
ORDER BY with user input |
Column names can’t be parameterized | Whitelist allowed columns |
| Concatenated HQL/Criteria | String building defeats ORM safety | Use proper parameter binding |
| Misconfigured migrations | Schema changes from untrusted input | Validate migration inputs |
6. WAF as a Defense Layer
How WAFs Detect SQLi
Web Application Firewalls inspect HTTP requests for SQL injection patterns:
Request: GET /search?q=admin' OR '1'='1
WAF Detection Steps:
1. Parse request (URL, headers, body, cookies)
2. Decode (URL decode, double decode, Unicode normalize)
3. Match against SQLi rule signatures:
- Keywords: UNION, SELECT, INSERT, DROP, EXEC
- Operators: OR, AND, =, !=, <, >
- Comments: --, #, /* */
- Functions: SLEEP(), BENCHMARK(), CHAR()
- Structure: ' OR '1'='1, 1=1--, UNION SELECT
4. Score the request (confidence level)
5. Block, challenge, or allow based on score
ModSecurity Ruleset Example
# ModSecurity SQL Injection Detection Rules
# Rule: Detect common SQL injection keywords
SecRule ARGS "@rx (?i)(union\s+select|select\s+.*\s+from|insert\s+into|delete\s+from|drop\s+table)" \
"id:1001,\
phase:2,\
deny,\
status:403,\
log,\
msg:'SQL Injection Attempt Detected',\
severity:'CRITICAL',\
tag:'attack-sqli'"
# Rule: Detect SQL comment sequences
SecRule ARGS "@rx (--|#|/\*)" \
"id:1002,\
phase:2,\
deny,\
status:403,\
log,\
msg:'SQL Comment Sequence Detected',\
severity:'WARNING'"
# Rule: Detect tautology-based attacks
SecRule ARGS "@rx (?i)(\b(or|and)\b\s+\d+\s*=\s*\d+)" \
"id:1003,\
phase:2,\
deny,\
status:403,\
log,\
msg:'SQL Tautology Attack Detected',\
severity:'CRITICAL'"
# Rule: Detect time-based blind SQLi
SecRule ARGS "@rx (?i)(sleep|benchmark|waitfor\s+delay)" \
"id:1004,\
phase:2,\
deny,\
status:403,\
log,\
msg:'Time-Based SQLi Attempt Detected',\
severity:'CRITICAL'"
OWASP CRS (Core Rule Set)
# Include OWASP ModSecurity Core Rule Set
Include owasp-crs/crs-setup.conf
Include owasp-crs/rules/*.conf
# CRS provides comprehensive rules for:
# - SQL Injection (PARSI)
# - XSS (R7032)
# - RCE (PAROS)
# - File inclusion
# - HTTP protocol violations
# - Session fixation
Limitations of WAF-Only Defense
| Limitation | Description | Impact |
|---|---|---|
| False negatives | Sophisticated attacks may bypass signatures | Attack succeeds despite WAF |
| False positives | Legitimate requests may be blocked | Service disruption, user frustration |
| Encoding bypasses | Double encoding, Unicode, chunked encoding | WAF may not decode all variants |
| Logic-based attacks | Attacks that don’t use SQL keywords | WAF signature matching fails |
| In-band attacks | Attacks through authenticated sessions | WAF may trust authenticated traffic |
| Performance impact | Deep inspection adds latency | May affect application performance |
| Maintenance burden | Rules need constant updating | Outdated rules = outdated protection |
| No code-level fix | WAF doesn’t fix the vulnerable code | Vulnerability still exists in application |
WAF Bypass Techniques (For Understanding)
# Technique 1: Double URL Encoding
# Input: %2527 (decodes to %27 which decodes to ')
GET /search?q=%2527%20OR%201%3D1--
# Technique 2: HTTP Parameter Pollution
# Some WAFs only check the first occurrence
GET /search?q=normal&q=' OR 1=1--
# Technique 3: Case Variation
GET /search?q=UnIoN%20SeLeCt%201,2,3
# Technique 4: Whitespace Substitution
GET /search?q=1%09UNION%0ASELECT%0D1,2,3
# Technique 5: Comment Obfuscation
GET /search?q=1/**/UNION/**/SELECT/**/1,2,3
# Technique 6: String Splitting
GET /search?q=UN%49ON%20SEL%45CT (hex encoding parts)
Key Takeaway: WAF is a complementary layer, not a replacement for secure coding.
7. OSCP Report Writing
Documenting SQLi Findings
Vulnerability Description Template
## Vulnerability: SQL Injection in Login Form
### Severity: Critical (CVSS 3.1: 9.8)
### Affected Component
- URL: http://10.10.10.100/login.php
- Parameter: username (POST)
- HTTP Method: POST
### Description
The login form at `/login.php` is vulnerable to SQL injection through the
`username` parameter. User-supplied input is directly concatenated into a SQL
query without proper sanitization or parameterization, allowing an attacker
to manipulate the database query logic.
### Proof of Concept
#### Step 1: Identify the Vulnerability
```http
POST /login.php HTTP/1.1
Host: 10.10.10.100
Content-Type: application/x-www-form-urlencoded
username=admin'%20OR%20'1'%3D'1'--%20&password=anything
Step 2: Authentication Bypass
The above payload bypasses authentication by commenting out the password check:
-- Original query (inferred):
SELECT * FROM users WHERE username = '$username' AND password = '$password'
-- With injection:
SELECT * FROM users WHERE username = 'admin' OR '1'='1'--' AND password = 'anything'
Step 3: Data Extraction via UNION-based SQLi
POST /login.php HTTP/1.1
Host: 10.10.10.100
Content-Type: application/x-www-form-urlencoded
username=' UNION SELECT 1,table_name,3 FROM information_schema.tables-- &password=x
Result: Retrieved database table names including users, admin_credentials, ssh_keys.
Step 4: Credential Extraction
POST /login.php HTTP/1.1
Host: 10.10.10.100
Content-Type: application/x-www-form-urlencoded
username=' UNION SELECT username,password,3 FROM users-- &password=x
Result: Retrieved plaintext credentials for admin account.
Impact
- Authentication Bypass: Attacker can log in as any user without credentials
- Data Exfiltration: Full database contents can be extracted
- Privilege Escalation: Admin credentials allow full system access
- Complete System Compromise: SSH keys were stored in the database, enabling remote server access
Root Cause
The application uses string concatenation to build SQL queries instead of parameterized queries:
// Vulnerable code (inferred from behavior):
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysqli_query($conn, $query);
Remediation
Immediate Fix
Replace string concatenation with parameterized queries:
// Secure implementation using PDO:
$stmt = $pdo->prepare("SELECT id, username, role FROM users WHERE username = :username AND password = :password");
$stmt->execute([':username' => $username, ':password' => $password]);
$user = $stmt->fetch();
Additional Recommendations
- Implement input validation with whitelist approach
- Use an ORM (e.g., Doctrine) for database abstraction
- Apply the principle of least privilege to database accounts
- Suppress detailed database error messages in production
- Implement a Web Application Firewall (WAF) as an additional layer
- Use prepared statements consistently across the entire application
- Conduct a code review to identify other instances of string concatenation
- Store passwords using bcrypt/argon2 hashing, never in plaintext
- Never store SSH keys or sensitive credentials in the database
- Implement rate limiting on authentication endpoints
### Risk Rating Methodology
| Factor | Weight | SQLi Typical Score |
|--------|--------|-------------------|
| **Exploitability** | 30% | High (easy to exploit, no special tools needed) |
| **Impact** | 40% | Critical (full database access, potential RCE) |
| **Scope** | 20% | High (affects all data in the database) |
| **Detectability** | 10% | Medium (may go unnoticed without monitoring) |
#### CVSS 3.1 Scoring for SQLi
Base Score: 9.8 (Critical) Vector: CVSS:3.1/AV:N/AC:L/PR:N/UI:N/S:U/C:H/I:H/A:H
Breakdown:
- Attack Vector (AV): Network (N) — Remotely exploitable
- Attack Complexity (AC): Low (L) — Simple payloads work
- Privileges Required (PR): None (N) — No auth needed (if unauthenticated)
- User Interaction (UI): None (N) — No user interaction needed
- Scope (S): Unchanged (U) — Impact within same scope
- Confidentiality (C): High (H) — Full data access
- Integrity (I): High (H) — Data can be modified
- Availability (A): High (A) — Database can be destroyed
### Remediation Recommendations Priority
| Priority | Action | Effort | Impact |
|----------|--------|--------|--------|
| **P0 — Immediate** | Replace all string concatenation with parameterized queries | Medium | Eliminates SQLi |
| **P0 — Immediate** | Rotate all credentials that may have been exposed | Low | Limits breach impact |
| **P1 — Short-term** | Implement input validation layer | Medium | Defense in depth |
| **P1 — Short-term** | Reduce database account privileges | Low | Limits blast radius |
| **P2 — Medium-term** | Deploy WAF with SQLi rules | Medium | Additional detection |
| **P2 — Medium-term** | Implement comprehensive logging and monitoring | Medium | Faster incident response |
| **P3 — Long-term** | Migrate to ORM framework | High | Structural improvement |
| **P3 — Long-term** | Conduct full application security audit | High | Find remaining issues |
---
## Quick Reference: Vulnerable vs Secure Patterns
### PHP
```php
// ❌ VULNERABLE
$query = "SELECT * FROM users WHERE id = $_GET[id]";
$result = mysqli_query($conn, $query);
// ✅ SECURE
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$_GET['id']]);
Python
# ❌ VULNERABLE
cursor.execute(f"SELECT * FROM users WHERE id = {user_id}")
# ✅ SECURE
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
Java
// ❌ VULNERABLE
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE id = " + userId);
// ✅ SECURE
PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
pstmt.setInt(1, userId);
ResultSet rs = pstmt.executeQuery();
Node.js
// ❌ VULNERABLE
conn.query(`SELECT * FROM users WHERE id = ${req.params.id}`);
// ✅ SECURE
conn.execute('SELECT * FROM users WHERE id = ?', [req.params.id]);
C#
// ❌ VULNERABLE
var cmd = new SqlCommand($"SELECT * FROM users WHERE id = {userId}", conn);
// ✅ SECURE
var cmd = new SqlCommand("SELECT * FROM users WHERE id = @id", conn);
cmd.Parameters.AddWithValue("@id", userId);
Go
// ❌ VULNERABLE
query := fmt.Sprintf("SELECT * FROM users WHERE id = %s", userID)
rows, _ := db.Query(query)
// ✅ SECURE
rows, err := db.Query("SELECT * FROM users WHERE id = ?", userID)
Key Takeaways for OSCP
-
Parameterized queries are the #1 defense — If you see string concatenation in SQL queries during code review, flag it immediately.
-
Look for the vulnerable patterns — During OSCP enumeration, check source code, JavaScript files, and API endpoints for SQL query construction.
-
ORMs are not bulletproof — Raw queries, dynamic identifiers, and misconfigured ORMs can still be vulnerable.
-
Defense in depth matters — Even with parameterized queries, input validation and least privilege limit the impact of any remaining vulnerabilities.
-
Error messages are gold — Detailed database errors help you identify the database type, table names, and column names during exploitation.
-
Report quality matters — Clear remediation recommendations with code examples demonstrate professional competence and are expected in OSCP reports.
-
WAF is not a silver bullet — Understanding WAF bypass techniques helps you test whether a WAF is properly configured.
-
The ORDER BY / LIMIT / table name problem — These SQL elements cannot be parameterized. Always use whitelist validation for dynamic identifiers.
Practice Exercises
Exercise 1: Identify Vulnerable Code
Review the following snippets and identify which are vulnerable:
// A
$stmt = $pdo->prepare("SELECT * FROM products WHERE category = ?");
$stmt->execute([$_GET['cat']]);
// B
$query = "SELECT * FROM products WHERE category = '{$_GET['cat']}'";
$result = mysqli_query($conn, $query);
// C
$stmt = $pdo->prepare("SELECT * FROM products ORDER BY {$_GET['sort']}");
$stmt->execute();
// D
$allowed = ['name', 'price', 'date'];
$sort = in_array($_GET['sort'], $allowed) ? $_GET['sort'] : 'name';
$stmt = $pdo->prepare("SELECT * FROM products ORDER BY $sort");
$stmt->execute();
Answers:
- A: ✅ Secure (parameterized)
- B: ❌ Vulnerable (string interpolation)
- C: ❌ Vulnerable (column name cannot be parameterized, no validation)
- D: ✅ Secure (whitelist validation for column name)
Exercise 2: Fix the Vulnerability
# Original vulnerable code
def search_products(request):
search = request.GET.get('q', '')
category = request.GET.get('cat', '')
query = f"SELECT * FROM products WHERE name LIKE '%{search}%' AND category = '{category}'"
cursor.execute(query)
return cursor.fetchall()
# Your fix:
def search_products(request):
search = request.GET.get('q', '')
category = request.GET.get('cat', '')
# FIXED: Using parameterized query
query = "SELECT * FROM products WHERE name LIKE %s AND category = %s"
cursor.execute(query, (f'%{search}%', category))
return cursor.fetchall()
Exercise 3: Write a Remediation Recommendation
Practice writing a remediation section for an OSCP report. Include:
- The specific vulnerable code pattern found
- The secure replacement code
- Additional defense-in-depth recommendations
- Priority level and estimated effort
Additional Resources
- OWASP SQL Injection Prevention Cheat Sheet
- OWASP Top 10 — A03:2021 Injection
- PortSwigger Web Security Academy — SQL Injection
- Bobby Tables — xkcd
- SQLMap Documentation
This study guide is part of the OSCP Web Application Attacks domain. Understanding prevention strategies helps you identify vulnerable code patterns during assessments and write professional-quality reports with actionable remediation recommendations.