PHP
Prevent SQL Injection Attacks with Prepared Statements (PHP PDO)
Safeguard your database against malicious SQL injection attacks by consistently using prepared statements and parameterized queries for all database interactions.
<?php
// Database connection details
$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, // Crucial for security with prepared statements
];
try {
$pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
// --- Example 1: SELECT statement ---
function getUserById($pdo, $userId) {
$sql = "SELECT id, username, email FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':id', $userId, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetch();
}
// --- Example 2: INSERT statement ---
function createUser($pdo, $username, $email) {
$sql = "INSERT INTO users (username, email) VALUES (:username, :email)";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->bindParam(':email', $email, PDO::PARAM_STR);
return $stmt->execute();
}
// --- Example 3: UPDATE statement ---
function updateUserEmail($pdo, $userId, $newEmail) {
$sql = "UPDATE users SET email = :email WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':email', $newEmail, PDO::PARAM_STR);
$stmt->bindParam(':id', $userId, PDO::PARAM_INT);
return $stmt->execute();
}
// Example Usage:
// $user = getUserById($pdo, 1);
// if ($user) {
// echo "User: " . $user['username'];
// }
// createUser($pdo, 'johndoe', '[email protected]');
// updateUserEmail($pdo, 1, '[email protected]');
?>
How it works: This PHP snippet demonstrates the crucial practice of preventing SQL injection using PDO (PHP Data Objects) prepared statements. Instead of directly embedding user input into the SQL query string, placeholders (e.g., `:id`, `:username`) are used. The `prepare()` method prepares the query, and then `bindParam()` (or `execute()` with an array) binds the actual user data to these placeholders. This ensures that the database differentiates between the query structure and the data, neutralizing any malicious input and executing only the intended query. `PDO::ATTR_EMULATE_PREPARES => false` is vital for ensuring native prepared statements are used.