Database Security & PDO

🐘 PHP Lesson 13 Advanced

PHP Data Objects (PDO) is a database access layer that provides a secure, consistent interface to communicate with databases in PHP.

1 Prepared Statements and SQL Injection protection

Interpolating variables directly into raw SQL strings makes your database vulnerable to SQL Injection attacks. PDO prevents this using **Prepared Statements**. The database compiles the SQL query structure first, and then binds parameters separately as values, rendering SQL injection payloads completely harmless.

2 PDO Code

Let's look at a connection template illustrating prepared statements:

PHP — Database Access via PDO ▶ Run Code

<?php
// PDO Connection Template
try {
    $dsn = "mysql:host=localhost;dbname=testdb;charset=utf8";
    $pdo = new PDO($dsn, "username", "password", [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);

    // Secure Prepared Statement query execution
    $stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
    $stmt->execute(['email' => 'balaji@example.com']);
    $user = $stmt->fetch();

    echo "Query completed safely!\n";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage() . "\n";
}
?>

3 Code Challenge

Challenge: Write a PDO code snippet that prepares an INSERT statement to add a new product to a database. Specify placeholders (`:name` and `:price`) and bind their values.