問題描述
我需要更改此查詢以使用準備好的語句.可能嗎?
I need to change this query to use a prepared statement. Is it possible?
查詢:
$sql = "SELECT id, title, content, priority, date, delivery FROM tasks " . $op . " " . $title . " " . $content . " " . $priority . " " . $date . " " . $delivery . " ORDER BY " . $orderField . " " . $order . " " . $pagination . "";
在查詢之前，有代碼來檢查 POST 變量并更改查詢中變量的內容.
Before the query, there's code to check the POST variables and change the content of variables in the query.
//For $op makes an INNER JOIN with or without IN clause depending on the content of a $_POST variable
$op = "INNER JOIN ... WHERE opID IN ('"$.opID."')";
//Or
$op = "INNER JOIN ... ";
//For $title (depends of $op):
$title = "WHERE title LIKE'%".$_POST["title"]."%'";
//Or
$title = "AND title LIKE'%".$_POST["title"]."%'";
//For $content:
$content = "AND content LIKE '%".$_POST["content"]."%'";
//For $priority just a switch:
$priority = "AND priority = DEPENDING_CASE";
//For $date and $delivery another switch
$d = date("Y-m-d", strtotime($_POST["date"]));
$date = "AND date >= '$d' 00:00:00 AND date <= '$d' 23:59:59";
//Or $date = "AND date >= '$d' 00:00:00";
//Or $date = "AND date <= '$d' 23:59:59";
//For $orderField
$orderField = $_POST["column"];
//For $order
$order= $_POST["order"];
//For $pagination
$pagination = "LIMIT ".$offset.",". $recordsPerPage;
如何使用準備好的語句執行此查詢?
How I could do this query using prepared statement?
- 查詢可能更加靜態，但這意味著生成不同的準備好的語句并根據 $_POST 檢查執行它.
- 這取決于許多變量，因為此查詢在包含搜索字段和要排序的列的表中顯示結果.
一個完整的查詢示例如下(取決于 $_POST 檢查):
A full example of query would be like this (depending of $_POST checks):
SELECT id, title, content, priority, date, delivery FROM tasks INNER JOIN op ON task.op = op.opId WHERE op IN (4851,8965,78562) AND title LIKE '%PHT%' AND content LIKE '%%' AND priority = '2' ORDER BY date DESC LIMIT 0, 10
推薦答案
一個很好的問題.感謝您轉向準備好的聲明.似乎經過這么多年的斗爭，這個想法終于開始接管了.
An excellent question. And thank you for moving to prepared statements. It seems that after all those years of struggle, the idea finally is starting to take over.
免責聲明:會有指向我自己網站的鏈接，因為我已經幫助人們使用 PHP 20 多年，并且癡迷于撰寫有關最常見問題的文章.
是的，這完全有可能.查看我的文章，如何為 mysqli 創建搜索過濾器以獲得完整功能的示例.
Yes, it's perfectly possible. Check out my article, How to create a search filter for mysqli for the fully functional example.
對于 WHERE 部分，您只需要創建兩個單獨的數組 - 一個包含帶有占位符的查詢條件，另一個包含這些占位符的實際值，即:
For the WHERE part, all you need is to create two separate arrays - one containing query conditions with placeholders and one containing actual values for these placeholders, i.e:
$conditions = [];
$parameters = [];
if (!empty($_POST["content"])) {
$conditions[] = 'content LIKE ?';
$parameters[] = '%'.$_POST['content ']."%";
}
等，適用于所有搜索條件.
and so on, for all search conditions.
然后你可以implode使用AND字符串作為膠水的所有條件，并得到一個一流的WHERE子句:
Then you could implode all the conditions using AND string as a glue, and get a first-class WHERE clause:
if ($conditions)
{
$where .= " WHERE ".implode(" AND ", $conditions);
}
所有搜索條件的例程都相同，但對于 IN() 子句會有所不同.
The routine is the same for all search conditions, but it will be a bit different for the IN() clause.
有點不同，因為您需要添加更多占位符和更多值:
is a bit different as you will need more placeholders and more values to be added:
if (!empty($_POST["opID"])) {
$in = str_repeat('?,', count($array) - 1) . '?';
$conditions[] = "opID IN ($in)";
$parameters = array_merge($parameters, $_POST["opID"]);
}
此代碼將向 IN() 子句添加與 $_POST["opID"] 中的元素一樣多的 ? 占位符并將所有這些值添加到 $parameters 數組中.解釋可以在我網站上同一部分的相鄰文章中找到.
this code will add as many ? placeholders to the IN() clause as many elements in the $_POST["opID"] and will add all those values to the $parameters array. The explanation can be found in the adjacent article in the same section on my site.
完成WHERE 子句后，您可以轉到查詢的其余部分
After you are done with WHERE clause, you can move to the rest of your query
您不能參數化 order by 子句，因為字段名稱和 SQL 關鍵字不能由占位符表示.為了解決這個問題，我懇求您使用我為此專門編寫的白名單功能.有了它，您可以使您的 ORDER BY 子句 100% 安全但非常靈活.您只需要預先定義一個數組，其中的字段名稱允許在 order by 子句中:
You cannot parameterize the order by clause, because field names and SQL keywords cannot be represented by a placeholder. And to tackle with this problem I beg you to use a whitelisting function I wrote for this exact purpose. With it you can make your ORDER BY clause 100% safe but perfectly flexible. All you need is to predefine an array with field names allowed in the order by clause:
$sortColumns = ["title","content","priority"]; // add your own
然后使用這個方便的函數獲取安全值:
and then get safe values using this handy function:
$orderField = white_list($_POST["column"], $sortColumns, "Invalid column name");
$order = white_list($_POST["order"], ["ASC","DESC"], "Invalid ORDER BY direction");
這是一個智能功能，涵蓋三種不同的場景
this is a smart function, that covers three different scenarios
- 如果沒有提供任何值(即 $_POST["column"] 為空)，將使用白名單中的第一個值，因此它用作默認值
- 如果提供了正確的值，它將在查詢中使用
- 如果提供的值不正確，則會引發錯誤.
LIMIT 值是完全參數化的，因此您可以將它們添加到 $parameters 數組中:
LIMIT values are perfectly parameterized so you can just add them to the $parameters array:
$limit = "LIMIT ?, ?";
$parameters[] = $offset;
$parameters[] = $recordsPerPage;
總裝
最后，您的查詢將是這樣的
The final assembly
In the end, your query will be something like this
$sql = "SELECT id, title, content, priority, date, delivery
FROM tasks INNER JOIN ... $where ORDER BY `$orderField` $order $limit";
并且可以使用以下代碼執行
And it can be executed using the following code
$stmt = $mysqli->prepare($sql);
$stmt->bind_param(str_repeat("s", count($parameters)), ...$parameters);
$stmt->execute();
$data = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
其中 $data 是一個包含查詢返回的所有行的常規數組.
where $data is a conventional array contains all the rows returned by the query.
這篇關于如何使用 mysqli API 制作一個完全動態的準備語句?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！