PHP
Prevent SQL Injection with Prepared Statements (PHP PDO)
Learn to effectively prevent SQL injection vulnerabilities in your PHP applications by utilizing prepared statements with PDO for secure database interactions.
<?php
// Database connection configuration
$host = 'localhost';
$db = 'your_database';
$user = 'your_username';
$pass = 'your_password';
$charset = 'utf8mb4';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
try {
$pdo = new PDO($dsn, $user, $pass, $options);
// UNSAFE (VULNERABLE TO SQL INJECTION) - DO NOT DO THIS!
// $unsafe_id = $_GET['id'] ?? '';
// $stmt = $pdo->query("SELECT * FROM users WHERE id = $unsafe_id");
// $user = $stmt->fetch();
// SAFE (USING PREPARED STATEMENTS)
$safe_id = $_GET['id'] ?? 1; // Example user input
$email = $_POST['email'] ?? '[email protected]'; // Example user input
// 1. SELECT statement with named placeholders
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute(['id' => $safe_id]);
$user = $stmt->fetch();
echo "User found by ID: " . print_r($user, true) . "
";
// 2. INSERT statement with positional placeholders
$username = 'newuser';
$password_hash = password_hash('securepass123', PASSWORD_BCRYPT);
$insert_stmt = $pdo->prepare("INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)");
$insert_stmt->execute([$username, $email, $password_hash]);
echo "Inserted new user with ID: " . $pdo->lastInsertId() . "
";
} catch (PDOException $e) {
throw new PDOException($e->getMessage(), (int)$e->getCode());
}
// Example of how to structure an actual user creation process (simplified)
function createUser($pdo, $username, $email, $rawPassword) {
$password_hash = password_hash($rawPassword, PASSWORD_BCRYPT);
$stmt = $pdo->prepare("INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)");
return $stmt->execute([$username, $email, $password_hash]);
}
// Example usage:
// if (createUser($pdo, 'testuser', '[email protected]', 'mypassword')) {
// echo "Test user created successfully.
";
// }
?>
How it works: This PHP snippet demonstrates how to prevent SQL injection attacks using Prepared Statements with PDO (PHP Data Objects). Instead of directly embedding user input into the SQL query string, prepared statements separate the query logic from the data. The SQL query is first prepared and compiled by the database, then the user-provided parameters are bound to placeholders. This ensures that the input data is treated purely as data, not as executable SQL code, effectively neutralizing malicious input that attempts to alter the query's intent.