PHP
Prevent SQL Injection using PDO Prepared Statements
Protect your database from SQL injection attacks by implementing PDO prepared statements for all database queries involving user input.
<?php
$dsn = 'mysql:host=localhost;dbname=mydb;charset=utf8mb4';
$user = 'dbuser';
$password = 'dbpassword';
try {
$pdo = new PDO($dsn, $user, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false, // Ensure real prepared statements are used
]);
} catch (PDOException $e) {
exit('Database connection failed: ' . $e->getMessage());
}
// Example 1: SELECT statement with user input
$userId = $_GET['id'] ?? 1; // Unsafe input example
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->bindParam(':id', $userId, PDO::PARAM_INT);
$stmt->execute();
$user = $stmt->fetch();
if ($user) {
echo "User found: " . htmlspecialchars($user['name']) . "
";
} else {
echo "User not found.
";
}
// Example 2: INSERT statement with user input
$username = $_POST['username'] ?? 'guest';
$email = $_POST['email'] ?? '[email protected]';
$stmt = $pdo->prepare('INSERT INTO users (username, email) VALUES (:username, :email)');
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->bindParam(':email', $email, PDO::PARAM_STR);
$stmt->execute();
echo "New user created with ID: " . $pdo->lastInsertId() . "
";
?>
How it works: This PHP snippet demonstrates how to prevent SQL injection vulnerabilities using PDO prepared statements. Instead of directly embedding user input into the SQL query string, prepared statements separate the SQL logic from the data. Parameters like `:id`, `:username`, and `:email` are placeholders that are securely bound to actual values using `bindParam()`. This ensures that even if a malicious string is provided (e.g., `'1 OR 1=1'`), it will be treated as data and not as executable SQL code, thus protecting your database.