PHP
Prevent SQL Injection with Parameterized Queries in PHP PDO
Safeguard databases from SQL injection attacks by using prepared statements and parameterized queries, ensuring user input is treated as data, not executable code.
<?php
// Database connection details
$host = 'localhost';
$db = 'your_database';
$user = 'your_user';
$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, // Important for security and performance
];
try {
$pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
// --- Example 1: Selecting data with user input ---
$userId = $_GET['user_id'] ?? 1; // Unsafe if directly inserted!
// Vulnerable approach (DO NOT USE!)
// $stmt = $pdo->query("SELECT * FROM users WHERE id = " . $userId);
// Secure approach: Parameterized Query
$stmt = $pdo->prepare("SELECT id, username, email FROM users WHERE id = :user_id");
$stmt->bindParam(':user_id', $userId, PDO::PARAM_INT); // Bind parameter explicitly
$stmt->execute();
$user = $stmt->fetch();
if ($user) {
// echo "User found: " . htmlspecialchars($user['username']);
} else {
// echo "User not found.";
}
// --- Example 2: Inserting data with user input ---
$newUsername = $_POST['username'] ?? 'testuser';
$newEmail = $_POST['email'] ?? '[email protected]';
$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);
// $stmt->execute();
// echo "User inserted successfully with ID: " . $pdo->lastInsertId();
// Always use parameterized queries for ALL user or external input in SQL.
?>
How it works: This snippet demonstrates the critical security practice of using parameterized SQL queries with PHP's PDO extension to prevent SQL injection attacks. Instead of directly embedding user input into SQL strings, placeholders (e.g., `:user_id`) are used. The `prepare()` method prepares the query, separating the SQL logic from the data. The `bindParam()` or `execute()` with an array then safely binds the user-supplied values to these placeholders, ensuring the database treats them strictly as data, not as executable SQL code. This completely neutralizes most SQL injection vectors.