2

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

However if I change the line with ORDER BY to:

ORDER BY (?)

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.

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

IveMadeAHugeMistake
  • 79
  • 1
  • 10

1 Answers1

0

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

I have tried:

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

and

$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.

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

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.

Kenny
  • 2,150
  • 2
  • 22
  • 30