如何使用mysqli API创建完全动态的prepared statement？

Question

我需要将这个查询改为使用预编译语句，是否可能？

查询语句：

$sql = "SELECT id, title, content, priority, date, delivery FROM tasks " . $op . " " . $title . " " . $content . " " . $priority . " " . $date . " " . $delivery . " ORDER BY " . $orderField . " " . $order . " " . $pagination . "";

在查询之前，有代码检查POST变量并更改查询中变量的内容。

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

如何使用预处理语句执行此查询？

• 查询可以更加静态化，但这意味着需要根据 $_POST 检查制作不同的预处理语句并执行它。
• 这取决于许多变量，因为此查询显示结果在包含搜索字段和排序列的表中。

查询的完整示例可能如下（根据 $_POST 检查而异）：

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 

- Kirito

---

这是一个非常动态构建的查询。请[编辑]您的问题，以包括更详细的描述您正在尝试构建何种类型的查询以及原因。为什么查询不能更加“静态”，不依赖于这样的可变组件？还要在您的问题中添加所需查询的示例结果。 - Progman

你不需要准备多个语句，只是因为你从 $_POST 中获取了不同的值。 - bestprogrammerintheworld

https://www.w3schools.com/php/php_mysql_prepared_statements.asp - bestprogrammerintheworld

@bestprogrammerintheworld 我知道如何做到这一点。但是查询需要不同，比如添加或删除IN、>=、<等。这就是我需要做的。 - Kirito

@bestprogrammerintheworld 说实话，这个任务并不简单，有许多怪异和例外情况。而且这个特定页面甚至没有涵盖所有可能出现的问题的10％。正如w3schools经常做的那样，它会推广某些不良实践。 - Your Common Sense

Answer 1

一个很好的问题。感谢您转向准备好的语句。经过那么多年的奋斗，这个想法似乎终于开始普及了。

免责声明：我会链接到自己的网站，因为我帮助人们使用PHP已经20多年了，并且对写关于最常见问题的文章有着痴迷。

是的，完全可以实现。查看我的文章如何为mysqli创建搜索过滤器以获取完全功能的示例。

对于WHERE部分，您只需要创建两个单独的数组——一个包含查询条件和占位符，另一个包含这些占位符的实际值，即：

WHERE子句

$conditions = [];
$parameters = [];

if (!empty($_POST["content"])) {
    $conditions[] = 'content LIKE ?';
    $parameters[] = '%'.$_POST['content ']."%";
}

并且对于所有的搜索条件都是如此。然后您可以使用AND字符串作为粘合剂将所有条件implode在一起，并获得一流的WHERE子句。

if ($conditions)
{
    $where .= " WHERE ".implode(" AND ", $conditions);
}

例程对于所有搜索条件都是相同的，但对于IN()子句会有一些不同。

IN()子句

与其他条件不同，您需要添加更多的占位符和值：

if (!empty($_POST["opID"])) {
    $in  = str_repeat('?,', count($array) - 1) . '?';
    $conditions[] = "opID IN ($in)";
    $parameters = array_merge($parameters, $_POST["opID"]);
}

这段代码将在IN()子句中添加与$_POST["opID"]元素数量相同的?占位符，并将所有这些值添加到$parameters数组中。有关说明，请参阅本站同一部分中相邻文章。

完成WHERE子句后，您可以继续查询的其余部分。 ORDER BY子句

无法使用占位符表示字段名和SQL关键字，因此无法对ORDER BY子句进行参数化。为了解决这个问题，我恳请您使用我为此特定目的编写的白名单函数。使用它，您可以使ORDER BY子句完全安全但非常灵活。您只需要预定义一个允许在ORDER BY子句中使用的字段名数组即可。

$sortColumns = ["title","content","priority"]; // add your own

然后使用这个方便的函数获取安全值：

$orderField = white_list($_POST["column"], $sortColumns, "Invalid column name");
$order = white_list($_POST["order"], ["ASC","DESC"], "Invalid ORDER BY direction");

这是一个智能函数，涵盖了三种不同的情况：

• 如果没有提供值（即 $_POST["column"] 为空），将使用白名单中的第一个值作为默认值
• 如果提供了正确的值，则将其用于查询
• 如果提供了错误的值，则会抛出错误

LIMIT 子句

LIMIT 值已完全参数化，因此您只需将它们添加到 $parameters 数组中：

$limit = "LIMIT ?, ?";
$parameters[] = $offset;
$parameters[] = $recordsPerPage;

最终装配

最后，您的查询将是这样的

$sql = "SELECT id, title, content, priority, date, delivery 
        FROM tasks INNER JOIN ... $where ORDER BY `$orderField` $order $limit"; 

而且它可以使用以下代码执行

$stmt = $mysqli->prepare($sql);
$stmt->bind_param(str_repeat("s", count($parameters)), ...$parameters);
$stmt->execute();
$data = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

$data 是一个传统数组，包含查询返回的所有行。

- Your Common Sense

---

为什么白名单功能使用 array_search 而不是 in_array。键没有被使用。通过引用的黑客将导致难以调试的代码。它不是指出错误，而是神奇地返回一个值。这个责任应该在调用方。$_POST["column"] ?? 'title'。没有魔法（为什么要使用第一个？）。没有使用引用，这总是很好的。并且从调用方有更多控制回退。最重要的是，必须始终写下失败消息，这是一个次优的 API，因为它看起来有点笨拙。最好情况下非常繁琐。 - PeeHaa

@PeeHaa 嗯，说得有道理。这些解决方案大多来自遗留系统，没有经过深思熟虑。看起来我会实现你所说的一切。 - Your Common Sense