PHP
Secure Database Queries with PHP PDO Prepared Statements
Prevent SQL injection vulnerabilities in your PHP applications by utilizing PDO's prepared statements and parameterized queries for safe database interactions.
<?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);
} catch (\PDOException $e) {
throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
// Example of a SELECT statement
$userId = 123;
$stmt = $pdo->prepare('SELECT name, email FROM users WHERE id = :id');
$stmt->execute(['id' => $userId]);
$user = $stmt->fetch();
if ($user) {
echo "User Name: " . htmlspecialchars($user['name']) . "
";
echo "User Email: " . htmlspecialchars($user['email']) . "
";
} else {
echo "User not found.
";
}
// Example of an INSERT statement
$userName = 'John Doe';
$userEmail = '[email protected]';
$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$stmt->execute(['name' => $userName, 'email' => $userEmail]);
echo "New user inserted with ID: " . $pdo->lastInsertId() . "
";
?>
How it works: This PHP snippet demonstrates how to use PDO (PHP Data Objects) with prepared statements to query and modify a database securely. Prepared statements separate the SQL query logic from user-supplied data, preventing SQL injection by ensuring that parameters are treated as literal values rather than executable SQL code. The `execute()` method binds the provided values to their respective placeholders, making database interactions safe and robust.