具有几个可选搜索词的参数化查询 [英] Parameterized query with several optional search terms

问题描述

我有一个包含大量数据的Web应用程序，以及一个具有多个字段(例如名称，状态，日期等)的搜索/过滤器功能.我一直在对常规(非搜索)查询使用这样的参数化查询:

I have a web application with lots of data, and a search/filter function with several fields, such as name, status, date, and so on. I have been using parameterized queries like this for the regular (non-search) queries:

$id = $_POST['itemID'];
$db = mysqli_connect($host, $username, $password, $database);
$sql_query = "SELECT * FROM tbl_data WHERE ID = ?";
$stmt_query = mysqli_prepare($db, $sql_query);
mysqli_stmt_bind_params($stmt_query, "i", $id);
mysqli_stmt_execute($stmt_query);

//and so on..

如何使用多个可选参数来保护SQL注入?最多可以设置10个单独的参数.

How would I protect agains SQL-injection with multiple, optional parameters? There can be up to 10 separate parameters that might or might not be set.

编辑该问题似乎不清楚:

我的示例使用一个参数，该参数不是可选的.我知道这可以防止sql注入.我将如何使用10个参数(可以同时设置一个或多个)来执行此操作?例如.这样的查询:

My example was with one parameter, which is not optional. I know this protects against sql-injection. How would I go about doing this with 10 parameters, where one or several could be set at the same time? E.g. a query such as this:

SELECT * FROM tbl_data 
WHERE NAME = ? 
AND STATUS = ? 
AND DATE = ? 
AND PARAM4 = ? 
AND PARAM5 = ?

用户只想搜索名称和日期的地方.我将如何进行装订?检查100个可能的搜索词组合中的每一个都不是一个好主意.

where the user only wants to search on name and date. How would I do the binding? It's not a good idea to check for each of the 100 possible combinations of search terms.

推荐答案

由于您正在使用mysqli_stmt_bind_params，因此您已经受到保护，可以防止sql注入.

You are already protected against sql injection, as you are using the mysqli_stmt_bind_params which will escape properly for you.

编辑. 您可以切换到某些数据库框架来获得干净漂亮的代码.

Edit. You could switch to some database framework to have a clean and beautiful code.

否则...这真是旧的意大利面条风格...但我喜欢它:D

Otherwise... this is so old spaghetti style... but I love it :D

扩展代码以使用未知数量的参数非常容易.您应该只循环输入参数，并同时进行1.用问号表示法构建查询字符串，然后将参数添加到数组中，然后将其传递给maxdb_stmt_bind_param(resource $ stmt，string $ types，array& ; $ var).

It's quite easy to expand your code to work with an unknown number of parameters. You should just loop on your parameters and at the same time 1. build your query string with the question mark notation, and add your parameters to an array, which you will be passing to maxdb_stmt_bind_param ( resource $stmt , string $types , array &$var ).

所以看起来像这样.它假定至少存在一个参数(但是避免这种情况很简单).

So it would look like this. It assumes at least ONE parameter is there (but it's trivial to avoid this).

$sql = "SELECT * FROM tbl_data WHERE ";
$and = '';
$types = '';
$parameters = array();
foreach($_POST as $k => $v) {
  // check that $k is on your whitelist, if not, skip to the next item
  $sql .= "$and $k = ?";
  $and = " AND ";
  $parameters[] = $v;
  $types .= 's';
}
$stmt_query = mysqli_prepare($db, $sql);
mysqli_stmt_bind_params($stmt_query, $types, $parameters);

这篇关于具有几个可选搜索词的参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！