Parameter in order by clause doesn't order -mysql, C#

In my mvc application i am using following query to select some data.I am not usin开发者_高级运维g stored procedure

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

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

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;

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

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

I don't believe the order by clause can be parameterized. Definitely not in Oracle, not 100% sure about MySQL.

You can SELECT the sorting column using a CASE WHEN condition based on a parameter, give it a name then sort by that column.

SELECT col1, col2, 
(CASE ?param 
  WHEN 'col3' THEN col3 
  WHEN 'col4' THEN col4
END) As MyOrder  

FROM myTable
ORDER BY MyOrder

We'll be adding one additional column to the results and I think it won't benefit from indexed columns, slowing the query down, but it works.