SQL injection from user data in manual SQL string in PHP

Critical Risk SQL Injection
phpsql-injectiondatabasewebmysqlipdo

What it is

SQL injection vulnerability in PHP applications where HTTP request data is concatenated into SQL query strings without parameterization or proper binding, allowing attackers to manipulate database operations and bypass application logic.

<?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;
    }

    // Example with transaction support
    public function batchUpdateStatuses($updates) {
        $this->connection->autocommit(FALSE);

        try {
            $stmt = $this->connection->prepare(
                "UPDATE users SET status = ?, updated_at = NOW() WHERE email = ?"
            );

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

            $totalUpdated = 0;

            foreach ($updates as $update) {
                $email = $this->validateEmail($update['email']);
                $status = $update['status'];

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

                $stmt->bind_param("ss", $status, $email);
                $stmt->execute();
                $totalUpdated += $stmt->affected_rows;
            }

            $stmt->close();
            $this->connection->commit();
            $this->connection->autocommit(TRUE);

            return $totalUpdated;

        } catch (Exception $e) {
            $this->connection->rollback();
            $this->connection->autocommit(TRUE);
            throw $e;
        }
    }

    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
";

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

💡 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

HTTP request data is concatenated into SQL query strings without parameterization or proper binding, allowing attacker-controlled tokens to alter query structure.

Root causes

Request Data Concatenation in SQL

HTTP request data is concatenated into SQL query strings without parameterization or proper binding, allowing attacker-controlled tokens to alter query structure.

Preview example – PHP 
<?php
// VULNERABLE: Direct concatenation
$userId = $_GET['id'];
$query = "SELECT * FROM users WHERE id = " . $userId;
$result = mysqli_query($connection, $query);
// Allows injection: ?id=1 OR 1=1 --

String Interpolation with User Input

Using PHP string interpolation or concatenation to embed user input directly into SQL queries without proper escaping or parameterization.

Preview example – PHP 
<?php
// VULNERABLE: String interpolation
$name = $_POST['name'];
$email = $_POST['email'];
$query = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
mysqli_query($connection, $query);
// Allows injection: name = "'; DROP TABLE users; --"

Fixes

1

Use Prepared Statements with mysqli or PDO

Replace string concatenation with prepared statements (mysqli or PDO). Use mysqli->prepare() with bind_param() or PDO::prepare() with bound parameters.

View implementation – PHP 
<?php
// SECURE: mysqli prepared statement
$userId = $_GET['id'];
$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $userId);
$stmt->execute();
$result = $stmt->get_result();
2

Use PDO with Named Parameters

Consider an ORM to handle queries safely, or use PDO with named parameters for better readability and security.

View implementation – PHP 
<?php
// SECURE: PDO with named parameters
$name = $_POST['name'];
$email = $_POST['email'];
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$stmt->execute();

Detect This Vulnerability in Your Code

Sourcery automatically identifies sql injection from user data in manual sql string in php and many other security issues in your codebase.

Scan Your Code for Free Explore More Vulnerabilities