如何使用 mysqli API 制作一个完全动态的准备语句? [英] How to make a fully dynamic prepared statement using mysqli API?

问题描述

我需要更改此查询以使用准备好的语句.可能吗?

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 制作一个完全动态的准备语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！