PHP Fundamentals

Prepared Statements & SQL Injection Prevention

13 min Lesson 38 of 45

Understanding SQL Injection

SQL injection is one of the most dangerous web application vulnerabilities. It occurs when an attacker can insert malicious SQL code into your queries through user input.

Example of Vulnerable Code

<?php
// NEVER DO THIS!
$username = $_POST['username'];
$password = $_POST['password'];

$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = $conn->query($sql);
?>

Danger! If an attacker enters ' OR '1'='1 as the username, the query becomes:
SELECT * FROM users WHERE username = '' OR '1'='1' AND password = ''
This returns all users because '1'='1' is always true!

How SQL Injection Works

Attackers can exploit SQL injection to:

Bypass authentication: Log in without valid credentials
Steal data: Access sensitive information from the database
Modify data: Update or delete records
Execute commands: Run system commands on the server
Drop tables: Delete entire databases

Common SQL Injection Examples

// Authentication Bypass
Username: admin'--
Password: anything

// Returns all users
Username: ' OR '1'='1
Password: ' OR '1'='1

// Stealing data via UNION
Username: ' UNION SELECT credit_card FROM payments--
Password: anything

// Deleting tables
Username: '; DROP TABLE users--
Password: anything

Prepared Statements: The Solution

Prepared statements separate SQL code from data, making SQL injection impossible. They work in two steps:

Prepare: Define the SQL structure with placeholders
Bind & Execute: Bind parameters and execute the query

Basic Prepared Statement Syntax

<?php
// Step 1: Prepare statement with placeholders (?)
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND email = ?");

// Step 2: Bind parameters (s = string, i = integer, d = double, b = blob)
$stmt->bind_param("ss", $username, $email);

// Step 3: Set parameter values
$username = "johndoe";
$email = "john@example.com";

// Step 4: Execute
$stmt->execute();

// Step 5: Get results
$result = $stmt->get_result();
$user = $result->fetch_assoc();

// Step 6: Close statement
$stmt->close();
?>

Parameter Type Specifiers

Type	Description	Example
`i`	Integer	42, -10, 0
`d`	Double/Float	3.14, -0.5
`s`	String	'hello', 'user@email.com'
`b`	Blob (binary)	Image data, files

Tip: When in doubt, use 's' (string) for most values. MySQL will handle type conversion.

SELECT with Prepared Statements

<?php
require_once 'database.php';

// User input (from form)
$search_username = $_POST['username'];

// Prepare statement
$stmt = $conn->prepare("SELECT id, username, email, full_name FROM users WHERE username = ?");

// Bind parameter
$stmt->bind_param("s", $search_username);

// Execute
$stmt->execute();

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

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "User: " . $row['username'] . " (" . $row['email'] . ")<br>";
    }
} else {
    echo "No user found";
}

$stmt->close();
?>

INSERT with Prepared Statements

<?php
require_once 'database.php';

// User input
$username = $_POST['username'];
$email = $_POST['email'];
$password = password_hash($_POST['password'], PASSWORD_DEFAULT);
$full_name = $_POST['full_name'];

// Prepare statement
$stmt = $conn->prepare("INSERT INTO users (username, email, password, full_name) VALUES (?, ?, ?, ?)");

// Bind parameters (4 strings)
$stmt->bind_param("ssss", $username, $email, $password, $full_name);

// Execute
if ($stmt->execute()) {
    echo "User created successfully<br>";
    echo "New user ID: " . $stmt->insert_id;
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
?>

UPDATE with Prepared Statements

<?php
require_once 'database.php';

// User input
$new_email = $_POST['email'];
$new_full_name = $_POST['full_name'];
$user_id = $_POST['user_id'];

// Prepare statement
$stmt = $conn->prepare("UPDATE users SET email = ?, full_name = ? WHERE id = ?");

// Bind parameters (2 strings, 1 integer)
$stmt->bind_param("ssi", $new_email, $new_full_name, $user_id);

// Execute
if ($stmt->execute()) {
    echo "User updated successfully<br>";
    echo "Rows affected: " . $stmt->affected_rows;
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
?>

DELETE with Prepared Statements

<?php
require_once 'database.php';

// User input
$user_id = $_POST['user_id'];

// Prepare statement
$stmt = $conn->prepare("DELETE FROM users WHERE id = ?");

// Bind parameter
$stmt->bind_param("i", $user_id);

// Execute
if ($stmt->execute()) {
    if ($stmt->affected_rows > 0) {
        echo "User deleted successfully";
    } else {
        echo "No user found with that ID";
    }
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
?>

Multiple Parameters Example

<?php
require_once 'database.php';

// Search users with multiple criteria
$search_name = $_GET['name'];
$min_age = $_GET['min_age'];
$max_age = $_GET['max_age'];
$city = $_GET['city'];

$stmt = $conn->prepare("
    SELECT * FROM users
    WHERE full_name LIKE ?
    AND age BETWEEN ? AND ?
    AND city = ?
    ORDER BY created_at DESC
");

// Add wildcards for LIKE search
$search_name = "%$search_name%";

// Bind parameters (string, int, int, string)
$stmt->bind_param("siis", $search_name, $min_age, $max_age, $city);

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

while ($row = $result->fetch_assoc()) {
    echo $row['full_name'] . " - " . $row['city'] . "<br>";
}

$stmt->close();
?>

Binding Results (Alternative Method)

Instead of get_result(), you can bind result columns to variables:

<?php
require_once 'database.php';

$username = "johndoe";

$stmt = $conn->prepare("SELECT id, username, email FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$stmt->execute();

// Bind result columns to variables
$stmt->bind_result($id, $db_username, $email);

// Fetch the row
if ($stmt->fetch()) {
    echo "ID: $id<br>";
    echo "Username: $db_username<br>";
    echo "Email: $email<br>";
} else {
    echo "User not found";
}

$stmt->close();
?>

Note: get_result() is more flexible and commonly used, but bind_result() uses less memory.

Complete Secure Login Example

<?php
require_once 'database.php';
session_start();

function login($conn, $username, $password) {
    // Prepare statement
    $stmt = $conn->prepare("SELECT id, username, password, full_name FROM users WHERE username = ?");

    // Bind parameter
    $stmt->bind_param("s", $username);

    // Execute
    $stmt->execute();

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

    if ($result->num_rows === 1) {
        $user = $result->fetch_assoc();

        // Verify password
        if (password_verify($password, $user['password'])) {
            // Success - set session variables
            $_SESSION['user_id'] = $user['id'];
            $_SESSION['username'] = $user['username'];
            $_SESSION['full_name'] = $user['full_name'];

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

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

// Process login
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $username = $_POST['username'];
    $password = $_POST['password'];

    if (login($conn, $username, $password)) {
        header("Location: dashboard.php");
        exit;
    } else {
        $error = "Invalid username or password";
    }
}
?>

Reusable Database Class with Prepared Statements

<?php
class Database {
    private $conn;

    public function __construct() {
        mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
        $this->conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        $this->conn->set_charset("utf8mb4");
    }

    // Execute query with parameters
    public function query($sql, $params = [], $types = "") {
        $stmt = $this->conn->prepare($sql);

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

        $stmt->execute();
        return $stmt;
    }

    // SELECT query - returns all rows
    public function select($sql, $params = [], $types = "") {
        $stmt = $this->query($sql, $params, $types);
        $result = $stmt->get_result();
        $rows = $result->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $rows;
    }

    // SELECT query - returns single row
    public function selectOne($sql, $params = [], $types = "") {
        $stmt = $this->query($sql, $params, $types);
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();
        return $row;
    }

    // INSERT query - returns last insert ID
    public function insert($sql, $params = [], $types = "") {
        $stmt = $this->query($sql, $params, $types);
        $insert_id = $stmt->insert_id;
        $stmt->close();
        return $insert_id;
    }

    // UPDATE/DELETE - returns affected rows
    public function execute($sql, $params = [], $types = "") {
        $stmt = $this->query($sql, $params, $types);
        $affected = $stmt->affected_rows;
        $stmt->close();
        return $affected;
    }

    public function close() {
        $this->conn->close();
    }
}

// Usage
$db = new Database();

// Select all users
$users = $db->select("SELECT * FROM users WHERE city = ?", ["New York"], "s");

// Select one user
$user = $db->selectOne("SELECT * FROM users WHERE id = ?", [42], "i");

// Insert user
$user_id = $db->insert(
    "INSERT INTO users (username, email, password) VALUES (?, ?, ?)",
    ["johndoe", "john@example.com", $hashed_password],
    "sss"
);

// Update user
$affected = $db->execute(
    "UPDATE users SET email = ? WHERE id = ?",
    ["newemail@example.com", 42],
    "si"
);

// Delete user
$affected = $db->execute("DELETE FROM users WHERE id = ?", [42], "i");
?>

Tip: The ...$params syntax unpacks the array to pass parameters individually to bind_param().

Additional Security Best Practices

Validate input: Check data types and formats before using them
Sanitize output: Use htmlspecialchars() when displaying user data
Limit privileges: Database users should have minimal necessary permissions
Use HTTPS: Encrypt data transmission between client and server
Hash passwords: Always use password_hash() and password_verify()
Implement rate limiting: Prevent brute force attacks
Log suspicious activity: Monitor and log failed login attempts

Exercise: Secure User Registration & Login System

Create a registration form that accepts: username, email, password, full_name
Validate all inputs (required fields, email format, password strength)
Use prepared statements to insert the user into the database
Hash the password with password_hash()
Check if username/email already exists before inserting
Create a login form that accepts username and password
Use prepared statements to query the user
Verify the password with password_verify()
Set session variables on successful login
Test with SQL injection attempts to verify security

Testing for SQL Injection

Test your application with these malicious inputs to ensure it's secure:

// Try these in username/email fields:
' OR '1'='1
' OR '1'='1' --
' OR '1'='1' /*
admin'--
' UNION SELECT NULL--
' AND 1=0 UNION ALL SELECT 'admin', '81dc9bdb52d04dc20036dbd8313ed055
1' ORDER BY 1--

Note: If using prepared statements correctly, all these attacks should fail safely without causing errors or unauthorized access.

Summary

SQL injection is a critical security vulnerability that can compromise your entire database
Never concatenate user input directly into SQL queries
Always use prepared statements for queries with user input
Prepared statements separate SQL code from data, preventing injection
Use appropriate type specifiers: i (integer), d (double), s (string), b (blob)
Use get_result() to fetch results as associative arrays
Combine prepared statements with password hashing for secure authentication
Test your application with malicious input to verify security