PDO order by throws error

Question

I am confused.

This is working:

$sql = 'SELECT * FROM TABLE ORDER BY DATEOFUPLOAD DESC'; 
$stmt = $conn->prepare($sql); 
$stmt->execute();

This is not:

$sql = 'SELECT * FROM TABLE ORDER BY DATEOFUPLOAD :orderbydateofupload'; 
$stmt = $conn->prepare($sql); 
$stmt->bindValue(':orderbydateofupload', $orderbydateofupload, PDO::PARAM_STR);  
$stmt->execute();

I have checked and set $orderbydateofupload by $orderbydateofupload='DESC', so it's definitely not null.

I get an error to the last line ($stmt->execute()):

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''DESC'' at line 1' in /home/gh6534/public_html/query.php:77 Stack trace: #0 /home/gh6534/public_html/query.php(77): PDOStatement->execute() #1 {main} thrown in /home/gh6534/public_html/query.php on line 77

I also tried to use the column as parameter:

$sort = 'DATEOFUPLOAD';
$sql = 'SELECT * FROM TABLE ORDER BY :sort :orderbydateofupload'; 
$stmt = $conn->prepare($sql); 
$stmt->bindParam(':sort', $sort);
$stmt->bindParam(':orderbydateofupload', $orderbydateofupload);
$stmt->execute();

This does not throw an exception, but all items are queried without any sorting. What's wrong?

score 2 · Accepted Answer · answered Jan 26 '13 at 08:25

2

Try this

$orderbydateofupload = 'ASC';  //Or DESC

if($orderbydateofupload == 'DESC')
    $sql = 'SELECT * FROM TABLE ORDER BY DATEOFUPLOAD DESC'; 
else
    $sql = 'SELECT * FROM TABLE'

answered Jan 26 '13 at 08:25

codingbiz

26,179
8
59
96

I guess this question deceived me: http://stackoverflow.com/questions/2683576/how-do-i-use-pdos-prepared-statement-for-order-by-and-limit-clauses – erdomester Jan 26 '13 at 08:58

score 1 · Answer 2 · edited May 23 '17 at 10:34

You can't bind identifiers with PDO because prepared statements can be used only with data, but not with identifiers or syntax keywords.
So, you have to use whitelisting, as shown in the example I posted before

That's why in my own class I use identifier placeholder, which makes whole code into one line (when you need to set the order by field only):

$data = $db->getAll('SELECT * FROM TABLE ORDER BY ?n',$sort);

but with keywords whitelisting is the only choice:

$order = $db->whiteList($_GET['order'],array('ASC','DESC'),'ASC');
$data  = $db->getAll("SELECT * FROM table ORDER BY ?n ?p", $sort, $order);

PDO order by throws error

2 Answers2