PHP MySQL SQL Injection

Critical Risk SQL Injection
sql-injectionphpmysqlidatabase

What it is

SQL injection vulnerability in PHP applications using mysqli where user input is directly concatenated into SQL queries without proper parameterization or prepared statements.

<?php
class UserManager {
    private $connection;

    public function __construct($host, $username, $password, $database) {
        $this->connection = new mysqli($host, $username, $password, $database);
        if ($this->connection->connect_error) {
            die("Connection failed: " . $this->connection->connect_error);
        }
    }

    // VULNERABLE: Direct concatenation
    public function getUserById($userId) {
        $query = "SELECT * FROM users WHERE id = " . $userId;
        $result = $this->connection->query($query);
        return $result->fetch_assoc();
    }

    // VULNERABLE: String interpolation
    public function searchUsers($name, $department) {
        $query = "SELECT * FROM users WHERE name LIKE '%$name%' AND department = '$department'";
        $result = $this->connection->query($query);

        $users = array();
        while ($row = $result->fetch_assoc()) {
            $users[] = $row;
        }
        return $users;
    }

    // VULNERABLE: POST data concatenation
    public function createUser($name, $email, $department) {
        $query = "INSERT INTO users (name, email, department) VALUES ('$name', '$email', '$department')";
        return $this->connection->query($query);
    }

    // VULNERABLE: Dynamic ORDER BY
    public function getUsersSorted($sortBy, $direction) {
        $query = "SELECT * FROM users ORDER BY $sortBy $direction";
        $result = $this->connection->query($query);

        $users = array();
        while ($row = $result->fetch_assoc()) {
            $users[] = $row;
        }
        return $users;
    }

    // VULNERABLE: Update with concatenation
    public function updateUserStatus($userEmail, $newStatus) {
        $query = "UPDATE users SET status = '$newStatus' WHERE email = '$userEmail'";
        return $this->connection->query($query);
    }
}

// Usage with malicious inputs
$userManager = new UserManager("localhost", "user", "password", "testdb");

// These allow SQL injection:
$user = $userManager->getUserById("1 OR 1=1 --");
$users = $userManager->searchUsers("'; DROP TABLE users; --", "IT");
$userManager->createUser("John", "'; DELETE FROM users; --", "IT");
$sorted = $userManager->getUsersSorted("name; DROP TABLE users; --", "ASC");

// Malicious inputs:
// ?id=1 OR 1=1 --
// name: '; DROP TABLE users; --
// department: IT'; UPDATE users SET salary=999999; --
?>
<?php
class UserManager {
    private $connection;

    public function __construct($host, $username, $password, $database) {
        $this->connection = new mysqli($host, $username, $password, $database);
        if ($this->connection->connect_error) {
            die("Connection failed: " . $this->connection->connect_error);
        }

        // Set charset to prevent character set confusion attacks
        $this->connection->set_charset("utf8mb4");
    }

    private function validateUserId($userId) {
        if (!is_numeric($userId) || $userId <= 0) {
            throw new InvalidArgumentException("Invalid user ID");
        }
        return (int)$userId;
    }

    private function validateEmail($email) {
        if (!filter_var($email, FILTER_VALIDATE_EMAIL) || strlen($email) > 100) {
            throw new InvalidArgumentException("Invalid email format");
        }
        return trim($email);
    }

    private function validateDepartment($department) {
        $validDepartments = ['IT', 'HR', 'Finance', 'Marketing', 'Sales'];
        if (!in_array($department, $validDepartments)) {
            throw new InvalidArgumentException("Invalid department: $department");
        }
        return $department;
    }

    // SECURE: Prepared statement with parameter binding
    public function getUserById($userIdStr) {
        $userId = $this->validateUserId($userIdStr);

        $stmt = $this->connection->prepare("SELECT id, name, email, department, status FROM users WHERE id = ?");
        if (!$stmt) {
            throw new RuntimeException("Prepare failed: " . $this->connection->error);
        }

        $stmt->bind_param("i", $userId);
        $stmt->execute();

        $result = $stmt->get_result();
        $user = $result->fetch_assoc();

        $stmt->close();
        return $user;
    }

    // SECURE: Parameterized query with validation
    public function searchUsers($name, $department) {
        // Input validation
        if (strlen($name) > 100) {
            throw new InvalidArgumentException("Search name too long");
        }

        if (!empty($department)) {
            $department = $this->validateDepartment($department);
        }

        // Build query dynamically with parameters
        $conditions = ["1=1"];
        $types = "";
        $params = [];

        if (!empty($name)) {
            $conditions[] = "name LIKE ?";
            $types .= "s";
            $params[] = "%$name%";
        }

        if (!empty($department)) {
            $conditions[] = "department = ?";
            $types .= "s";
            $params[] = $department;
        }

        $query = "SELECT id, name, email, department FROM users WHERE " .
                 implode(" AND ", $conditions) . " LIMIT 100";

        $stmt = $this->connection->prepare($query);
        if (!$stmt) {
            throw new RuntimeException("Prepare failed: " . $this->connection->error);
        }

        if (!empty($params)) {
            $stmt->bind_param($types, ...$params);
        }

        $stmt->execute();
        $result = $stmt->get_result();

        $users = [];
        while ($row = $result->fetch_assoc()) {
            $users[] = $row;
        }

        $stmt->close();
        return $users;
    }

    // SECURE: Prepared statement for inserts
    public function createUser($name, $email, $department) {
        // Input validation
        if (empty($name) || strlen($name) > 100) {
            throw new InvalidArgumentException("Invalid name");
        }

        $email = $this->validateEmail($email);
        $department = $this->validateDepartment($department);

        $stmt = $this->connection->prepare(
            "INSERT INTO users (name, email, department, created_at) VALUES (?, ?, ?, NOW())"
        );
        if (!$stmt) {
            throw new RuntimeException("Prepare failed: " . $this->connection->error);
        }

        $stmt->bind_param("sss", $name, $email, $department);
        $success = $stmt->execute();

        if ($success) {
            $userId = $this->connection->insert_id;
            $stmt->close();
            return $userId;
        } else {
            $error = $stmt->error;
            $stmt->close();
            throw new RuntimeException("Insert failed: $error");
        }
    }

    // SECURE: Whitelisted sort columns
    public function getUsersSorted($sortBy, $direction) {
        $validSortColumns = [
            'name' => 'name',
            'email' => 'email',
            'department' => 'department',
            'created' => 'created_at'
        ];

        $validDirections = ['ASC', 'DESC'];

        if (!isset($validSortColumns[$sortBy])) {
            throw new InvalidArgumentException("Invalid sort column: $sortBy");
        }

        $direction = strtoupper($direction);
        if (!in_array($direction, $validDirections)) {
            throw new InvalidArgumentException("Invalid sort direction: $direction");
        }

        $column = $validSortColumns[$sortBy];

        // Safe because both column and direction are whitelisted
        $query = "SELECT id, name, email, department FROM users ORDER BY $column $direction LIMIT 100";

        $result = $this->connection->query($query);
        if (!$result) {
            throw new RuntimeException("Query failed: " . $this->connection->error);
        }

        $users = [];
        while ($row = $result->fetch_assoc()) {
            $users[] = $row;
        }

        return $users;
    }

    // SECURE: Parameterized update with validation
    public function updateUserStatus($userEmail, $newStatus) {
        $email = $this->validateEmail($userEmail);

        $validStatuses = ['active', 'inactive', 'suspended', 'pending'];
        if (!in_array($newStatus, $validStatuses)) {
            throw new InvalidArgumentException("Invalid status: $newStatus");
        }

        $stmt = $this->connection->prepare(
            "UPDATE users SET status = ?, updated_at = NOW() WHERE email = ?"
        );
        if (!$stmt) {
            throw new RuntimeException("Prepare failed: " . $this->connection->error);
        }

        $stmt->bind_param("ss", $newStatus, $email);
        $success = $stmt->execute();
        $affectedRows = $stmt->affected_rows;

        $stmt->close();

        if (!$success) {
            throw new RuntimeException("Update failed: " . $this->connection->error);
        }

        return $affectedRows > 0;
    }

    public function __destruct() {
        if ($this->connection) {
            $this->connection->close();
        }
    }
}

// Safe usage examples:
try {
    $userManager = new UserManager("localhost", "user", "password", "testdb");

    // These are now safe:
    $user = $userManager->getUserById("123");
    $users = $userManager->searchUsers("John", "IT");
    $newUserId = $userManager->createUser("Jane Doe", "jane@example.com", "HR");
    $sorted = $userManager->getUsersSorted("name", "ASC");

    echo "Operations completed successfully\n";

} catch (InvalidArgumentException $e) {
    echo "Validation error: " . $e->getMessage() . "\n";
} catch (RuntimeException $e) {
    echo "Database error: " . $e->getMessage() . "\n";
} catch (Exception $e) {
    echo "Unexpected error: " . $e->getMessage() . "\n";
}
?>

💡 Why This Fix Works

The vulnerable code uses string concatenation and interpolation to build SQL queries with user input, allowing injection attacks. The fixed version uses mysqli prepared statements with ? placeholders, implements comprehensive input validation, whitelisting for dynamic fields, proper error handling, and transaction support for batch operations.

Why it happens

Direct concatenation of user input into SQL query strings using the . operator is a primary cause of SQL injection in PHP applications.

Root causes

String Concatenation in PHP SQL Queries

Direct concatenation of user input into SQL query strings using the . operator is a primary cause of SQL injection in PHP applications.

Preview example – PHP 
// Vulnerable
$query = "SELECT * FROM users WHERE id = " . $userId;
$result = $connection->query($query);

Variable Interpolation in SQL Strings

Using variable interpolation within double-quoted SQL strings allows direct injection of malicious SQL code.

Preview example – PHP 
// Vulnerable
$query = "SELECT * FROM users WHERE name LIKE '%$name%' AND department = '$department'";
$result = $connection->query($query);

Fixes

1

Use MySQLi Prepared Statements

Always use prepared statements with ? placeholders when working with mysqli in PHP applications.

View implementation – PHP 
// Secure
$stmt = $connection->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $userId);
$stmt->execute();
$result = $stmt->get_result();
2

Implement Input Validation and Sanitization

Validate and sanitize all user input before using it in database queries. Use PHP filter functions and type checking.

View implementation – PHP 
private function validateUserId($userId) {
    if (!is_numeric($userId) || $userId <= 0) {
        throw new InvalidArgumentException("Invalid user ID");
    }
    return (int)$userId;
}

Detect This Vulnerability in Your Code

Sourcery automatically identifies php mysql sql injection and many other security issues in your codebase.

Scan Your Code for Free Explore More Vulnerabilities