Safe queries with PDO

What is PDO and why use it?

PDO (PHP Data Objects) is a PHP extension that helps you work with databases safely and efficiently. One of the biggest benefits is protection against a serious security risk called SQL injection.

Why SQL injection matters

When you build SQL queries by inserting user input directly, like this:

<?php
$username = $_POST['username'] ?? '';
$password = $_POST['password'] ?? '';

// VULNERABLE to SQL injection
$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = $pdo->query($sql)->fetch();

A malicious user could enter something like admin' OR 1=1 # as the username on your login form, which tricks the query into always returning a result — even if the user doesn't exist. This is called a login bypass attack.

Why it works: The # symbol starts a comment in MySQL, so everything after it is ignored. That means the actual SQL query becomes something like:

SELECT * FROM users WHERE username = 'admin' OR 1=1 # ' AND password = 'anything'

This matches all users because 1=1 is always true — and the password check is skipped entirely.

How PDO prevents this with prepared statements

PDO supports prepared statements with placeholders to handle data safely. Prepared statements with placeholders keep your queries safe by separating SQL code from user input. This protects against SQL injection.

Using placeholders and prepared statements

Here's how you safely insert a new user using placeholders:

• - Prepare the query with placeholders :username and :email.
• - Execute the statement passing the actual data as an array.
• - PDO handles escaping and protects against injection automatically.

<?php
$pdo = include 'database.php';

$username = 'newuser';
$email = 'newuser@example.com';

$stmt = $pdo->prepare('INSERT INTO users (username, email) VALUES (:username, :email)');

$stmt->execute([
    ':username' => $username,
    ':email' => $email,
]);

Try the examples — create a test table (optional)

If you want to try the code examples in this tutorial, you can create a simple users table in your database. This step is optional if you already have a table to work with.

To create the users table, log into your hosting control panel (cPanel, Plesk, etc.), open phpMyAdmin, select your database, go to the SQL tab, and paste this command:

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

This creates a basic table with an ID, username, email, password and a timestamp for when each user was added.

Setting up your PDO connection

Make sure you have a database.php file with your PDO connection setup. For example:

<?php
$dbConfig = [
    'host'     => 'localhost',
    'name'     => 'yourdatabasename',
    'user'     => 'yourdatabaseuser',
    'password' => 'yourpassword',
    'charset'  => 'utf8mb4'
];

// Validates config
if (empty($dbConfig['host']) || empty($dbConfig['name']) || empty($dbConfig['user'])) {
    exit('Database host, name, and user cannot be empty.');
}

try {
    $pdo = new PDO(
        "mysql:host={$dbConfig['host']};dbname={$dbConfig['name']};charset={$dbConfig['charset']}",
        $dbConfig['user'],
        $dbConfig['password'],
        [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES   => false,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]
    );

    // Test the connection
    $pdo->query('SELECT 1');

} catch (PDOException $e) {
    die('Database connection failed.');
}

return $pdo;

Best practices

• - Always use prepared statements with placeholders for user input.
• - Use htmlspecialchars() when outputting data to prevent HTML injection.
• - Keep your database connection code separate and reusable.

Next: Useful PDO snippets for practical examples how to safely handle data with PDO.