PHP
Preventing SQL Injection with Parameterized Queries in PHP PDO
Learn to use parameterized queries with PHP PDO to effectively prevent SQL injection vulnerabilities, ensuring secure database interactions in your web applications.
<?php
$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,
];
try {
$pdo = new PDO($dsn, $user, $pass, $options);
// User input (example - typically from $_GET or $_POST)
$userId = 123;
$status = 'active';
// INSECURE (DO NOT DO THIS!): $stmt = $pdo->query("SELECT * FROM users WHERE id = $userId");
// SECURE: Using a prepared statement with named placeholders
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id AND status = :status");
$stmt->execute([':id' => $userId, ':status' => $status]);
$user = $stmt->fetch();
if ($user) {
echo "User found: " . htmlspecialchars($user['username']);
} else {
echo "User not found.";
}
// Another example: INSERT statement
$newUsername = 'testuser';
$newEmail = '[email protected]';
$insertStmt = $pdo->prepare("INSERT INTO users (username, email, status) VALUES (:username, :email, 'active')");
$insertStmt->execute([':username' => $newUsername, ':email' => $newEmail]);
echo "
New user inserted with ID: " . $pdo->lastInsertId();
} catch (PDOException $e) {
throw new PDOException($e->getMessage(), (int)$e->getCode());
}
?>
How it works: This PHP snippet demonstrates how to use parameterized queries with PDO (PHP Data Objects) to prevent SQL injection attacks. Instead of directly embedding user input into the SQL query string, placeholders (`:id`, `:status`) are used. The `prepare()` method sends the query structure to the database separately from the actual values. The `execute()` method then sends the values, which the database treats purely as data, not as executable code. This fundamental separation prevents malicious input from altering the query's intent, thereby securing database interactions. `htmlspecialchars` is also used for outputting user data to prevent XSS.