PHP
Prevent SQL Injection with Prepared Statements in PHP
Learn how to protect your PHP web applications from SQL injection attacks by using prepared statements with PDO, ensuring safe database interactions.
<?php
// Database connection (replace with your actual credentials)
$dsn = 'mysql:host=localhost;dbname=your_database;charset=utf8mb4';
$username = 'your_username';
$password = 'your_password';
try {
$pdo = new PDO($dsn, $username, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false, // Ensure real prepared statements
]);
} catch (PDOException $e) {
die("Connection failed: " . $e->getMessage());
}
// Example: Retrieving user data safely
if (isset($_GET['user_id'])) {
$userId = $_GET['user_id'];
// Use a prepared statement to prevent SQL injection
$stmt = $pdo->prepare("SELECT id, username, email FROM users WHERE id = :user_id");
$stmt->bindParam(':user_id', $userId, PDO::PARAM_INT);
$stmt->execute();
$user = $stmt->fetch();
if ($user) {
echo "User found: " . htmlspecialchars($user['username']);
} else {
echo "User not found.";
}
} else {
echo "Please provide a user_id.";
}
// Example: Inserting data safely
if (isset($_POST['new_username'], $_POST['new_email'])) {
$newUsername = $_POST['new_username'];
$newEmail = $_POST['new_email'];
$stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (:username, :email)");
$stmt->bindParam(':username', $newUsername, PDO::PARAM_STR);
$stmt->bindParam(':email', $newEmail, PDO::PARAM_STR);
if ($stmt->execute()) {
echo "User added successfully.";
} else {
echo "Failed to add user.";
}
}
?>
How it works: This PHP snippet demonstrates how to prevent SQL injection using PDO prepared statements. Instead of directly embedding user input into the SQL query, placeholders (like `:user_id`) are used. The input is then bound to these placeholders, and the database driver handles the escaping, ensuring that malicious input is treated as data, not executable SQL code. `PDO::ATTR_EMULATE_PREPARES => false` is crucial for ensuring true prepared statements are used.