使用者管理(建立、讀取、更新、刪除)是 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
<?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,
],
];
<?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();
}
}
<?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;
}
}
<?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 角色 狀態 建立時間 操作
= htmlspecialchars($user['id']) ?>
= htmlspecialchars($user['full_name'] ?? $user['username']) ?>
= htmlspecialchars($user['email']) ?>
= $user['role'] ?>
= $user['status'] ?>
= $user['created_at'] ?>
檢視
編輯
prepare + execute),ATTR_EMULATE_PREPARES = false。htmlspecialchars($val, ENT_QUOTES, 'UTF-8'),Blade 模板自動轉義 {{ $var }}。_token,Session 儲存,提交時驗證。password_hash($pwd, PASSWORD_ARGON2ID),驗證用 password_verify。Secure; HttpOnly; SameSite=Strict。這個 CRUD 範例展示了從資料庫設計、分層架構、PDO 安全查詢、密碼雜湊、輸出轉義到 CSRF 保護的完整安全實務。實際專案建議採用成熟框架(Laravel、Symfony)獲得內建認證、授權、驗證、測試等完整生態,但理解底層原理對除錯與效能調優至關重要。