Prepared Statements and Security

Understanding Database Security Risks

Web applications that interact with databases face several security challenges. Understanding these risks is the first step toward developing secure applications.

Primary Database Security Threats

• SQL Injection: Inserting malicious SQL code into queries through user input
• Data Exposure: Unintentionally exposing sensitive database data
• Insufficient Access Controls: Poor user privilege management
• Insecure Credential Storage: Plain text passwords or weak hashing
• Inadequate Error Handling: Leaking database details through errors

SQL Injection in Depth

SQL injection is one of the most common and dangerous web application vulnerabilities, occurring when untrusted user input is incorrectly incorporated into SQL queries.

How SQL Injection Works

Common SQL Injection Techniques

Real-world Impact of SQL Injection

SQL injection attacks have led to numerous high-profile data breaches. Some notable examples include:

• Heartland Payment Systems (2008): 134 million credit card numbers stolen
• Sony Pictures (2011): Personal data of millions of users compromised
• LinkedIn (2012): 6.5 million password hashes leaked
• Yahoo (2013): All 3 billion user accounts compromised
• Equifax (2017): Personal data of 147 million people exposed

These breaches highlight why protecting against SQL injection is critical for any application that interacts with a database.

Prepared Statements: The Primary Defense

Prepared statements are the most effective defense against SQL injection attacks. They work by separating SQL code from user input data.

How Prepared Statements Work

The key security advantage of prepared statements is that the SQL structure and the data are handled separately:

1. The SQL query template is sent to MySQL and parsed/compiled
2. Parameters are sent separately and treated strictly as data values, not executable code
3. Even if parameters contain SQL code, they are treated as literal strings

MySQLi vs. PDO Prepared Statements

Implementing Prepared Statements

MySQLi Prepared Statements

<?php
// Connection setup
$conn = mysqli_connect("localhost", "username", "password", "database");

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Prepare statement
$stmt = mysqli_prepare($conn, "SELECT * FROM users WHERE username = ? AND status = ?");

// Check for errors in prepare
if (!$stmt) {
    die("Prepare failed: " . mysqli_error($conn));
}

// Bind parameters (s = string, i = integer, d = double, b = blob)
$username = "johndoe";
$status = 1;
mysqli_stmt_bind_param($stmt, "si", $username, $status);

// Execute statement
if (!mysqli_stmt_execute($stmt)) {
    die("Execute failed: " . mysqli_stmt_error($stmt));
}

// Get results
$result = mysqli_stmt_get_result($stmt);

// Process results
while ($row = mysqli_fetch_assoc($result)) {
    echo "User: " . $row['username'] . ", Email: " . $row['email'] . "<br>";
}

// Close statement and connection
mysqli_stmt_close($stmt);
mysqli_close($conn);
?>
          

<?php
// Connection setup
$conn = new mysqli("localhost", "username", "password", "database");

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare statement
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND status = ?");

// Check for errors in prepare
if (!$stmt) {
    die("Prepare failed: " . $conn->error);
}

// Bind parameters
$username = "johndoe";
$status = 1;
$stmt->bind_param("si", $username, $status);

// Execute statement
if (!$stmt->execute()) {
    die("Execute failed: " . $stmt->error);
}

// Get results
$result = $stmt->get_result();

// Process results
while ($row = $result->fetch_assoc()) {
    echo "User: " . $row['username'] . ", Email: " . $row['email'] . "<br>";
}

// Close statement and connection
$stmt->close();
$conn->close();
?>
          

PDO Prepared Statements

<?php
try {
    // Connection setup
    $conn = new PDO("mysql:host=localhost;dbname=database", "username", "password");
    
    // Set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Prepare statement
    $stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND status = ?");
    
    // Execute with parameters
    $username = "johndoe";
    $status = 1;
    $stmt->execute([$username, $status]);
    
    // Process results
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "User: " . $row['username'] . ", Email: " . $row['email'] . "<br>";
    }
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Connection closes automatically when script ends
$conn = null;
?>
          

<?php
try {
    // Connection setup
    $conn = new PDO("mysql:host=localhost;dbname=database", "username", "password");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Prepare statement with named parameters
    $stmt = $conn->prepare("SELECT * FROM users WHERE username = :username AND status = :status");
    
    // Bind parameters
    $params = [
        ':username' => 'johndoe',
        ':status' => 1
    ];
    
    // Execute statement
    $stmt->execute($params);
    
    // Process results
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "User: " . $row['username'] . ", Email: " . $row['email'] . "<br>";
    }
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Connection closes automatically when script ends
$conn = null;
?>
          

Beyond Prepared Statements: Additional Security Measures

While prepared statements are your primary defense against SQL injection, a comprehensive security strategy includes multiple layers of protection.

Input Validation

Always validate user input before processing it, even when using prepared statements.

<?php
// Get user input
$user_id = $_GET['id'];

// Validate (ensure it's a positive integer)
if (!is_numeric($user_id) || $user_id <= 0 || floor($user_id) != $user_id) {
    die("Invalid user ID. Must be a positive integer.");
}

// Even after validation, still use a prepared statement
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $user_id);
$stmt->execute();
?>
          

Principle of Least Privilege

Database users should have only the minimum privileges necessary for their function.

-- Create a user with only SELECT privileges on specific tables
CREATE USER 'read_only_user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON myapp_db.products TO 'read_only_user'@'localhost';
GRANT SELECT ON myapp_db.categories TO 'read_only_user'@'localhost';

-- Create a user for a specific application function
CREATE USER 'order_system'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON myapp_db.orders TO 'order_system'@'localhost';
GRANT SELECT, UPDATE ON myapp_db.inventory TO 'order_system'@'localhost';
          

Error Handling and Logging

Proper error handling prevents leakage of sensitive database information.

<?php
try {
    // Database operations
    $stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->bind_param("i", $user_id);
    $stmt->execute();
    
    // Check if user exists
    $result = $stmt->get_result();
    if ($result->num_rows === 0) {
        // User-friendly message
        echo "User not found.";
    }
} catch (Exception $e) {
    // Log the detailed error (not visible to users)
    error_log("Database error: " . $e->getMessage() . " in " . $e->getFile() . " on line " . $e->getLine());
    
    // Show generic error to the user
    echo "Sorry, a system error occurred. Please try again later.";
}
?>
          

Secure Password Management

<?php
// NEVER store plaintext passwords!

// When registering a user
$username = "newuser";
$password = "user_password";

// Hash the password securely with password_hash() (uses bcrypt by default)
$hashed_password = password_hash($password, PASSWORD_DEFAULT);

// Store the hashed password in the database
$stmt = $conn->prepare("INSERT INTO users (username, password_hash) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $hashed_password);
$stmt->execute();

// When verifying a login
$username = "newuser";
$password = "user_password";

// Get the stored hash from the database
$stmt = $conn->prepare("SELECT id, password_hash FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$stmt->execute();
$result = $stmt->get_result();

if ($row = $result->fetch_assoc()) {
    // Verify the password
    if (password_verify($password, $row["password_hash"])) {
        echo "Login successful";
        
        // Check if password needs rehashing (if PHP's defaults have changed)
        if (password_needs_rehash($row["password_hash"], PASSWORD_DEFAULT)) {
            $new_hash = password_hash($password, PASSWORD_DEFAULT);
            // Update the hash in the database
            $update = $conn->prepare("UPDATE users SET password_hash = ? WHERE id = ?");
            $update->bind_param("si", $new_hash, $row["id"]);
            $update->execute();
        }
    } else {
        echo "Invalid password";
    }
} else {
    echo "User not found";
}
?>
          

Common Security Patterns and Antipatterns

When Prepared Statements Aren't Enough

Prepared statements protect against SQL injection in data values, but they can't parameterize table or column names. For these cases, you need additional precautions:

<?php
// UNSAFE - don't do this!
$table_name = $_GET['table'];
$sql = "SELECT * FROM $table_name";  // SQL injection risk!

// SAFE - use a whitelist approach
$table_name = $_GET['table'];
$allowed_tables = ['products', 'categories', 'brands'];

if (in_array($table_name, $allowed_tables)) {
    // Table name is validated against whitelist
    $sql = "SELECT * FROM $table_name";
    $result = $conn->query($sql);
} else {
    die("Invalid table name");
}
?>
          

<?php
// UNSAFE - don't do this!
$sort_column = $_GET['sort'];
$sql = "SELECT * FROM products ORDER BY $sort_column";  // SQL injection risk!

// SAFE - use a whitelist approach
$sort_column = $_GET['sort'];
$allowed_columns = ['name', 'price', 'created_at', 'category_id'];

if (in_array($sort_column, $allowed_columns)) {
    // Column name is validated against whitelist
    $sql = "SELECT * FROM products ORDER BY $sort_column";
    $result = $conn->query($sql);
} else {
    // Default fallback
    $sql = "SELECT * FROM products ORDER BY name";
    $result = $conn->query($sql);
}
?>
          

Building a Secure Data Access Layer

A well-designed data access layer centralizes database operations and enforces security best practices across your application.

<?php
class Database {
    private $host = "localhost";
    private $username = "app_user";
    private $password = "secure_password";
    private $database = "myapp_db";
    private $conn;
    
    // Constructor - establish connection
    public function __construct() {
        try {
            $this->conn = new PDO(
                "mysql:host=$this->host;dbname=$this->database;charset=utf8mb4",
                $this->username,
                $this->password
            );
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // Use real prepared statements
        } catch(PDOException $e) {
            $this->logError("Connection error: " . $e->getMessage());
            throw new Exception("Database connection failed");
        }
    }
    
    // Execute a SELECT query with prepared statement
    public function select($query, $params = []) {
        try {
            $stmt = $this->conn->prepare($query);
            $stmt->execute($params);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch(PDOException $e) {
            $this->logError("Select error: " . $e->getMessage() . " | Query: $query");
            throw new Exception("Database query failed");
        }
    }
    
    // Execute an INSERT query with prepared statement
    public function insert($table, $data) {
        try {
            // Build the query
            $columns = implode(", ", array_keys($data));
            $placeholders = ":" . implode(", :", array_keys($data));
            
            $query = "INSERT INTO $table ($columns) VALUES ($placeholders)";
            
            // Execute the statement
            $stmt = $this->conn->prepare($query);
            $stmt->execute($data);
            
            return $this->conn->lastInsertId();
        } catch(PDOException $e) {
            $this->logError("Insert error: " . $e->getMessage() . " | Table: $table");
            throw new Exception("Database insert failed");
        }
    }
    
    // Execute an UPDATE query with prepared statement
    public function update($table, $data, $where, $whereParams = []) {
        try {
            // Build the SET part
            $set = "";
            foreach(array_keys($data) as $column) {
                $set .= "$column = :$column, ";
            }
            $set = rtrim($set, ", ");
            
            // Prepare the query
            $query = "UPDATE $table SET $set WHERE $where";
            
            // Merge data and where parameters
            $params = array_merge($data, $whereParams);
            
            // Execute the statement
            $stmt = $this->conn->prepare($query);
            $stmt->execute($params);
            
            return $stmt->rowCount();
        } catch(PDOException $e) {
            $this->logError("Update error: " . $e->getMessage() . " | Table: $table");
            throw new Exception("Database update failed");
        }
    }
    
    // Execute a DELETE query with prepared statement
    public function delete($table, $where, $params = []) {
        try {
            $query = "DELETE FROM $table WHERE $where";
            
            $stmt = $this->conn->prepare($query);
            $stmt->execute($params);
            
            return $stmt->rowCount();
        } catch(PDOException $e) {
            $this->logError("Delete error: " . $e->getMessage() . " | Table: $table");
            throw new Exception("Database delete failed");
        }
    }
    
    // Log database errors (customize as needed)
    private function logError($message) {
        // In production, you might log to a file or error monitoring service
        error_log($message);
    }
    
    // Begin a transaction
    public function beginTransaction() {
        return $this->conn->beginTransaction();
    }
    
    // Commit a transaction
    public function commit() {
        return $this->conn->commit();
    }
    
    // Rollback a transaction
    public function rollback() {
        return $this->conn->rollBack();
    }
    
    // Get the database connection
    public function getConnection() {
        return $this->conn;
    }
    
    // Close the connection
    public function __destruct() {
        $this->conn = null;
    }
}
?>
          

<?php
require_once 'Database.php';

try {
    // Create database instance
    $db = new Database();
    
    // Example SELECT
    $users = $db->select("SELECT * FROM users WHERE status = :status LIMIT :limit", [
        ':status' => 1,
        ':limit' => 10
    ]);
    
    foreach ($users as $user) {
        echo $user['username'] . "<br>";
    }
    
    // Example INSERT
    $userId = $db->insert('users', [
        'username' => 'newuser',
        'email' => 'newuser@example.com',
        'password_hash' => password_hash('secure_password', PASSWORD_DEFAULT),
        'created_at' => date('Y-m-d H:i:s')
    ]);
    
    echo "New user created with ID: $userId<br>";
    
    // Example UPDATE
    $updated = $db->update(
        'users', 
        ['email' => 'updated@example.com'], 
        'id = :id', 
        [':id' => $userId]
    );
    
    echo "Updated $updated users<br>";
    
    // Example DELETE
    $deleted = $db->delete('users', 'id = :id', [':id' => $userId]);
    
    echo "Deleted $deleted users<br>";
    
    // Example Transaction
    $db->beginTransaction();
    
    try {
        $orderId = $db->insert('orders', [
            'user_id' => 1,
            'total' => 99.99,
            'order_date' => date('Y-m-d H:i:s')
        ]);
        
        $db->insert('order_items', [
            'order_id' => $orderId,
            'product_id' => 123,
            'quantity' => 2,
            'price' => 49.99
        ]);
        
        $db->commit();
        echo "Order processed successfully<br>";
    } catch (Exception $e) {
        $db->rollback();
        echo "Order processing failed<br>";
    }
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
?>
          

Real-World Security Examples

Secure User Registration and Authentication System

<?php
class User {
    private $db;
    
    public function __construct(Database $database) {
        $this->db = $database;
    }
    
    // Register a new user
    public function register($username, $email, $password) {
        // Validate inputs
        if (empty($username) || empty($email) || empty($password)) {
            throw new Exception("All fields are required");
        }
        
        if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            throw new Exception("Invalid email format");
        }
        
        if (strlen($password) < 8) {
            throw new Exception("Password must be at least 8 characters");
        }
        
        // Check if username already exists
        $existing = $this->db->select(
            "SELECT id FROM users WHERE username = :username",
            [':username' => $username]
        );
        
        if (!empty($existing)) {
            throw new Exception("Username already taken");
        }
        
        // Check if email already exists
        $existing = $this->db->select(
            "SELECT id FROM users WHERE email = :email",
            [':email' => $email]
        );
        
        if (!empty($existing)) {
            throw new Exception("Email already registered");
        }
        
        // Hash password
        $password_hash = password_hash($password, PASSWORD_DEFAULT);
        
        // Create user
        $user_id = $this->db->insert('users', [
            'username' => $username,
            'email' => $email,
            'password_hash' => $password_hash,
            'created_at' => date('Y-m-d H:i:s')
        ]);
        
        return $user_id;
    }
    
    // Authenticate user
    public function login($username, $password) {
        // Validate inputs
        if (empty($username) || empty($password)) {
            throw new Exception("Username and password are required");
        }
        
        // Get user
        $users = $this->db->select(
            "SELECT id, username, password_hash FROM users WHERE username = :username",
            [':username' => $username]
        );
        
        if (empty($users)) {
            // Note: It's generally better not to reveal whether the username exists
            throw new Exception("Invalid username or password");
        }
        
        $user = $users[0];
        
        // Verify password
        if (!password_verify($password, $user['password_hash'])) {
            throw new Exception("Invalid username or password");
        }
        
        // Check if password needs rehashing (if PHP's defaults have changed)
        if (password_needs_rehash($user['password_hash'], PASSWORD_DEFAULT)) {
            $new_hash = password_hash($password, PASSWORD_DEFAULT);
            $this->db->update(
                'users',
                ['password_hash' => $new_hash],
                'id = :id',
                [':id' => $user['id']]
            );
        }
        
        // Update last login
        $this->db->update(
            'users',
            ['last_login' => date('Y-m-d H:i:s')],
            'id = :id',
            [':id' => $user['id']]
        );
        
        // Return user data (avoiding sensitive information)
        return [
            'id' => $user['id'],
            'username' => $user['username']
        ];
    }
    
    // Get user by ID
    public function getUserById($id) {
        $users = $this->db->select(
            "SELECT id, username, email, created_at, last_login FROM users WHERE id = :id",
            [':id' => $id]
        );
        
        return empty($users) ? null : $users[0];
    }
    
    // Change password
    public function changePassword($user_id, $current_password, $new_password) {
        // Validate inputs
        if (empty($current_password) || empty($new_password)) {
            throw new Exception("Current and new passwords are required");
        }
        
        if (strlen($new_password) < 8) {
            throw new Exception("New password must be at least 8 characters");
        }
        
        // Get user
        $users = $this->db->select(
            "SELECT password_hash FROM users WHERE id = :id",
            [':id' => $user_id]
        );
        
        if (empty($users)) {
            throw new Exception("User not found");
        }
        
        // Verify current password
        if (!password_verify($current_password, $users[0]['password_hash'])) {
            throw new Exception("Current password is incorrect");
        }
        
        // Hash new password
        $new_hash = password_hash($new_password, PASSWORD_DEFAULT);
        
        // Update password
        $result = $this->db->update(
            'users',
            ['password_hash' => $new_hash],
            'id = :id',
            [':id' => $user_id]
        );
        
        return $result > 0;
    }
}
?>
          

Secure Product Management API

<?php
// product_api.php

// Include necessary files
require_once 'Database.php';
require_once 'auth.php';

// Verify API token (implementation in auth.php)
$user_id = verifyApiToken();
if (!$user_id) {
    header('HTTP/1.1 401 Unauthorized');
    echo json_encode(['error' => 'Unauthorized access']);
    exit;
}

// Create database connection
$db = new Database();

// Get HTTP method
$method = $_SERVER['REQUEST_METHOD'];

// Get product ID from URL if present
$product_id = isset($_GET['id']) ? (int)$_GET['id'] : null;

// Process request based on method
switch ($method) {
    case 'GET':
        // Get products
        if ($product_id) {
            // Get specific product
            $products = $db->select(
                "SELECT * FROM products WHERE id = :id",
                [':id' => $product_id]
            );
            
            if (empty($products)) {
                header('HTTP/1.1 404 Not Found');
                echo json_encode(['error' => 'Product not found']);
                exit;
            }
            
            echo json_encode($products[0]);
        } else {
            // Get all products with pagination
            $page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
            $limit = isset($_GET['limit']) ? (int)$_GET['limit'] : 10;
            $offset = ($page - 1) * $limit;
            
            // Validate pagination parameters
            if ($page < 1 || $limit < 1 || $limit > 100) {
                header('HTTP/1.1 400 Bad Request');
                echo json_encode(['error' => 'Invalid pagination parameters']);
                exit;
            }
            
            // Get products with limits
            $products = $db->select(
                "SELECT * FROM products ORDER BY id DESC LIMIT :limit OFFSET :offset",
                [':limit' => $limit, ':offset' => $offset]
            );
            
            // Get total count for pagination
            $count_result = $db->select("SELECT COUNT(*) as total FROM products");
            $total = $count_result[0]['total'];
            
            echo json_encode([
                'products' => $products,
                'pagination' => [
                    'page' => $page,
                    'limit' => $limit,
                    'total' => (int)$total,
                    'pages' => ceil($total / $limit)
                ]
            ]);
        }
        break;
        
    case 'POST':
        // Check permission for creating products
        if (!hasPermission($user_id, 'create_product')) {
            header('HTTP/1.1 403 Forbidden');
            echo json_encode(['error' => 'Permission denied']);
            exit;
        }
        
        // Get request body
        $data = json_decode(file_get_contents('php://input'), true);
        
        // Validate input
        if (!isset($data['name']) || empty($data['name'])) {
            header('HTTP/1.1 400 Bad Request');
            echo json_encode(['error' => 'Product name is required']);
            exit;
        }
        
        if (!isset($data['price']) || !is_numeric($data['price']) || $data['price'] < 0) {
            header('HTTP/1.1 400 Bad Request');
            echo json_encode(['error' => 'Valid product price is required']);
            exit;
        }
        
        // Create product
        try {
            $product_id = $db->insert('products', [
                'name' => $data['name'],
                'description' => $data['description'] ?? '',
                'price' => $data['price'],
                'stock' => $data['stock'] ?? 0,
                'created_by' => $user_id,
                'created_at' => date('Y-m-d H:i:s')
            ]);
            
            // Get the created product
            $products = $db->select(
                "SELECT * FROM products WHERE id = :id",
                [':id' => $product_id]
            );
            
            header('HTTP/1.1 201 Created');
            echo json_encode($products[0]);
        } catch (Exception $e) {
            header('HTTP/1.1 500 Internal Server Error');
            echo json_encode(['error' => 'Failed to create product']);
            // Log the detailed error
            error_log("Product creation error: " . $e->getMessage());
        }
        break;
        
    case 'PUT':
        // Check permission for updating products
        if (!hasPermission($user_id, 'update_product')) {
            header('HTTP/1.1 403 Forbidden');
            echo json_encode(['error' => 'Permission denied']);
            exit;
        }
        
        // Ensure we have a product ID
        if (!$product_id) {
            header('HTTP/1.1 400 Bad Request');
            echo json_encode(['error' => 'Product ID is required']);
            exit;
        }
        
        // Get request body
        $data = json_decode(file_get_contents('php://input'), true);
        
        // Prepare update data
        $updateData = [];
        
        // Validate and add fields to update
        if (isset($data['name']) && !empty($data['name'])) {
            $updateData['name'] = $data['name'];
        }
        
        if (isset($data['description'])) {
            $updateData['description'] = $data['description'];
        }
        
        if (isset($data['price']) && is_numeric($data['price']) && $data['price'] >= 0) {
            $updateData['price'] = $data['price'];
        }
        
        if (isset($data['stock']) && is_numeric($data['stock']) && $data['stock'] >= 0) {
            $updateData['stock'] = $data['stock'];
        }
        
        if (empty($updateData)) {
            header('HTTP/1.1 400 Bad Request');
            echo json_encode(['error' => 'No valid fields to update']);
            exit;
        }
        
        // Add update metadata
        $updateData['updated_by'] = $user_id;
        $updateData['updated_at'] = date('Y-m-d H:i:s');
        
        // Update product
        try {
            $updated = $db->update(
                'products',
                $updateData,
                'id = :id',
                [':id' => $product_id]
            );
            
            if ($updated == 0) {
                header('HTTP/1.1 404 Not Found');
                echo json_encode(['error' => 'Product not found']);
                exit;
            }
            
            // Get the updated product
            $products = $db->select(
                "SELECT * FROM products WHERE id = :id",
                [':id' => $product_id]
            );
            
            echo json_encode($products[0]);
        } catch (Exception $e) {
            header('HTTP/1.1 500 Internal Server Error');
            echo json_encode(['error' => 'Failed to update product']);
            // Log the detailed error
            error_log("Product update error: " . $e->getMessage());
        }
        break;
        
    case 'DELETE':
        // Check permission for deleting products
        if (!hasPermission($user_id, 'delete_product')) {
            header('HTTP/1.1 403 Forbidden');
            echo json_encode(['error' => 'Permission denied']);
            exit;
        }
        
        // Ensure we have a product ID
        if (!$product_id) {
            header('HTTP/1.1 400 Bad Request');
            echo json_encode(['error' => 'Product ID is required']);
            exit;
        }
        
        // Delete product
        try {
            $deleted = $db->delete(
                'products',
                'id = :id',
                [':id' => $product_id]
            );
            
            if ($deleted == 0) {
                header('HTTP/1.1 404 Not Found');
                echo json_encode(['error' => 'Product not found']);
                exit;
            }
            
            header('HTTP/1.1 200 OK');
            echo json_encode(['message' => 'Product deleted successfully']);
        } catch (Exception $e) {
            header('HTTP/1.1 500 Internal Server Error');
            echo json_encode(['error' => 'Failed to delete product']);
            // Log the detailed error
            error_log("Product deletion error: " . $e->getMessage());
        }
        break;
        
    default:
        header('HTTP/1.1 405 Method Not Allowed');
        header('Allow: GET, POST, PUT, DELETE');
        echo json_encode(['error' => 'Method not allowed']);
        break;
}
?>
          

Practical Activities

Activity 1: SQL Injection Testing

Create a simple form that demonstrates both vulnerable and secure approaches:

1. Create a login form that processes input in two ways:
   • Method 1: Using string concatenation (vulnerable)
   • Method 2: Using prepared statements (secure)
2. Try various SQL injection payloads and observe the differences in behavior
3. Add a logging mechanism to record both successful and failed login attempts
4. Implement a rate-limiting mechanism to prevent brute force attacks

Activity 2: Secure Database Wrapper

Extend the Database class from the examples with additional security features:

1. Add a whitelist mechanism for table names and column names
2. Implement an audit logging system for sensitive operations
3. Add support for connection pooling or persistent connections
4. Create a configuration loader that supports different environments (development, staging, production)

Activity 3: Security Audit

Review and improve an existing database-driven application:

1. Take an existing PHP/MySQL project (your own or an open-source one)
2. Conduct a security audit focusing on database interactions
3. Identify security vulnerabilities (SQL injection, insecure configurations, etc.)
4. Remediate the issues using the techniques learned in this lecture
5. Document the changes and create a security checklist for future projects

Key Takeaways

• SQL injection remains one of the most dangerous web application vulnerabilities
• Prepared statements are the primary defense against SQL injection attacks
• Both MySQLi and PDO support prepared statements with different syntax options
• Input validation should be used even with prepared statements
• Least privilege principle should be applied to database users
• Password hashing is essential for secure user credential storage
• Proper error handling prevents information leakage
• A well-designed data access layer enforces security best practices
• Table and column names need special handling as they can't be parameterized
• Database security requires multiple layers of protection working together

Additional Resources

• OWASP Top 10 Web Application Security Risks
• PHP PDO Manual
• PHP MySQLi Manual
• OWASP SQL Injection Prevention Cheat Sheet
• PHP Password Hashing Functions