萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP + MySQL 實作使用者 CRUD 完整教學:MVC 架構、PDO 預處理、安全防護

前言

使用者管理(建立、讀取、更新、刪除)是 Web 應用的基礎功能。本文從零開始,使用原生 PHP + PDO + MySQL,採用簡易 MVC 架構,實作完整的使用者 CRUD 系統,並強調 SQL 注入防護、XSS 防禦、密碼雜湊、CSRF 保護等安全實務。

資料庫設計

CREATE DATABASE `user_crud` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `user_crud`;

CREATE TABLE `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `email` VARCHAR(191) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `full_name` VARCHAR(100) DEFAULT NULL,
    `avatar` VARCHAR(255) DEFAULT NULL,
    `role` ENUM('user','admin') NOT NULL DEFAULT 'user',
    `status` ENUM('active','inactive','banned') NOT NULL DEFAULT 'active',
    `email_verified_at` TIMESTAMP NULL DEFAULT NULL,
    `last_login_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_email` (`email`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

專案結構

user-crud/
├── config/
│   └── database.php          # 資料庫設定
├── src/
│   ├── Model/
│   │   └── User.php          # 使用者模型
│   ├── Repository/
│   │   └── UserRepository.php # 資料存取層
│   ├── Service/
│   │   └── UserService.php   # 業務邏輯
│   ├── Controller/
│   │   ├── UserController.php
│   │   └── AuthController.php
│   └── View/
│       ├── layout.php
│       ├── users/
│       │   ├── index.php
│       │   ├── create.php
│       │   ├── edit.php
│       │   └── show.php
│       └── partials/
│           ├── flash.php
│           └── form_errors.php
├── public/
│   ├── index.php             # 進入點
│   ├── css/
│   └── js/
├── bootstrap.php             # 自動載入、啟動 Session、錯誤處理
├── composer.json
└── .htaccess

核心程式碼精選

1. 資料庫連線 (PDO)

<?php // config/database.php
return [
    'driver' => 'mysql',
    'host' => $_ENV['DB_HOST'] ?? '127.0.0.1',
    'port' => $_ENV['DB_PORT'] ?? 3306,
    'database' => $_ENV['DB_DATABASE'] ?? 'user_crud',
    'username' => $_ENV['DB_USERNAME'] ?? 'root',
    'password' => $_ENV['DB_PASSWORD'] ?? '',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'options' => [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false, // 真實預處理
        PDO::MYSQL_ATTR_SSL_CA => $_ENV['DB_SSL_CA'] ?? null,
    ],
];

2. UserRepository(資料存取層)

<?php // src/Repository/UserRepository.php
class UserRepository {
    private PDO $pdo;

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }

    public function findAll(int $limit = 50, int $offset = 0): array {
        $stmt = $this->pdo->prepare('SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?');
        $stmt->bindValue(1, $limit, PDO::PARAM_INT);
        $stmt->bindValue(2, $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function findById(int $id): ?array {
        $stmt = $this->pdo->prepare('SELECT * FROM users WHERE id = ?');
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }

    public function findByEmail(string $email): ?array {
        $stmt = $this->pdo->prepare('SELECT * FROM users WHERE email = ?');
        $stmt->execute([$email]);
        return $stmt->fetch() ?: null;
    }

    public function create(array $data): int {
        $sql = 'INSERT INTO users (username, email, password_hash, full_name, role, status) VALUES (?, ?, ?, ?, ?, ?)';
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            $data['username'],
            $data['email'],
            $data['password_hash'],
            $data['full_name'] ?? null,
            $data['role'] ?? 'user',
            $data['status'] ?? 'active',
        ]);
        return (int)$this->pdo->lastInsertId();
    }

    public function update(int $id, array $data): bool {
        $fields = [];
        $values = [];
        foreach (['username', 'email', 'full_name', 'role', 'status', 'avatar'] as $field) {
            if (array_key_exists($field, $data)) {
                $fields[] = "$field = ?";
                $values[] = $data[$field];
            }
        }
        if (!$fields) return false;
        $values[] = $id;
        $sql = 'UPDATE users SET ' . implode(', ', $fields) . ' WHERE id = ?';
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($values);
    }

    public function updatePassword(int $id, string $passwordHash): bool {
        $stmt = $this->pdo->prepare('UPDATE users SET password_hash = ? WHERE id = ?');
        return $stmt->execute([$passwordHash, $id]);
    }

    public function delete(int $id): bool {
        $stmt = $this->pdo->prepare('DELETE FROM users WHERE id = ?');
        return $stmt->execute([$id]);
    }

    public function count(): int {
        return (int)$this->pdo->query('SELECT COUNT(*) FROM users')->fetchColumn();
    }
}

3. UserService(業務邏輯與驗證)

<?php // src/Service/UserService.php
class UserService {
    private UserRepository $repo;

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

    public function register(array $input): array {
        $errors = $this->validate($input, true);
        if ($errors) return ['success' => false, 'errors' => $errors];

        $data = [
            'username' => trim($input['username']),
            'email' => strtolower(trim($input['email'])),
            'password_hash' => password_hash($input['password'], PASSWORD_ARGON2ID),
            'full_name' => trim($input['full_name'] ?? ''),
        ];
        $id = $this->repo->create($data);
        return ['success' => true, 'id' => $id];
    }

    public function updateProfile(int $id, array $input, bool $changePassword = false): array {
        $errors = $this->validate($input, false, $changePassword);
        if ($errors) return ['success' => false, 'errors' => $errors];

        $data = [];
        if (!empty($input['full_name'])) $data['full_name'] = trim($input['full_name']);
        if (!empty($input['email'])) {
            $email = strtolower(trim($input['email']));
            if ($this->repo->findByEmail($email) && $this->repo->findByEmail($email)['id'] != $id) {
                $errors['email'] = 'Email 已被使用';
            } else {
                $data['email'] = $email;
            }
        }
        if ($changePassword && !empty($input['password'])) {
            $this->repo->updatePassword($id, password_hash($input['password'], PASSWORD_ARGON2ID));
        }
        if ($errors) return ['success' => false, 'errors' => $errors];
        if ($data) $this->repo->update($id, $data);
        return ['success' => true];
    }

    private function validate(array $input, bool $isNew, bool $checkPassword = false): array {
        $errors = [];
        if ($isNew) {
            if (empty($input['username'])) $errors['username'] = '請輸入使用者名稱';
            elseif (strlen($input['username']) > 50) $errors['username'] = '名稱過長';
            elseif ($this->repo->findByUsername($input['username'])) $errors['username'] = '名稱已存在';

            if (empty($input['email'])) $errors['email'] = '請輸入 Email';
            elseif (!filter_var($input['email'], FILTER_VALIDATE_EMAIL)) $errors['email'] = 'Email 格式錯誤';
            elseif ($this->repo->findByEmail($input['email'])) $errors['email'] = 'Email 已註冊';

            if (empty($input['password'])) $errors['password'] = '請輸入密碼';
            elseif (strlen($input['password']) < 8) $errors['password'] = '密碼至少 8 字元';
            elseif ($input['password'] !== ($input['password_confirm'] ?? '')) $errors['password_confirm'] = '密碼不一致';
        }
        if ($checkPassword && !empty($input['password'])) {
            if (strlen($input['password']) < 8) $errors['password'] = '密碼至少 8 字元';
            elseif ($input['password'] !== ($input['password_confirm'] ?? '')) $errors['password_confirm'] = '密碼不一致';
        }
        return $errors;
    }
}

4. Controller 與 View 關鍵片段

<?php // public/index.php 簡易路由
require_once __DIR__ . '/../bootstrap.php';

$uri = parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH);
$method = $_SERVER['REQUEST_METHOD'];

$routes = [
    'GET /users' => [UserController::class, 'index'],
    'GET /users/create' => [UserController::class, 'create'],
    'POST /users' => [UserController::class, 'store'],
    'GET /users/{id:\d+}' => [UserController::class, 'show'],
    'GET /users/{id:\d+}/edit' => [UserController::class, 'edit'],
    'PUT /users/{id:\d+}' => [UserController::class, 'update'],
    'DELETE /users/{id:\d+}' => [UserController::class, 'destroy'],
    // 簡易路由比對略...
];

// View 片段:users/index.php
?>
ID姓名Email角色狀態建立時間操作
檢視 編輯

安全防護清單

  1. SQL 注入:全程使用 PDO 預處理語句(prepare + execute),ATTR_EMULATE_PREPARES = false。
  2. XSS:輸出到 HTML 統一用 htmlspecialchars($val, ENT_QUOTES, 'UTF-8'),Blade 模板自動轉義 {{ $var }}。
  3. CSRF:表單隱藏 _token,Session 儲存,提交時驗證。
  4. 密碼儲存:password_hash($pwd, PASSWORD_ARGON2ID),驗證用 password_verify。
  5. 強制 HTTPS:Nginx/Apache 強制導向 HTTPS,Cookie 設定 Secure; HttpOnly; SameSite=Strict。
  6. 速率限制:登入、註冊、密碼重設端點加入 IP/帳號限流。

總結

這個 CRUD 範例展示了從資料庫設計、分層架構、PDO 安全查詢、密碼雜湊、輸出轉義到 CSRF 保護的完整安全實務。實際專案建議採用成熟框架(Laravel、Symfony)獲得內建認證、授權、驗證、測試等完整生態,但理解底層原理對除錯與效能調優至關重要。

copyright © 萬盛學電腦網 all rights reserved