1. php
  2. /security
  3. /sql-injection

PHP SQL Injection Prevention

Introduction to SQL Injection

SQL injection is one of the most common and dangerous web application vulnerabilities. It occurs when untrusted user input is directly incorporated into SQL queries without proper sanitization or parameterization. Attackers can exploit this vulnerability to manipulate database queries, potentially gaining unauthorized access to sensitive data, modifying database contents, or even executing system commands.

According to OWASP, injection attacks (primarily SQL injection) consistently rank in the top 3 most critical web application security risks.

How SQL Injection Works

Vulnerable Code Example

<?php
// NEVER DO THIS - Vulnerable to SQL injection
$username = $_POST['username'];
$password = $_POST['password'];

$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysqli_query($connection, $query);

if (mysqli_num_rows($result) > 0) {
    echo "Login successful!";
} else {
    echo "Invalid credentials!";
}
?>

Attack Scenarios

Scenario 1: Authentication Bypass

// Attacker input:
// username: admin' --
// password: anything

// Resulting query:
// SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything'
// The -- comments out the password check, bypassing authentication

Scenario 2: Data Extraction

// Attacker input:
// username: ' UNION SELECT 1, username, password, email FROM users --
// password: anything

// Resulting query:
// SELECT * FROM users WHERE username = '' UNION SELECT 1, username, password, email FROM users --' AND password = 'anything'
// This reveals all usernames, passwords, and emails

Scenario 3: Data Manipulation

// Attacker input:
// username: '; DROP TABLE users; --
// password: anything

// Resulting query:
// SELECT * FROM users WHERE username = ''; DROP TABLE users; --' AND password = 'anything'
// This could delete the entire users table

Prevention Techniques

Prepared statements are the most effective defense against SQL injection. They separate SQL code from data, making it impossible for user input to be interpreted as SQL commands.

Using PDO (PHP Data Objects)

<?php
class SecureUserRepository {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    public function authenticate($username, $password) {
        // Secure: Using prepared statements
        $stmt = $this->pdo->prepare("SELECT id, username, password_hash FROM users WHERE username = ? AND active = 1");
        $stmt->execute([$username]);
        
        $user = $stmt->fetch();
        
        if ($user && password_verify($password, $user['password_hash'])) {
            return [
                'id' => $user['id'],
                'username' => $user['username']
            ];
        }
        
        return false;
    }
    
    public function getUserById($userId) {
        $stmt = $this->pdo->prepare("SELECT id, username, email, created_at FROM users WHERE id = ?");
        $stmt->execute([$userId]);
        
        return $stmt->fetch();
    }
    
    public function searchUsers($searchTerm, $limit = 10) {
        $stmt = $this->pdo->prepare("
            SELECT id, username, email 
            FROM users 
            WHERE username LIKE ? OR email LIKE ? 
            ORDER BY username 
            LIMIT ?
        ");
        
        $searchParam = "%{$searchTerm}%";
        $stmt->execute([$searchParam, $searchParam, $limit]);
        
        return $stmt->fetchAll();
    }
    
    public function updateUser($userId, $email, $firstName, $lastName) {
        $stmt = $this->pdo->prepare("
            UPDATE users 
            SET email = ?, first_name = ?, last_name = ?, updated_at = NOW() 
            WHERE id = ?
        ");
        
        return $stmt->execute([$email, $firstName, $lastName, $userId]);
    }
    
    public function deleteUser($userId) {
        // Soft delete - mark as deleted instead of removing
        $stmt = $this->pdo->prepare("UPDATE users SET deleted_at = NOW(), active = 0 WHERE id = ?");
        return $stmt->execute([$userId]);
    }
    
    public function createUser($username, $email, $password, $firstName, $lastName) {
        $stmt = $this->pdo->prepare("
            INSERT INTO users (username, email, password_hash, first_name, last_name, created_at) 
            VALUES (?, ?, ?, ?, ?, NOW())
        ");
        
        $passwordHash = password_hash($password, PASSWORD_DEFAULT);
        
        if ($stmt->execute([$username, $email, $passwordHash, $firstName, $lastName])) {
            return $this->pdo->lastInsertId();
        }
        
        return false;
    }
}

// Usage
try {
    $pdo = new PDO("mysql:host=localhost;dbname=myapp", $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
    
    $userRepo = new SecureUserRepository($pdo);
    
    // Safe authentication
    $user = $userRepo->authenticate($_POST['username'], $_POST['password']);
    
    if ($user) {
        echo "Welcome, " . htmlspecialchars($user['username']) . "!";
    } else {
        echo "Invalid credentials";
    }
    
} catch (PDOException $e) {
    error_log("Database error: " . $e->getMessage());
    echo "An error occurred. Please try again.";
}
?>

Using MySQLi with Prepared Statements

<?php
class MySQLiSecureRepository {
    private $mysqli;
    
    public function __construct($host, $username, $password, $database) {
        $this->mysqli = new mysqli($host, $username, $password, $database);
        
        if ($this->mysqli->connect_error) {
            throw new Exception("Connection failed: " . $this->mysqli->connect_error);
        }
        
        $this->mysqli->set_charset("utf8mb4");
    }
    
    public function getUserByEmail($email) {
        $stmt = $this->mysqli->prepare("SELECT id, username, email FROM users WHERE email = ? AND active = 1");
        
        if (!$stmt) {
            throw new Exception("Prepare failed: " . $this->mysqli->error);
        }
        
        $stmt->bind_param("s", $email);
        $stmt->execute();
        
        $result = $stmt->get_result();
        $user = $result->fetch_assoc();
        
        $stmt->close();
        
        return $user;
    }
    
    public function getPostsByCategory($categoryId, $limit = 10, $offset = 0) {
        $stmt = $this->mysqli->prepare("
            SELECT p.id, p.title, p.content, p.created_at, u.username 
            FROM posts p 
            JOIN users u ON p.user_id = u.id 
            WHERE p.category_id = ? AND p.published = 1
            ORDER BY p.created_at DESC 
            LIMIT ? OFFSET ?
        ");
        
        if (!$stmt) {
            throw new Exception("Prepare failed: " . $this->mysqli->error);
        }
        
        $stmt->bind_param("iii", $categoryId, $limit, $offset);
        $stmt->execute();
        
        $result = $stmt->get_result();
        $posts = $result->fetch_all(MYSQLI_ASSOC);
        
        $stmt->close();
        
        return $posts;
    }
    
    public function insertComment($postId, $userId, $comment) {
        $stmt = $this->mysqli->prepare("
            INSERT INTO comments (post_id, user_id, comment, created_at) 
            VALUES (?, ?, ?, NOW())
        ");
        
        if (!$stmt) {
            throw new Exception("Prepare failed: " . $this->mysqli->error);
        }
        
        $stmt->bind_param("iis", $postId, $userId, $comment);
        $success = $stmt->execute();
        
        if ($success) {
            $insertId = $this->mysqli->insert_id;
            $stmt->close();
            return $insertId;
        } else {
            $error = $stmt->error;
            $stmt->close();
            throw new Exception("Insert failed: " . $error);
        }
    }
    
    public function __destruct() {
        if ($this->mysqli) {
            $this->mysqli->close();
        }
    }
}

// Usage
try {
    $repo = new MySQLiSecureRepository("localhost", "username", "password", "database");
    
    // Safe email lookup
    $user = $repo->getUserByEmail($_GET['email']);
    
    // Safe category filtering
    $posts = $repo->getPostsByCategory($_GET['category_id'], 20, 0);
    
    // Safe comment insertion
    if ($_SERVER['REQUEST_METHOD'] === 'POST') {
        $commentId = $repo->insertComment($_POST['post_id'], $_SESSION['user_id'], $_POST['comment']);
        echo "Comment added with ID: $commentId";
    }
    
} catch (Exception $e) {
    error_log("Database error: " . $e->getMessage());
    echo "An error occurred. Please try again.";
}
?>

2. Input Validation and Sanitization

While prepared statements prevent SQL injection, input validation provides an additional layer of security and data integrity.

<?php
class InputValidator {
    public static function validateId($id) {
        if (!is_numeric($id) || $id <= 0) {
            throw new InvalidArgumentException("Invalid ID format");
        }
        return (int) $id;
    }
    
    public static function validateEmail($email) {
        $email = filter_var($email, FILTER_SANITIZE_EMAIL);
        if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            throw new InvalidArgumentException("Invalid email format");
        }
        return $email;
    }
    
    public static function validateUsername($username) {
        $username = trim($username);
        if (strlen($username) < 3 || strlen($username) > 50) {
            throw new InvalidArgumentException("Username must be between 3 and 50 characters");
        }
        if (!preg_match('/^[a-zA-Z0-9_]+$/', $username)) {
            throw new InvalidArgumentException("Username can only contain letters, numbers, and underscores");
        }
        return $username;
    }
    
    public static function validateSearchTerm($term) {
        $term = trim($term);
        if (strlen($term) < 2) {
            throw new InvalidArgumentException("Search term must be at least 2 characters");
        }
        // Remove potentially dangerous characters while allowing international characters
        $term = preg_replace('/[<>"\']/', '', $term);
        return $term;
    }
    
    public static function validateSortColumn($column, $allowedColumns) {
        if (!in_array($column, $allowedColumns)) {
            throw new InvalidArgumentException("Invalid sort column");
        }
        return $column;
    }
    
    public static function validateSortDirection($direction) {
        $direction = strtoupper($direction);
        if (!in_array($direction, ['ASC', 'DESC'])) {
            throw new InvalidArgumentException("Invalid sort direction");
        }
        return $direction;
    }
}

class SecurePostRepository {
    private $pdo;
    private $allowedSortColumns = ['id', 'title', 'created_at', 'updated_at'];
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    public function getPost($id) {
        // Validate input
        $id = InputValidator::validateId($id);
        
        $stmt = $this->pdo->prepare("
            SELECT p.*, u.username 
            FROM posts p 
            JOIN users u ON p.user_id = u.id 
            WHERE p.id = ? AND p.published = 1
        ");
        $stmt->execute([$id]);
        
        return $stmt->fetch();
    }
    
    public function searchPosts($searchTerm, $sortBy = 'created_at', $sortDir = 'DESC', $limit = 10) {
        // Validate all inputs
        $searchTerm = InputValidator::validateSearchTerm($searchTerm);
        $sortBy = InputValidator::validateSortColumn($sortBy, $this->allowedSortColumns);
        $sortDir = InputValidator::validateSortDirection($sortDir);
        $limit = InputValidator::validateId($limit);
        
        // Note: Column names cannot be parameterized, but we've validated them against a whitelist
        $stmt = $this->pdo->prepare("
            SELECT p.id, p.title, p.excerpt, p.created_at, u.username 
            FROM posts p 
            JOIN users u ON p.user_id = u.id 
            WHERE (p.title LIKE ? OR p.content LIKE ?) AND p.published = 1
            ORDER BY p.{$sortBy} {$sortDir}
            LIMIT ?
        ");
        
        $searchParam = "%{$searchTerm}%";
        $stmt->execute([$searchParam, $searchParam, $limit]);
        
        return $stmt->fetchAll();
    }
    
    public function getUserPosts($username, $limit = 10) {
        // Validate username
        $username = InputValidator::validateUsername($username);
        
        $stmt = $this->pdo->prepare("
            SELECT p.id, p.title, p.excerpt, p.created_at 
            FROM posts p 
            JOIN users u ON p.user_id = u.id 
            WHERE u.username = ? AND p.published = 1
            ORDER BY p.created_at DESC 
            LIMIT ?
        ");
        
        $stmt->execute([$username, $limit]);
        
        return $stmt->fetchAll();
    }
}

// Usage with error handling
try {
    $postRepo = new SecurePostRepository($pdo);
    
    // Get single post
    $post = $postRepo->getPost($_GET['id']);
    
    // Search with sorting
    $posts = $postRepo->searchPosts(
        $_GET['q'], 
        $_GET['sort'] ?? 'created_at',
        $_GET['dir'] ?? 'DESC',
        $_GET['limit'] ?? 10
    );
    
    // Get user posts
    $userPosts = $postRepo->getUserPosts($_GET['username']);
    
} catch (InvalidArgumentException $e) {
    http_response_code(400);
    echo "Invalid input: " . $e->getMessage();
} catch (Exception $e) {
    error_log("Database error: " . $e->getMessage());
    http_response_code(500);
    echo "An error occurred. Please try again.";
}
?>

3. Dynamic Query Building (Advanced)

Sometimes you need to build dynamic queries. Here's how to do it safely:

<?php
class SecureQueryBuilder {
    private $pdo;
    private $allowedColumns = ['id', 'username', 'email', 'created_at', 'status'];
    private $allowedOperators = ['=', '!=', '>', '<', '>=', '<=', 'LIKE', 'IN'];
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    public function buildSearchQuery($filters, $sortBy = 'id', $sortDir = 'ASC', $limit = 10) {
        $whereConditions = [];
        $parameters = [];
        
        // Build WHERE conditions safely
        foreach ($filters as $column => $filter) {
            if (!in_array($column, $this->allowedColumns)) {
                throw new InvalidArgumentException("Invalid column: $column");
            }
            
            $operator = $filter['operator'] ?? '=';
            if (!in_array($operator, $this->allowedOperators)) {
                throw new InvalidArgumentException("Invalid operator: $operator");
            }
            
            $value = $filter['value'];
            
            if ($operator === 'IN') {
                if (!is_array($value)) {
                    throw new InvalidArgumentException("IN operator requires an array value");
                }
                
                $placeholders = str_repeat('?,', count($value) - 1) . '?';
                $whereConditions[] = "$column IN ($placeholders)";
                $parameters = array_merge($parameters, $value);
            } elseif ($operator === 'LIKE') {
                $whereConditions[] = "$column LIKE ?";
                $parameters[] = "%{$value}%";
            } else {
                $whereConditions[] = "$column $operator ?";
                $parameters[] = $value;
            }
        }
        
        // Validate sort parameters
        if (!in_array($sortBy, $this->allowedColumns)) {
            throw new InvalidArgumentException("Invalid sort column: $sortBy");
        }
        
        $sortDir = strtoupper($sortDir);
        if (!in_array($sortDir, ['ASC', 'DESC'])) {
            throw new InvalidArgumentException("Invalid sort direction: $sortDir");
        }
        
        // Build the query
        $sql = "SELECT " . implode(', ', $this->allowedColumns) . " FROM users";
        
        if (!empty($whereConditions)) {
            $sql .= " WHERE " . implode(' AND ', $whereConditions);
        }
        
        $sql .= " ORDER BY $sortBy $sortDir LIMIT ?";
        $parameters[] = $limit;
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($parameters);
        
        return $stmt->fetchAll();
    }
}

// Usage
$queryBuilder = new SecureQueryBuilder($pdo);

try {
    // Safe dynamic filtering
    $filters = [
        'status' => ['operator' => '=', 'value' => 'active'],
        'username' => ['operator' => 'LIKE', 'value' => 'john'],
        'id' => ['operator' => 'IN', 'value' => [1, 2, 3, 4, 5]]
    ];
    
    $results = $queryBuilder->buildSearchQuery($filters, 'created_at', 'DESC', 20);
    
    foreach ($results as $user) {
        echo htmlspecialchars($user['username']) . "\n";
    }
    
} catch (InvalidArgumentException $e) {
    echo "Invalid parameters: " . $e->getMessage();
}
?>

Database Configuration Security

Secure Connection Settings

<?php
class SecureDatabaseConnection {
    private static $instance = null;
    private $pdo;
    
    private function __construct() {
        $host = $_ENV['DB_HOST'] ?? 'localhost';
        $dbname = $_ENV['DB_NAME'] ?? '';
        $username = $_ENV['DB_USERNAME'] ?? '';
        $password = $_ENV['DB_PASSWORD'] ?? '';
        
        $dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";
        
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_PERSISTENT => false,
            PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'"
        ];
        
        try {
            $this->pdo = new PDO($dsn, $username, $password, $options);
        } catch (PDOException $e) {
            error_log("Database connection failed: " . $e->getMessage());
            throw new Exception("Database connection failed");
        }
    }
    
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    public function getConnection() {
        return $this->pdo;
    }
    
    // Prevent cloning
    private function __clone() {}
    
    // Prevent unserialization
    private function __wakeup() {}
}

// Database user privileges should be minimal
// CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';
// GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'app_user'@'localhost';
// GRANT DELETE ON myapp.user_sessions TO 'app_user'@'localhost';
// FLUSH PRIVILEGES;
?>

Error Handling and Logging

<?php
class DatabaseErrorHandler {
    private static $logFile = '/var/log/app/database_errors.log';
    
    public static function handleDatabaseError($exception, $query = '', $params = []) {
        // Log the error with context
        $errorData = [
            'timestamp' => date('Y-m-d H:i:s'),
            'message' => $exception->getMessage(),
            'code' => $exception->getCode(),
            'file' => $exception->getFile(),
            'line' => $exception->getLine(),
            'query' => $query,
            'params' => $params,
            'trace' => $exception->getTraceAsString()
        ];
        
        $logEntry = json_encode($errorData) . "\n";
        file_put_contents(self::$logFile, $logEntry, FILE_APPEND | LOCK_EX);
        
        // In production, don't expose internal error details
        if ($_ENV['APP_ENV'] === 'production') {
            throw new Exception("A database error occurred. Please try again.");
        } else {
            // In development, show more details
            throw new Exception("Database Error: " . $exception->getMessage());
        }
    }
}

class SecureRepository {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    public function executeSecureQuery($query, $params = []) {
        try {
            $stmt = $this->pdo->prepare($query);
            $stmt->execute($params);
            return $stmt;
        } catch (PDOException $e) {
            DatabaseErrorHandler::handleDatabaseError($e, $query, $params);
        }
    }
    
    public function getUser($id) {
        try {
            return $this->executeSecureQuery(
                "SELECT id, username, email FROM users WHERE id = ?",
                [$id]
            )->fetch();
        } catch (Exception $e) {
            error_log("Failed to get user $id: " . $e->getMessage());
            return null;
        }
    }
}
?>

Testing for SQL Injection Vulnerabilities

Automated Testing

<?php
class SQLInjectionTester {
    private $testPayloads = [
        "' OR '1'='1",
        "' OR '1'='1' --",
        "' OR '1'='1' /*",
        "'; DROP TABLE users; --",
        "' UNION SELECT 1,2,3 --",
        "1' OR '1'='1",
        "admin'--",
        "admin' #",
        "admin' /*",
        "' OR 1=1#",
        "' OR 1=1--",
        "' OR 1=1/*",
        "') OR ('1'='1",
        "') OR ('1'='1'--",
        "') OR ('1'='1'/*"
    ];
    
    public function testEndpoint($url, $parameter) {
        $vulnerabilities = [];
        
        foreach ($this->testPayloads as $payload) {
            $testUrl = $url . '?' . $parameter . '=' . urlencode($payload);
            
            $response = file_get_contents($testUrl);
            
            // Check for common SQL error messages
            if ($this->containsSQLError($response)) {
                $vulnerabilities[] = [
                    'payload' => $payload,
                    'url' => $testUrl,
                    'error_found' => true
                ];
            }
        }
        
        return $vulnerabilities;
    }
    
    private function containsSQLError($response) {
        $errorPatterns = [
            'mysql_fetch_array',
            'mysql_fetch_assoc',
            'mysql_fetch_object',
            'mysql_num_rows',
            'You have an error in your SQL syntax',
            'Warning: mysql_',
            'function.mysql',
            'MySQL result index',
            'MySQL Error',
            'MySQL ODBC',
            'MySQL Driver',
            'mysqli_fetch_array',
            'mysqli_fetch_assoc',
            'ORA-[0-9]{5}',
            'Oracle error',
            'Oracle driver',
            'Microsoft OLE DB Provider for ODBC Drivers',
            'Microsoft JET Database',
            'Access Database Engine'
        ];
        
        foreach ($errorPatterns as $pattern) {
            if (stripos($response, $pattern) !== false) {
                return true;
            }
        }
        
        return false;
    }
}

// Usage in testing environment
$tester = new SQLInjectionTester();
$vulnerabilities = $tester->testEndpoint('http://localhost/app/user.php', 'id');

if (!empty($vulnerabilities)) {
    echo "⚠️  SQL Injection vulnerabilities found:\n";
    foreach ($vulnerabilities as $vuln) {
        echo "Payload: " . $vuln['payload'] . "\n";
        echo "URL: " . $vuln['url'] . "\n\n";
    }
} else {
    echo "✅ No obvious SQL injection vulnerabilities detected.\n";
}
?>

Best Practices Summary

  1. Always Use Prepared Statements: Never concatenate user input directly into SQL queries
  2. Validate Input: Check data types, formats, and ranges before database operations
  3. Use Least Privilege: Database users should have minimal necessary permissions
  4. Whitelist Approach: For dynamic queries, validate against allowed columns/tables
  5. Error Handling: Don't expose database errors to users
  6. Regular Testing: Use automated tools to test for SQL injection vulnerabilities
  7. Code Review: Have security-focused code reviews for database interactions
  8. Keep Updated: Regularly update database drivers and PHP versions

For more PHP security topics:

Summary

SQL injection prevention in PHP requires:

  • Prepared Statements: Use PDO or MySQLi with parameter binding
  • Input Validation: Validate all user input before database operations
  • Least Privilege: Limit database user permissions
  • Error Handling: Proper error logging without information disclosure
  • Regular Testing: Automated and manual testing for vulnerabilities
  • Security Awareness: Team education on secure coding practices

SQL injection is preventable with proper coding practices. The key is never trusting user input and always using parameterized queries. Combined with input validation and proper error handling, these techniques provide robust protection against one of the web's most dangerous vulnerabilities.