原文使用 mysql_* 函數,**已在 PHP 7.0 移除**,嚴禁在新專案使用。本文提供現代化 PDO 版本,並修正 SQL 注入、編碼、分頁、錯誤處理等關鍵問題。
<?php
// config.php - 資料庫設定
return [
'dsn' => 'mysql:host=localhost;dbname=jilinwula;charset=utf8mb4',
'user' => 'root',
'pass' => 'root',
'options' => [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false, // 真實預備語句,防注入
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",
],
];
// api/users.php
require 'config.php';
try {
$pdo = new PDO(...$config['dsn'], $config['user'], $config['pass'], $config['options']);
} catch (PDOException $e) {
http_response_code(500);
echo json_encode(['error' => 'Database connection failed']);
exit;
}
// 1. 參數驗證與預設值
$page = max(1, (int)($_GET['page'] ?? 1));
$pageSize = min(100, max(1, (int)($_GET['pageSize'] ?? 10))); // 限制最大 100
$offset = ($page - 1) * $pageSize;
// 2. 總筆數(分頁資訊)
$totalStmt = $pdo->query('SELECT COUNT(*) FROM userinfo');
$total = (int)$totalStmt->fetchColumn();
// 3. 參數化查詢(防 SQL 注入)
$stmt = $pdo->prepare('SELECT username, password FROM userinfo LIMIT :offset, :limit');
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':limit', $pageSize, PDO::PARAM_INT);
$stmt->execute();
$users = $stmt->fetchAll();
// 4. 輸出標準 JSON 格式
header('Content-Type: application/json; charset=utf-8');
echo json_encode([
'success' => true,
'data' => $users,
'pagination' => [
'page' => $page,
'pageSize' => $pageSize,
'total' => $total,
'totalPages' => (int)ceil($total / $pageSize),
],
], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
?>
<?php
class UserRepository
{
private PDO $pdo;
public function __construct(PDO $pdo)
{
$this->pdo = $pdo;
}
public function findPaginated(int $page, int $pageSize, array $filters = []): array
{
$where = [];
$params = [];
// 動態建構 WHERE
if (!empty($filters['username'])) {
$where[] = 'username LIKE :username';
$params[':username'] = '%' . $filters['username'] . '%';
}
if (!empty($filters['status'])) {
$where[] = 'status = :status';
$params[':status'] = $filters['status'];
}
$whereSql = $where ? 'WHERE ' . implode(' AND ', $where) : '';
// 總數
$total = (int)$this->pdo->query("SELECT COUNT(*) FROM userinfo $whereSql")->fetchColumn();
// 分頁資料
$offset = ($page - 1) * $pageSize;
$sql = "SELECT id, username, email, created_at FROM userinfo $whereSql ORDER BY id DESC LIMIT :offset, :limit";
$stmt = $this->pdo->prepare($sql);
foreach ($params as $k => $v) $stmt->bindValue($k, $v);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':limit', $pageSize, PDO::PARAM_INT);
$stmt->execute();
return [
'data' => $stmt->fetchAll(),
'pagination' => [
'page' => $page,
'pageSize' => $pageSize,
'total' => $total,
'totalPages' => (int)ceil($total / $pageSize),
],
];
}
}
// 控制器使用
$repo = new UserRepository($pdo);
$result = $repo->findPaginated($page, $pageSize, ['username' => $_GET['q'] ?? '']);
echo json_encode(['success' => true, ...$result], JSON_UNESCAPED_UNICODE);
?>
| 舊寫法 (mysql_*) | 問題 | 現代寫法 (PDO) |
|---|---|---|
| mysql_connect | 無預備語句、無例外處理 | new PDO(..., [ATTR_ERRMODE=>EXCEPTION]) |
| mysql_query($sql) | 字串拼接 → SQL 注入 | $pdo->prepare + bindValue |
| mysql_fetch_assoc | 逐行取得,效能較差 | $stmt->fetchAll() 一次取完 |
| SET NAMES 'UTF8' | UTF8 非完整 Unicode (無 Emoji) | charset=utf8mb4 + COLLATE utf8mb4_unicode_ci |
| json_encode($arr) | 中文會被 Unicode 轉義 | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES |
| die('error') | 輸出 HTML/純文字,非 JSON | http_response_code + json_encode(['error'=>...]) |
<?php
// 成功
{
"success": true,
"data": [...],
"pagination": { "page": 1, "pageSize": 10, "total": 100, "totalPages": 10 }
}
// 失敗
{
"success": false,
"error": {
"code": "VALIDATION_ERROR",
"message": "頁碼必須大於 0",
"details": { "page": "must be >= 1" }
}
}
// 統一輸出函數
function jsonResponse(array $data, int $status = 200): never
{
http_response_code($status);
header('Content-Type: application/json; charset=utf-8');
echo json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
exit;
}
?>
SELECT * 會傳輸多餘資料、增加記憶體PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false + fetch() 迴圈拋棄 mysql_*,擁抱 PDO + 預備語句 + JSON 標準輸出,是 PHP 後端開發的基本功。