如何使用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.

免责声明:将提供指向我自己网站的链接，因为我在20多年的时间里一直在帮助使用PHP的人们，并且迷恋于撰写有关最常见问题的文章.

是的，这完全有可能.查看我的文章如何为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.

然后您可以使用AND字符串作为胶水implode所有条件，并获得一流的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屋！