order by子句中的参数不排序-mysql，C# [英] Parameter in order by clause doesn't order -mysql, C#

问题描述

在我的mvc应用程序中，我正在使用以下查询来选择一些数据.我没有在使用存储过程

In my mvc application i am using following query to select some data.I am not using stored procedure

 select recordID,ChannelID,UserID ,StartTime ,Duration,SeqNum from result  WHERE SeqNum = ?pSeqNum
             ORDER BY StartTime  DESC limit ?pStartIndex, ?pRecordsPerPage;

我将值传递给LIMT.而且效果很好.

I will pass values to LIMT. and Its works fine.

现在我给用户一个选项，可以按条件选择顺序(用户从recordID，ChannelID，UserID，StartTime和Duration中选择一个). 所以，请尝试以下代码.

Now i gave an option to user to select order by condition (user select one from recordID,ChannelID,UserID ,StartTime ,Duration) . So o tried following code.

 select recordID,ChannelID,UserID ,StartTime ,Duration,SeqNum from result  WHERE SeqNum = ?pSeqNum
             ORDER BY ?pOrderBy  DESC limit ?pStartIndex, ?pRecordsPerPage;

当我传递pStartIndex和pRecordsPerPage时，我传递了pOrderBy的值. 但是它不能正常工作了.它只能选择无序排序的数据

I passed values for pOrderBy as i passes for pStartIndex, and pRecordsPerPage. But its not working order By. Its only selecting the data without order by

推荐答案

这是因为ORDER BY使用数据库标识符(即列，别名或表达式).您正在将值作为参数传递.

This is because ORDER BY uses a database identifier (ie, a column, alias or expression). You are passing the value as a parameter.

换句话说，结果查询将等同于

In other words, the resulting query would be equivalent to

... ORDER BY 'StartTime' ...

如果您根据一组已知值(即可用列)验证用户输入，则可以简单地将值插值到查询字符串中，例如(非常粗糙)

If you validate the user input against a known set of values (ie, the available columns), you can simply interpolate the value into the query string, eg (very rough)

$orderBy = $_GET['order_by'];
if (!in_array($orderBy, $orderableColumns)) {
    throw new Exception('Invalid "order by" specified');
}
$query = sprintf('... ORDER BY `%s` ...', $orderBy);

这篇关于order by子句中的参数不排序-mysql，C#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！