簡易 user 類別封裝單條件與多條件查詢,但存在 SQL 注入風險、硬編碼表名、使用已棄用 mysql_* 函數。本文分析問題並給出 PDO 重構版。
class user {
var $usertable;
function get_oneuser($field, $value) {
$field_array = ["id", "name"];
if (in_array($field, $field_array)) {
$sql = "SELECT * FROM `$this->usertable` WHERE $field='$value'"; // ❌ 直接拼接
$db = new database;
$res = $db->execute($sql);
return mysql_fetch_object($res);
}
}
function get_moreusers() {
$args = func_get_args();
switch (func_num_args()) {
case 0: $sql = "SELECT * FROM `$this->usertable`"; break;
case 2: $sql = "SELECT * FROM `$this->usertable` WHERE $args[0]='$args[1]'"; break;
case 4: $sql = "SELECT * FROM `$this->usertable` WHERE $args[0]='$args[1]' AND $args[2]='$args[3]'"; break;
}
// ...
}
}
$value 直接拼接,無轉義$field,$value 未過濾mysql_* PHP 7.0+ 已移除$usertable 需外部設定,易忘<?php
class UserRepository {
private PDO $pdo;
private string $table;
public function __construct(PDO $pdo, string $table = 'users') {
$this->pdo = $pdo;
$this->table = $table;
}
// 單一欄位精確查詢 (白名單保護)
public function findByField(string $field, $value): ?object {
$allowed = ['id', 'name', 'email', 'phone', 'status'];
if (!in_array($field, $allowed, true)) {
throw new InvalidArgumentException("Invalid field: $field");
}
$stmt = $this->pdo->prepare("SELECT * FROM `{$this->table}` WHERE `$field` = ? LIMIT 1");
$stmt->execute([$value]);
return $stmt->fetchObject() ?: null;
}
// 多條件動態查詢
public function findBy(array $conditions, array $options = []): array {
$allowedFields = ['id', 'name', 'email', 'phone', 'status', 'created_at'];
$where = [];
$params = [];
foreach ($conditions as $field => $value) {
if (!in_array($field, $allowedFields, true)) continue;
$where[] = "`$field` = ?";
$params[] = $value;
}
$sql = "SELECT * FROM `{$this->table}`";
if ($where) $sql .= " WHERE " . implode(" AND ", $where);
if (!empty($options['order'])) {
$sql .= " ORDER BY " . $this->sanitizeOrder($options['order']);
}
if (!empty($options['limit'])) {
$sql .= " LIMIT " . (int)$options['limit'];
}
if (!empty($options['offset'])) {
$sql .= " OFFSET " . (int)$options['offset'];
}
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll(PDO::FETCH_OBJ);
}
private function sanitizeOrder(string $order): string {
// 簡單白名單:欄名 ASC/DESC
if (preg_match('/^([a-z_]+)\s+(ASC|DESC)$/i', $order, $m)) {
return in_array($m[1], ['id','name','email','created_at']) ? "$m[1] $m[2]" : 'id DESC';
}
return 'id DESC';
}
}
// 使用
$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", "root", "root");
$repo = new UserRepository($pdo, 'users');
$user = $repo->findByField('email', 'test@example.com');
$users = $repo->findBy(['status'=>1, 'name'=>'張三'], ['order'=>'created_at DESC', 'limit'=>10]);
$qb = $repo->newQuery()
->where('status', 1)
->whereLike('name', '%張%')
->orderBy('created_at', 'DESC')
->paginate(15);
推薦採用成熟 ORM (Eloquent、Doctrine) 或查詢建構器,避免手寫 SQL 拼接。