How to Securely Connect MySQL and PHP using PDO

When working with PHP and databases, security is paramount. Older connection methods (like `mysql_connect`) are obsolete and vulnerable. Today, we use PDO (PHP Data Objects), which provides a secure, object-oriented way to interact with your data.

Key Advantages of the PDO Extension

Prepared Statements: These prevent SQL injection by separating the SQL command from the user data.
Portability: If you decide to switch from MySQL to PostgreSQL, most of your code remains unchanged.
Advanced Error Handling: PDO uses "Exceptions," making it much easier to debug connection issues.

Connection Method Comparison

Feature	MySQLi	PDO
Security	Good	Excellent (standard)
Prepared Statements	Supported	Supported (easier to use)
Multi-DB Support	MySQL only	12 different databases

Secure Connection Code (db.php)

Save this code to a db.php file and include it in your projects:


$host = 'localhost';
$db = 'your_database';
$user = 'username';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
     $pdo = new PDO($dsn, $user, $pass, $options);
     // Connection established!
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}