What are the best practices for handling database connections and queries in PHP scripts, especially when dealing with sensitive information like passwords?

When handling database connections and queries in PHP scripts, especially when dealing with sensitive information like passwords, it is important to follow best practices to ensure security. One way to do this is by storing sensitive information in a separate configuration file outside of the web root, using secure methods to connect to the database, and sanitizing user input to prevent SQL injection attacks. 

<?php
// Include configuration file with sensitive information
require_once 'config.php';

// Connect to the database using PDO with prepared statements
try {
    $pdo = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Error connecting to the database: " . $e->getMessage());
}

// Sanitize user input before using it in a query
$username = filter_var($_POST['username'], FILTER_SANITIZE_STRING);
$password = filter_var($_POST['password'], FILTER_SANITIZE_STRING);

// Prepare and execute a query using prepared statements
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND password = :password");
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
$stmt->execute();

// Fetch results
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Process results
foreach ($results as $row) {
    // Do something with the data
}

// Close the database connection
$pdo = null;
?>

Keywords

PDO mysqli prepared statements encryption secure coding practices

Related Questions