SQL Injection

What is SQL Injection? 💉

SQL injection is a security vulnerability that allows an attacker to execute arbitrary SQL commands on a database by injecting malicious SQL code through user inputs.

How does SQL Injection work? 🤔

Attackers exploit poorly sanitized user inputs by inserting SQL commands that get executed by the database. For example:

// Vulnerable code
$query = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "'";

An attacker could input admin'; DROP TABLE users; -- to execute destructive commands.

---

Best Practices 📝

• Never concatenate user input directly into SQL queries,
• Use prepared statements for all database interactions,
• Validate and sanitize all user inputs,
• Use appropriate PDO parameter types (PDO::PARAM_INT, PDO::PARAM_STR),
• Enable PDO error mode: $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION),
• Limit database privileges for application users,
• Regularly update PHP and database software.

---

Preventing SQL Injection with PDO 🔒

1. Use Prepared Statements

Prepared statements separate SQL logic from data by pre-compiling the query structure. This prevents user input from being interpreted as SQL commands, making injection attacks impossible.

$pdo = new PDO($dsn, $username, $password);
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->execute([$_POST['username'], $_POST['password']]);
$user = $stmt->fetch();

---

2. Named Parameters 📦

Named parameters provide a more readable alternative to positional parameters. They make queries easier to maintain and reduce the risk of parameter misalignment in complex queries.

$stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (:username, :email)");
$stmt->execute([
    ':username' => $_POST['username'],
    ':email' => $_POST['email']
]);

---

3. Bind Parameters Explicitly 🔒

Explicit parameter binding allows you to specify data types (string, integer, etc.) and provides fine-grained control over how data is handled. This adds an extra layer of type safety.

$stmt = $pdo->prepare("UPDATE users SET email = ? WHERE id = ?");
$stmt->bindParam(1, $_POST['email'], PDO::PARAM_STR);
$stmt->bindParam(2, $_POST['id'], PDO::PARAM_INT);
$stmt->execute();

---

4. Input Validation 🔒

Input validation ensures data meets expected formats and constraints before processing. This prevents malformed data from reaching the database and provides early error detection.

// Validate input types
$id = filter_input(INPUT_POST, 'id', FILTER_VALIDATE_INT);
if ($id === false) {
    throw new InvalidArgumentException('Invalid ID');
}

// Whitelist validation for specific values
$allowed_columns = ['name', 'email', 'created_at'];
if (!in_array($_POST['sort'], $allowed_columns)) {
    throw new InvalidArgumentException('Invalid sort column');
}

---

5. Input Sanitization 🔒

Input sanitization removes or encodes potentially dangerous characters from user input. While not a replacement for prepared statements, it provides defense-in-depth by cleaning data before processing.

// Remove potentially harmful characters
$username = preg_replace('/[^a-zA-Z0-9_-]/', '', $_POST['username']);

// Sanitize strings
$email = filter_var($_POST['email'], FILTER_SANITIZE_EMAIL);
$url = filter_var($_POST['website'], FILTER_SANITIZE_URL);

// Length validation
if (strlen($username) > 50) {
    throw new InvalidArgumentException('Username too long');
}

// Pattern validation
if (!preg_match('/^[a-zA-Z0-9_]{3,20}$/', $username)) {
    throw new InvalidArgumentException('Invalid username format');
}

// Comprehensive input cleaning function
function sanitizeInput($input, $type = 'string') {
    $input = trim($input);
    $input = stripslashes($input);

    switch ($type) {
        case 'email':
            return filter_var($input, FILTER_SANITIZE_EMAIL);
        case 'int':
            return filter_var($input, FILTER_SANITIZE_NUMBER_INT);
        case 'float':
            return filter_var($input, FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
        case 'url':
            return filter_var($input, FILTER_SANITIZE_URL);
        default:
            return htmlspecialchars($input, ENT_QUOTES, 'UTF-8');
    }
}

---

6. Escape Output (Defense in Depth) 🔒

Output escaping prevents stored malicious data from executing when displayed to users. This protects against stored XSS attacks and ensures data integrity when rendering user-generated content.

echo htmlspecialchars($user['username'], ENT_QUOTES, 'UTF-8');