Creating and Securely Connecting to a MySQL Database with PDO in PHP

When developing dynamic web applications, working with databases is inevitable. In the past, the legacy mysql_* library or mysqli was commonly used in PHP. Today, however, the gold standard for database interaction is PDO (PHP Data Objects). PDO provides a secure, flexible, and database-agnostic environment.

In this article, we will take a detailed look at why PDO is the best choice, how to construct a secure connection file `db.php`, and how to properly handle connection errors when connecting to the server.

1. Why Use PDO Instead of MySQLi?

PDO delivers three major advantages when developing web applications:

• Multiple Database Support: While `mysqli` works exclusively with MySQL databases, PDO supports 12 different database systems (MySQL, PostgreSQL, SQLite, MSSQL...).
• Security (Prepared Statements): PDO natively encourages prepared statements, completely preventing SQL Injection attacks.
• Superior Error Handling: PDO uses Exceptions, allowing developers to catch errors gracefully without exposing sensitive credentials to users.

2. Building a Secure Connection File db.php

The best practice is to isolate database connection code into a dedicated file (e.g., db.php or config.php), which is then included wherever needed using require_once 'db.php';.

Correct and secure code example:

<?php
// Connection configuration
$host = 'localhost';
$db = 'vadnic60_my_db'; // Database name (with server prefix)
$user = 'vadnic60_db_user'; // Database username
$pass = 'YourSecurePassword123!'; // Database user password
$charset = 'utf8mb4'; // Character set supporting all characters and emojis

// DSN (Data Source Name) string
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

// Additional PDO options
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // Throw exceptions on errors
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // Return associative arrays
    PDO::ATTR_EMULATE_PREPARES => false, // Disable emulation for better security
];

try {
    // Create new PDO connection
    $povezava = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
    // Hide password and output safe error message on failure
    die("Database connection failed: " . $e->getMessage());
}
?>

3. Breakdown of Essential PDO Settings

Let's examine what the three key options configured inside the $options array mean:

• ERRMODE_EXCEPTION: If a syntax error occurs in an SQL query, PDO throws a detailed exception containing line numbers and error descriptions, accelerating debugging.
• FETCH_ASSOC: Fetches data as clean associative arrays (e.g., $row['title'] instead of numeric indices $row[0]).
• utf8mb4: Sets character encoding to support all international characters and symbols without corruption.

4. Example of Secure Inserts and Reads Using Prepared Statements

Once the $povezava connection is established, we can execute SQL queries safely. User input should never be concatenated directly into SQL strings; use placeholders (?) or named parameters instead.

Secure Insert (INSERT):

$sql = "INSERT INTO objave (naslov_en, vsebina_en) VALUES (?, ?)";
$stmt = $povezava->prepare($sql);
$stmt->execute([$title, $content]);

Secure Read (SELECT):

$sql = "SELECT * FROM objave WHERE kategorija = ?";
$stmt = $povezava->prepare($sql);
$stmt->execute(['WEB']);
$all_posts = $stmt->fetchAll();

Summary

By mastering the PDO library, you have taken a major step toward writing secure, modern, and professional PHP applications!