如何在MySQL語句中包含一個PHP變數

Question

我正在嘗試在內容表中插入值。如果在VALUES中沒有PHP變量，它可以正常工作。但是，當我將變數$type放在VALUES中時，它就無法運作了。我做錯了什麼？

$type = 'testing';
mysql_query("INSERT INTO contents (type, reporter, description) 
     VALUES($type, 'john', 'whatever')");

Answer 1

為了避免SQL注入，插入語句將會是：

$type = 'testing';
$name = 'john';
$description = 'whatever';

$con = new mysqli($user, $pass, $db);
$stmt = $con->prepare("INSERT INTO contents (type, reporter, description) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $type , $name, $description);
$stmt->execute();

Answer 2

在任何MySQL語句中加入PHP變數的規則是簡單明了的：

1. 使用預處理語句

這個規則適用於99%的查詢，也適用於你的查詢。任何代表SQL資料字面量的變數（或簡單來說，就是一個SQL字串或數字），都必須透過預處理語句添加。沒有例外。

這種方法包含四個基本步驟：

• 在你的SQL語句中，用佔位符取代所有的變數
• 對得到的查詢進行準備
• 將變數綁定到佔位符上
• 執行查詢

以下是如何在所有流行的PHP資料庫驅動程式中實現這一點：

使用mysqli新增資料字面量

目前的PHP版本允許你在一次呼叫中完成準備/綁定/執行：

$type = 'testing';
$reporter = "John O'Hara";
$query = "INSERT INTO contents (type, reporter, description) 
             VALUES(?, ?, 'whatever')";
$mysqli->execute_query($query, [$type, $reporter]);

如果你的PHP版本較舊，則必須明確地執行準備/綁定/執行：

$type = 'testing';
$reporter = "John O'Hara";
$query = "INSERT INTO contents (type, reporter, description) 
             VALUES(?, ?, 'whatever')";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("ss", $type, $reporter);
$stmt->execute();

這段程式碼有點複雜，但是關於所有這些運算元的詳細解釋可以在我的文章如何使用Mysqli運行INSERT查詢中找到，以及一個可以大大簡化這個過程的解決方案。

對於SELECT查詢，你可以使用與上面相同的方法：

$reporter = "John O'Hara";
$result = $mysqli->execute_query("SELECT * FROM users WHERE name=?", [$reporter]);
$row = $result->fetch_assoc(); // 或者 while (...)

但是，如果你的PHP版本較舊，你需要執行準備/綁定/執行的例程，並且還需要呼叫get_result()方法，以便從中取得一個熟悉的 mysqli_result，從中可以按照通常的方式提取資料：

$reporter = "John O'Hara";
$stmt = $mysqli->prepare("SELECT * FROM users WHERE name=?");
$stmt->bind_param("s", $reporter);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc(); // 或者 while (...)

使用PDO加入資料字面量

$type = 'testing';
$reporter = "John O'Hara";
$query = "INSERT INTO contents (type, reporter, description) 
             VALUES(?, ?, 'whatever')";
$stmt = $pdo->prepare($query);
$stmt->execute([$type, $reporter]);

在PDO中，我們可以將綁定和執行部分合併在一起，非常方便。 PDO也支援命名佔位符，有些人覺得很方便。

2. 使用白名單過濾

任何其他查詢部分，例如SQL關鍵字、表名或欄位名，或操作符，都必須透過一個白名單來篩選。

有時我們必須加入一個代表查詢的另一部分的變量，例如關鍵字或識別碼（資料庫、表格或欄位名稱）。這是一個罕見的情況，但最好做好準備。

在這種情況下，你的變數必須與你的腳本中明確寫入的值清單進行檢查。這在我的另一篇文章根據使用者的選擇在ORDER BY子句中新增欄位名稱中有詳細解釋：

這是一個例子：

$orderby = $_GET['orderby'] ?: "name"; // 设置默认值
$allowed = ["name","price","qty"]; // 允许的字段名的白名单
$key = array_search($orderby, $allowed, true); // 看看是否有这个名字
if ($key === false) { 
    throw new InvalidArgumentException("无效的字段名"); 
}

$direction = $_GET['direction'] ?: "ASC";
$allowed = ["ASC","DESC"];
$key = array_search($direction, $allowed, true);
if ($key === false) { 
    throw new InvalidArgumentException("无效的ORDER BY方向"); 
}

在這樣的程式碼之後，$direction和$orderby變數可以安全地放入SQL查詢中，因為它們要麼等於允許的變體之一，要麼會拋出錯誤。

最後要提到的是，標識符也必須根據特定的資料庫語法進行格式化。對於MySQL來說，標識符周圍應該是backtick字元。所以我們的ORDER BY範例的最終查詢字串應該是：

$query = "SELECT * FROM `table` ORDER BY `$orderby` $direction";