How PHP and MySQL Work Together

// Connecting via Unix socket in PDO
$dsn = "mysql:unix_socket=/var/run/mysqld/mysqld.sock;dbname=tutorials_db;charset=utf8mb4";

// Connecting via TCP/IP in PDO
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=tutorials_db;charset=utf8mb4";

namespace App\Core;

use PDO;
use PDOException;
use RuntimeException;

class DatabaseConnection {
    private static ?DatabaseConnection $instance = null;
    private ?PDO $connection = null;

    // Private constructor prevents external instantiation
    private function __construct() {
        // In production, these should be loaded from secure environment variables
        $host = '127.0.0.1';
        $port = 3306;
        $dbname = 'tutorials_db';
        $username = 'root';
        $password = '';
        $charset = 'utf8mb4';

        $dsn = "mysql:host={$host};port={$port};dbname={$dbname};charset={$charset}";
        
        $options = [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false, // Force native prepared statements
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
        ];

        try {
            $this->connection = new PDO($dsn, $username, $password, $options);
        } catch (PDOException $e) {
            // Log the error and hide raw credentials from stack trace
            error_log("Database connection failure: " . $e->getMessage());
            throw new RuntimeException("Could not establish database connection.");
        }
    }

    // Retrieve the singleton instance
    public static function getInstance(): DatabaseConnection {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    // Retrieve active PDO instance
    public function getConnection(): PDO {
        return $this->connection;
    }

    // Prevent cloning of singleton
    private function __clone() {}

    // Prevent unserializing of singleton
    public function __wakeup() {
        throw new RuntimeException("Cannot unserialize a singleton.");
    }
}

namespace App\Repositories;

use PDO;
use App\Core\DatabaseConnection;

class UserRepository {
    private PDO $db;

    public function __construct() {
        $this->db = DatabaseConnection::getInstance()->getConnection();
    }

    // 1. CREATE: Insert a new user record
    public function create(array $userData): int {
        $sql = "INSERT INTO users (name, email, password_hash, role) 
                VALUES (:name, :email, :password_hash, :role)";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([
            ':name'          => $userData['name'],
            ':email'         => $userData['email'],
            ':password_hash' => password_hash($userData['password'], PASSWORD_BCRYPT),
            ':role'          => $userData['role'] ?? 'user'
        ]);

        return (int) $this->db->lastInsertId();
    }

    // 2. READ: Fetch user by email
    public function findByEmail(string $email): ?array {
        $sql = "SELECT id, name, email, password_hash, role, created_at 
                FROM users 
                WHERE email = :email";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':email' => $email]);
        $user = $stmt->fetch();

        return $user ?: null;
    }

    // 3. UPDATE: Update user profile
    public function update(int $id, array $updates): bool {
        $sql = "UPDATE users 
                SET name = :name, role = :role 
                WHERE id = :id";
        
        $stmt = $this->db->prepare($sql);
        return $stmt->execute([
            ':name' => $updates['name'],
            ':role' => $updates['role'],
            ':id'   => $id
        ]);
    }

    // 4. DELETE: Remove user
    public function delete(int $id): bool {
        $sql = "DELETE FROM users WHERE id = :id";
        $stmt = $this->db->prepare($sql);
        return $stmt->execute([':id' => $id]);
    }
}

namespace App\Services;

use PDO;
use Exception;
use App\Core\DatabaseConnection;

class OrderManager {
    private PDO $db;

    public function __construct() {
        $this->db = DatabaseConnection::getInstance()->getConnection();
    }

    public function processOrder(int $userId, array $items, float $totalPrice): bool {
        try {
            // Begin the transaction
            $this->db->beginTransaction();

            // 1. Check user balance
            $stmt = $this->db->prepare("SELECT balance FROM users WHERE id = :id FOR UPDATE");
            $stmt->execute([':id' => $userId]);
            $balance = $stmt->fetchColumn();

            if ($balance < $totalPrice) {
                throw new Exception("Insufficient account balance.");
            }

            // 2. Deduct user balance
            $stmt = $this->db->prepare("UPDATE users SET balance = balance - :amount WHERE id = :id");
            $stmt->execute([&#039;:amount' => $totalPrice, ':id' => $userId]);

            // 3. Create the order
            $stmt = $this->db->prepare("INSERT INTO orders (user_id, total_price, status) VALUES (:user_id, :total, &#039;Paid')");
            $stmt->execute([&#039;:user_id' => $userId, ':total' => $totalPrice]);
            $orderId = $this->db->lastInsertId();

            // 4. Insert order items
            $stmt = $this->db->prepare("INSERT INTO order_items (order_id, product_id, quantity) VALUES (:order_id, :prod_id, :qty)");
            foreach ($items as $item) {
                $stmt->execute([
                    &#039;:order_id' => $orderId,
                    &#039;:prod_id'  => $item['product_id'],
                    &#039;:qty'      => $item['quantity']
                ]);
            }

            // Commit all changes
            $this->db->commit();
            return true;

        } catch (Exception $e) {
            // Something failed. Rollback database to original state
            if ($this->db->inTransaction()) {
                $this->db->rollBack();
            }
            error_log("Order failed: " . $e->getMessage());
            return false;
        }
    }
}

$username = $_POST['username'];
$password = $_POST['password'];

// If attacker passes username: admin' OR '1'='1
// The query compiles to: SELECT * FROM users WHERE username = 'admin' OR '1'='1' ...
$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = $db->query($sql);

$username = $_POST['username'];
$password = $_POST['password'];

$sql = "SELECT * FROM users WHERE username = :username";
$stmt = $db->prepare($sql);
$stmt->execute([':username' => $username]);
$user = $stmt->fetch();

if ($user && password_verify($password, $user['password_hash'])) {
    // Access granted
}

namespace App\Controllers;

use App\Repositories\UserRepository;

class AuthController {
    private UserRepository $userRepo;

    public function __construct() {
        $this->userRepo = new UserRepository();
    }

    public function login(): void {
        session_start();

        // 1. Verify CSRF Token
        if (!isset($_POST['csrf_token']) || $_POST['csrf_token'] !== ($_SESSION['csrf_token'] ?? '')) {
            http_response_code(403);
            die("Invalid CSRF token.");
        }

        $email = filter_var($_POST['email'] ?? '', FILTER_VALIDATE_EMAIL);
        $password = $_POST['password'] ?? '';

        if (!$email || empty($password)) {
            $_SESSION['error'] = "Invalid login credentials.";
            header("Location: /login");
            exit;
        }

        // 2. Fetch User from DB
        $user = $this->userRepo->findByEmail($email);

        // 3. Verify Password Hash
        if ($user && password_verify($password, $user['password_hash'])) {
            // Regenerate session ID to prevent Session Fixation
            session_regenerate_id(true);

            $_SESSION['user_id'] = $user['id'];
            $_SESSION['username'] = $user['name'];
            $_SESSION['role'] = $user['role'];
            $_SESSION['logged_in'] = true;

            header("Location: /dashboard");
            exit;
        }

        $_SESSION['error'] = "Invalid email or password.";
        header("Location: /login");
        exit;
    }
}

// api/users.php
header("Access-Control-Allow-Origin: https://trusted-frontend.com");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: GET");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");

define('ROOT_PATH', dirname(__DIR__));
require_once ROOT_PATH . '/app/Core/DatabaseConnection.php';

try {
    $db = \App\Core\DatabaseConnection::getInstance()->getConnection();
    
    $limit = isset($_GET['limit']) ? (int) $_GET['limit'] : 10;
    $offset = isset($_GET['offset']) ? (int) $_GET['offset'] : 0;
    
    // Enforce limits to prevent Denial of Service (DoS)
    if ($limit > 100) $limit = 100;
    if ($limit < 1) $limit = 10;

    $sql = "SELECT id, name, email, role, created_at 
            FROM users 
            ORDER BY created_at DESC 
            LIMIT :limit OFFSET :offset";
            
    $stmt = $db->prepare($sql);
    $stmt->bindValue(&#039;:limit', $limit, PDO::PARAM_INT);
    $stmt->bindValue(&#039;:offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    
    $users = $stmt->fetchAll();

    http_response_code(200);
    echo json_encode([
        "success" => true,
        "count"   => count($users),
        "data"    => $users
    ]);

} catch (Exception $e) {
    http_response_code(500);
    echo json_encode([
        "success" => false,
        "message" => "Internal Server Error"
    ]);
}