PHP SQL Injection Prevention - Secure Your Database

PHP SQL Injection Prevention – Secure Your Database 🛡️

SQL Injection is one of the most common and dangerous security vulnerabilities in PHP applications. It allows attackers to manipulate your database by injecting malicious SQL queries. 😨

But don’t worry! You can easily prevent SQL injection using prepared statements with MySQLi or PDO. Let’s see how! 🚀

🚨 What is SQL Injection?

Imagine a login form where a user enters their username and password. If you directly insert these values into an SQL query, an attacker could enter something like:

Username: admin' --
Password: (anything)

This could turn your query into:

SELECT * FROM users WHERE username = 'admin' --' AND password = '...'

The -- turns the rest of the query into a comment, allowing the attacker to bypass authentication! 😱

🛑 BAD EXAMPLE (Vulnerable to SQL Injection) ❌

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "secure_db";

$conn = new mysqli($servername, $username, $password, $database);

// Get user input
$user = $_POST['username'];
$pass = $_POST['password'];

// 🚨 UNSAFE QUERY - DO NOT USE!
$sql = "SELECT * FROM users WHERE username = '$user' AND password = '$pass'";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "Login successful! ✅";
} else {
    echo "Invalid login ❌";
}

$conn->close();
?>

⚠️ This code is vulnerable! An attacker can easily bypass authentication. Let’s fix it with prepared statements.

✅ SAFE EXAMPLE: Using MySQLi Prepared Statements

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "secure_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

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

// Secure prepared statement
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param("ss", $user, $pass);

// Get user input safely
$user = $_POST['username'];
$pass = $_POST['password'];

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

if ($result->num_rows > 0) {
    echo "Login successful! ✅";
} else {
    echo "Invalid login ❌";
}

// Close connections
$stmt->close();
$conn->close();
?>

Try It Now

🔒 Why is this secure? The user input is bound separately, preventing SQL injection.

✅ SAFE EXAMPLE: Using PDO Prepared Statements

<?php
$dsn = "mysql:host=localhost;dbname=secure_db";
$username = "root";
$password = "";

try {
    // Create PDO instance
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Secure prepared statement
    $stmt = $pdo->prepare("SELECT * FROM users WHERE username = :user AND password = :pass");
    $stmt->bindParam(":user", $user);
    $stmt->bindParam(":pass", $pass);

    // Get user input safely
    $user = $_POST['username'];
    $pass = $_POST['password'];

    $stmt->execute();

    if ($stmt->rowCount() > 0) {
        echo "Login successful! ✅";
    } else {
        echo "Invalid login ❌";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Close connection
$pdo = null;
?>

Try It Now

🔒 Why is this secure? PDO supports named placeholders, making it even easier to use prepared statements.

🎯 Key Takeaways

🚨 Never insert user input directly into SQL queries.
🔒 Always use prepared statements with MySQLi or PDO.
✅ PDO is more flexible (supports multiple databases).
🛡️ MySQLi is optimized for MySQL and slightly faster.

🚀 Next Steps

Try modifying the examples above and experiment with prepared statements. Avoid SQL injection like a pro! 💪