How to Connect PHP with MySQL?

PDO (PHP Data Objects) is recommended for new projects due to its database abstraction layer, which allows switching between different database systems with minimal code changes. PDO also has superior security features and follows modern PHP practices.

While MySQLi is also secure and functional, PDO offers better portability, consistency, and is considered more modern. PDO supports named parameters and has a more intuitive interface.

The answer is C) PDO.

Prepared Statement Definition: A feature that separates SQL logic from data by pre-compiling the query structure and then binding parameters separately.

SQL Injection Prevention:

Without Prepared Statements:

Unsafe query: SELECT * FROM users WHERE email = '$userInput'

If $userInput = "admin'; DROP TABLE users; --", the query becomes:

SELECT * FROM users WHERE email = 'admin'; DROP TABLE users; --'

With Prepared Statements:

Safe query: SELECT * FROM users WHERE email = ?

The parameter is bound separately and treated as data, not executable code.

Code Example:

// Secure with PDO
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ? AND password = ?");
$stmt->execute([$email, $password]);
$user = $stmt->fetch();

// Secure with named parameters
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email AND password = :password");
$stmt->execute(['email' => $email, 'password' => $password]);
$user = $stmt->fetch();

Prepared statements prevent SQL injection by ensuring parameters are treated as literal values rather than executable code.

Secure Registration Script:

<?php
// Database configuration
$host = 'localhost';
$dbname = 'user_system';
$username = 'db_user';
$password = 'secure_password';

try {
    // Create PDO connection with security settings
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_PERSISTENT => true
    ]);
    
    // Sanitize and validate inputs
    $email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL);
    $username = trim($_POST['username']);
    $password = $_POST['password'];
    
    if (!$email || strlen($username) < 3 || strlen($password) < 8) {
        throw new Exception("Invalid input data");
    }
    
    // Hash password for security
    $hashedPassword = password_hash($password, PASSWORD_DEFAULT);
    
    // Use prepared statement to prevent SQL injection
    $stmt = $pdo->prepare("INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)");
    $stmt->execute([$username, $email, $hashedPassword]);
    
    echo "Registration successful!";
    
} catch(PDOException $e) {
    error_log("Database error: " . $e->getMessage());
    echo "Registration failed. Please try again.";
} catch(Exception $e) {
    error_log("Application error: " . $e->getMessage());
    echo "Invalid input data.";
}
?>

Security Measures Implemented:

1. Prepared statements prevent SQL injection

2. Password hashing prevents plaintext storage

3. Input validation ensures data integrity

4. Error logging without exposing details

5. Character set specification prevents encoding issues

Approach 1: Die with Message

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

Advantages: Simple, immediate termination

Disadvantages: Exposes error details, no graceful degradation

Approach 2: Log and Redirect

try {
    $pdo = new PDO($dsn, $username, $password);
} catch(PDOException $e) {
    error_log("DB Error: " . $e->getMessage());
    header("Location: /error-page.php");
    exit();
}

Advantages: Secure, user-friendly

Disadvantages: May lose context, harder to debug

Approach 3: Graceful Degradation

try {
    $pdo = new PDO($dsn, $username, $password);
    $connected = true;
} catch(PDOException $e) {
    error_log("DB Error: " . $e->getMessage());
    $connected = false;
    $errorPage = true;
}

Advantages: Maintains functionality, secure

Disadvantages: More complex implementation

Production Recommendation: Use approach 3 with proper error logging and user notifications.

The most secure approaches are using environment variables (B) or storing credentials in a config file outside the web root (C). Both prevent direct access via web requests.

Hardcoding credentials in PHP files is insecure because the source code could be exposed through server misconfigurations. Environment variables are preferred in modern deployments (especially with containerization) while config files outside the web root provide security through file system permissions.

Environment variables are more flexible for different deployment environments and are the preferred method in cloud and containerized environments.

The answer is D) Both B and C are equally secure.