What best practices should be followed when handling database connections and queries in PHP scripts to prevent errors and improve security?

When handling database connections and queries in PHP scripts, it is important to use prepared statements to prevent SQL injection attacks and sanitize user input to avoid potential security vulnerabilities. Additionally, closing the database connection after use helps improve performance and prevent resource leaks. 

// Establishing a database connection using PDO
$dsn = 'mysql:host=localhost;dbname=mydatabase';
$username = 'username';
$password = 'password';

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

// Using prepared statements to prevent SQL injection
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
$stmt->bindParam(':username', $username);
$stmt->execute();

// Sanitizing user input
$username = filter_var($_POST['username'], FILTER_SANITIZE_STRING);

// Closing the database connection
$pdo = null;

Keywords

SQL injection PDO prepared statements error handling secure coding practices

Related Questions