問題描述

我需要更改此查詢以使用準(zhǔn)備好的語句.可能嗎?

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 變量并更改查詢中變量的內(nèi)容.

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;

如何使用準(zhǔn)備好的語句執(zhí)行此查詢?

How I could do this query using prepared statement?

• 查詢可能更加靜態(tài)，但這意味著生成不同的準(zhǔn)備好的語句并根據(jù) $_POST 檢查執(zhí)行它.
• 這取決于許多變量，因?yàn)榇瞬樵冊诎阉髯侄魏鸵判虻牧械谋碇酗@示結(jié)果.

一個(gè)完整的查詢示例如下(取決于 $_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 

推薦答案

一個(gè)很好的問題.感謝您轉(zhuǎn)向準(zhǔn)備好的聲明.似乎經(jīng)過這么多年的斗爭，這個(gè)想法終于開始接管了.

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.

免責(zé)聲明:會(huì)有指向我自己網(wǎng)站的鏈接，因?yàn)槲乙呀?jīng)幫助人們使用 PHP 20 多年，并且癡迷于撰寫有關(guān)最常見問題的文章.

是的，這完全有可能.查看我的文章，如何為 mysqli 創(chuàng)建搜索過濾器以獲得完整功能的示例.

Yes, it's perfectly possible. Check out my article, How to create a search filter for mysqli for the fully functional example.

對于 WHERE 部分，您只需要?jiǎng)?chuàng)建兩個(gè)單獨(dú)的數(shù)組 - 一個(gè)包含帶有占位符的查詢條件，另一個(gè)包含這些占位符的實(shí)際值，即:

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字符串作為膠水的所有條件，并得到一個(gè)一流的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() 子句會(huì)有所不同.

The routine is the same for all search conditions, but it will be a bit different for the IN() clause.

有點(diǎn)不同，因?yàn)槟枰砑痈嗾嘉环透嘀?

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 數(shù)組中.解釋可以在我網(wǎng)站上同一部分的相鄰文章中找到.

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 子句后，您可以轉(zhuǎn)到查詢的其余部分

After you are done with WHERE clause, you can move to the rest of your query

您不能參數(shù)化 order by 子句，因?yàn)樽侄蚊Q和 SQL 關(guān)鍵字不能由占位符表示.為了解決這個(gè)問題，我懇求您使用我為此專門編寫的白名單功能.有了它，您可以使您的 ORDER BY 子句 100% 安全但非常靈活.您只需要預(yù)先定義一個(gè)數(shù)組，其中的字段名稱允許在 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

然后使用這個(gè)方便的函數(shù)獲取安全值:

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");

這是一個(gè)智能功能，涵蓋三種不同的場景

this is a smart function, that covers three different scenarios

• 如果沒有提供任何值(即 $_POST["column"] 為空)，將使用白名單中的第一個(gè)值，因此它用作默認(rèn)值
• 如果提供了正確的值，它將在查詢中使用
• 如果提供的值不正確，則會(huì)引發(fā)錯(cuò)誤.

LIMIT 值是完全參數(shù)化的，因此您可以將它們添加到 $parameters 數(shù)組中:

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"; 

并且可以使用以下代碼執(zhí)行

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 是一個(gè)包含查詢返回的所有行的常規(guī)數(shù)組.

where $data is a conventional array contains all the rows returned by the query.

這篇關(guān)于如何使用 mysqli API 制作一個(gè)完全動(dòng)態(tài)的準(zhǔn)備語句?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！