<?php
// 原代碼片段
$result = mysql_query("INSERT INTO t_dblocal_userinformation (ID, OSTYPE, UUID, LASTDATE) VALUES (NULL, $ostype, $uuid, $nowtime)");
三大致命問題:
$ostype、$uuid、$nowtime 直接拼接,若為字串類型 SQL 語法錯誤。time() 返回 Unix 時間戳 (整數),資料庫 DATE 需 Y-m-d,DATETIME 需 Y-m-d H:i:s。$_POST),無任何過濾。<?php
// 配置
$dsn = 'mysql:host=localhost;dbname=your_db;charset=utf8mb4';
$user = 'db_user';
$pass = 'db_pass';
try {
$pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false, // 真實預處理
]);
// 輸入獲取與驗證
$ostype = $_POST['ostype'] ?? '';
$uuid = $_POST['uuid'] ?? '';
if (!$ostype || !$uuid) {
throw new InvalidArgumentException('缺少必要參數');
}
// 時間格式化 (假設 LASTDATE 為 DATETIME)
$now = (new DateTime())->format('Y-m-d H:i:s');
// 參數化插入
$sql = 'INSERT INTO t_dblocal_userinformation (OSTYPE, UUID, LASTDATE) VALUES (?, ?, ?)';
$stmt = $pdo->prepare($sql);
$stmt->execute([$ostype, $uuid, $now]);
$id = $pdo->lastInsertId();
echo json_encode(['code' => 1000, 'message' => '插入成功', 'id' => $id]);
} catch (PDOException $e) {
// 生產環境不輸出具體錯誤,記錄日誌
error_log('DB Insert Error: ' . $e->getMessage());
echo json_encode(['code' => 1002, 'message' => '插入失敗']);
} catch (Exception $e) {
echo json_encode(['code' => 1001, 'message' => $e->getMessage()]);
}
<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->set_charset('utf8mb4');
$ostype = $mysqli->real_escape_string($_POST['ostype'] ?? '');
$uuid = $mysqli->real_escape_string($_POST['uuid'] ?? '');
$now = date('Y-m-d H:i:s');
$sql = "INSERT INTO t_dblocal_userinformation (OSTYPE, UUID, LASTDATE) VALUES (?, ?, ?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('sss', $ostype, $uuid, $now);
if ($stmt->execute()) {
echo json_encode(['code' => 1000, 'id' => $stmt->insert_id]);
} else {
echo json_encode(['code' => 1002, 'message' => '執行失敗: ' . $stmt->error]);
}
$stmt->close();
$mysqli->close();
| MySQL 類型 | PHP 格式化 | 範例 |
|---|---|---|
| DATE | date('Y-m-d') | 2024-01-15 |
| DATETIME | date('Y-m-d H:i:s') | 2024-01-15 14:30:00 |
| TIMESTAMP | date('Y-m-d H:i:s') 或 @timestamp | 1970-2038 年範圍 |
| TIME | date('H:i:s') | 14:30:00 |
| YEAR | date('Y') | 2024 |
<?php
class ApiCode {
public const SUCCESS = 1000;
public const DB_CONN_FAIL = 1001;
public const DB_EXEC_FAIL = 1002;
public const VALID_FAIL = 1003;
public const PARAM_MISSING = 1004;
// ...
}
// 統一回應格式
function jsonResponse(int $code, string $message = '', mixed $data = null): never
{
http_response_code($code >= 1000 ? 200 : 400); // 業務碼非 HTTP 碼
echo json_encode([
'code' => $code,
'message' => $message,
'data' => $data,
'time' => time(),
], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
exit;
}
mysql_(connect|query|fetch|escape)。prepare() → bind_param/execute() → fetch()。addslashes()、mysql_real_escape_string() 手動轉義。ERRMODE_EXCEPTION) 統一異常處理。核心鐵律:永遠使用參數化查詢,永不拼接 SQL 字串,時間格式務必匹配欄位類型。