PHP SQLSRV使用预准备语句的参数排序 [英] PHP SQLSRV Sorting with Parameter of Prepared Statement

问题描述

只要我不使用$ sort作为传入参数，我就无法弄清楚为什么排序会起作用.以下示例适用于排序:

I can't figure out why sorting will work as long as I'm not using $sort as a passed in parameter. Example below will work for sorting:

$sort = "quantity desc";

$sql = " with items as (
SELECT i.[item_id]
,i.[name]
,i.[value]
,i.[quantity]
,i.[available]
,isnull(r.awarded, 0) as awarded
, ROW_NUMBER() OVER(
  ORDER BY $sort
) rowNumber 
FROM [Intranet].[dbo].[Goodwell_Item] i
LEFT JOIN (
SELECT r.item_id
, COUNT(1) awarded 
from [Intranet].[dbo].[Goodwell_Reward] r
group by r.item_id
) as r
ON i.item_id = r.item_id
)
SELECT * 
FROM items 
WHERE rowNumber BETWEEN (?) and (?)
and ( (?) = '' OR (available = (?)))
";

$params = array( $pagify['startFrom'], $end, $available, $available );

$stmt = sqlsrv_query( $conn, $sql, $params );

但是，如果我将ORDER BY的行更改为:

However if I change the line with ORDER BY to:

ORDER BY (?)

像这样将其添加到我的$ params中:

and add it to my $params like so:

$params = array($sort, $pagify['startFrom'], $end, $available, $available );

然后由于某种原因而忽略了排序.

then the sort for some reason is being ignored.

请告诉我如何以不允许SQL注入的方式使排序工作.

Please tell me how to get the sort working in a way that doesn't allow SQL injection.

推荐答案

我正在处理这个确切的问题，无法在线找到任何帮助.

I am dealing with this exact issue right now, and cannot find anything online to help.

我尝试过:

 $query = "SELECT * FROM {$this->view} WHERE SeriesID = ? ORDER BY ? ";
 $result = $conn->getData($query, array($seriesID,$sortBy));

和

$query = "SELECT * FROM {$this->view} WHERE SeriesID = ? ORDER BY ? ?";
$result = $conn->getData($query, array($seriesID,$sortBy,$sortOrder));

在两种情况下，我都没有错误，也没有结果.

In both cases, I get no error, and no results.

我认为安全解决此问题的唯一方法是在查询之前使用switch语句手动验证可接受的值.但是，除非只处理一张表，否则您将不知道SortBy列的可能值是多少.

I think the only way to solve this safely is to use a switch statement before the query to manually validate the acceptable values. However, unless you're only ever dealing with one table, you can't know what the possible values are for the SortBy column.

但是，如果仅假设此时的值已被清除，则可以使用非参数化版本，如下所示:

However, if you just go with the assumption that the values at this point have already been cleaned, you can go with the non-parameterized version like this:

 $query = "SELECT * FROM {$this->view} WHERE SeriesID = ? ORDER BY " . $sortBy . " " . $sortOrder;
 $result = $conn->getData($query, array($seriesID));

我计划要做的是在将sortBy和sortOrder传递给包含此代码的方法之前，先对其进行验证.通过这种方式，我调用代码的每个地方都有责任在发送数据之前验证数据.调用代码将知道它正在调用的表(在这种情况下为视图)的有效可能值. (在这种情况下，我是这两段代码的作者，所以我知道它是安全的.)

What I plan to do is make sure to validate sortBy and sortOrder before I pass them to the method that contains this code. By doing it this way, each place I call the code becomes responsible for validating the data before sending it. The calling code would know the valid possible values for the table (or view in this case) that it is calling. (I'm the author of both pieces of code in this case, so I know it's safe.)

因此，简而言之，只需确保代码中此刻的值已被清理和安全，然后将这种责任推到调用此代码的代码上即可.

So, in short, just make sure that the values at this point in the code are already cleaned and safe, and push that responsibility up one level the code that calls this code.

这篇关于PHP SQLSRV使用预准备语句的参数排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！