註冊系統中檢查用戶名唯一性是高頻操作,正確做法應使用 Prepared Statement 防 SQL 注入、加索引提升查詢速度、避免競態條件。本文對比錯誤寫法與最佳實踐。
<?php
$username = $_REQUEST['username'] ?? '';
$password = $_REQUEST['password'] ?? '';
$conn = mysqli_connect('localhost', 'root', '', 'my_db');
$sql = "SELECT * FROM user WHERE username = '$username'"; // 直接拼接!
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
echo "用戶名已存在";
} else {
$sql = "INSERT INTO user (username, password) VALUES ('$username', '$password')"; // 再次拼接!
mysqli_query($conn, $sql);
}
風險:輸入 admin' -- 即可繞過檢查、任意註冊、甚至刪表。
<?php
$username = trim($_POST['username'] ?? '');
$password = $_POST['password'] ?? '';
if ($username === '' || $password === '') {
exit('參數缺失');
}
// 1. 建立連線(mysqli 物件導向風格)
$mysqli = new mysqli('localhost', 'root', '', 'my_db');
if ($mysqli->connect_errno) {
error_log("DB 連線失敗: {$mysqli->connect_error}");
exit('系統繁忙,請稍後再試');
}
$mysqli->set_charset('utf8mb4');
// 2. 檢查用戶名是否存在(Prepared Statement)
$stmt = $mysqli->prepare('SELECT id FROM user WHERE username = ?');
$stmt->bind_param('s', $username);
$stmt->execute();
$stmt->store_result(); // 重要:緩衝結果集以取得 num_rows
if ($stmt->num_rows > 0) {
$stmt->close();
exit('用戶名已被註冊');
}
$stmt->close();
// 3. 寫入新用戶(密碼必須雜湊!)
$hash = password_hash($password, PASSWORD_ARGON2ID); // PHP 7.2+
$stmt = $mysqli->prepare('INSERT INTO user (username, password_hash) VALUES (?, ?)');
$stmt->bind_param('ss', $username, $hash);
if ($stmt->execute()) {
echo '註冊成功,用戶 ID: ' . $mysqli->insert_id;
} else {
// 捕獲唯一鍵衝突(競態條件:兩請求同時通過檢查)
if ($mysqli->errno === 1062) { // ER_DUP_ENTRY
echo '用戶名已被註冊(併發衝突)';
} else {
error_log("插入失敗: {$mysqli->error}");
echo '系統錯誤,請稍後再試';
}
}
$stmt->close();
$mysqli->close();
ALTER TABLE `user` ADD UNIQUE INDEX `uk_username` (`username`);
即使應用層檢查通過,資料庫唯一索引是最後一道防線,能 100% 防止重複資料寫入(併發安全)。
SELECT id 而非 SELECT *,避免回表<?php
$pdo = new PDO('mysql:host=localhost;dbname=my_db;charset=utf8mb4', 'root', '', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false, // 真正預處理
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$stmt = $pdo->prepare('SELECT id FROM user WHERE username = ?');
$stmt->execute([$username]);
if ($stmt->fetchColumn()) {
exit('用戶名已存在');
}
$hash = password_hash($password, PASSWORD_ARGON2ID);
$stmt = $pdo->prepare('INSERT INTO user (username, password_hash) VALUES (?, ?)');
try {
$stmt->execute([$username, $hash]);
echo '註冊成功,ID: ' . $pdo->lastInsertId();
} catch (PDOException $e) {
if ($e->getCode() === '23000') exit('用戶名已存在');
throw $e;
}
password_hash),嚴禁明文/MD5/SHA1