PHP
Prevent SQL Injection with PHP Prepared Statements
Learn to prevent SQL injection attacks in PHP by using prepared statements and parameterized queries, ensuring secure database interactions.
<?php
$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);
// --- Example: SELECT query with prepared statement ---
$userId = 123;
$stmt = $pdo->prepare("SELECT username, email FROM users WHERE id = :id");
$stmt->bindParam(':id', $userId, PDO::PARAM_INT);
$stmt->execute();
$user = $stmt->fetch();
if ($user) {
echo "User: " . htmlspecialchars($user['username']) . ", Email: " . htmlspecialchars($user['email']) . "
";
} else {
echo "User not found.
";
}
// --- Example: INSERT query with prepared statement ---
$newUsername = 'johndoe';
$newEmail = '[email protected]';
$newPasswordHash = password_hash('securepassword123', PASSWORD_BCRYPT); // Secure password hashing
$stmt = $pdo->prepare("INSERT INTO users (username, email, password) VALUES (:username, :email, :password)");
$stmt->bindParam(':username', $newUsername, PDO::PARAM_STR);
$stmt->bindParam(':email', $newEmail, PDO::PARAM_STR);
$stmt->bindParam(':password', $newPasswordHash, PDO::PARAM_STR);
$stmt->execute();
echo "New user '{$newUsername}' added successfully with ID: " . $pdo->lastInsertId() . "
";
} catch (\PDOException $e) {
// Log the error for debugging, do not expose details to the user
error_log("Database Error: " . $e->getMessage());
die("An error occurred. Please try again later.");
}
?>
How it works: SQL injection is a critical vulnerability. This PHP snippet demonstrates how to use PDO (PHP Data Objects) with prepared statements to prevent SQL injection. Instead of concatenating user input directly into SQL queries, prepared statements send the SQL query structure and the data separately. The database then understands the query's intent before receiving the potentially malicious data, ensuring that user input is always treated as data, not executable code. `bindParam()` ensures proper type handling.