0

I'm trying to select from a table using a particular sorting, based on a query string that determines both the column to sort by, and the order to sort it in. This results in a SQL syntax error, but I really don't understand what the problem is. I get the same error no matter what I try:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?' at line 1

This is the code:

$stmt = $db->connect()->prepare("SELECT * FROM media ORDER BY :sortBy :sortOrder");
$stmt->bindParam(':sortBy', $sortBy);
$stmt->bindParam(':sortOrder', $sortOrder);
$stmt->execute();
$media = $stmt->fetchAll();

What's so bizarre is that when I replace the :placeholders from the query with actual text, it works:

$stmt = $db->connect()->prepare("SELECT * FROM media ORDER BY title ASC");

This puts the table data in $media as intended, but of course this way I can't actually change the sorting. I've made very sure the variables $sortBy and $sortOrder are set correctly; that's definitely not the problem.

This is on a local XAMPP server using MariaDB 10.4.16. What on earth is going on here?

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Luke
  • 181
  • 5
  • 2
    You cannot use placeholders at all places within an sql statement and hope that they will work - they are intended for specific uses and it looks like that is not one of them – Professor Abronsius Dec 10 '20 at 13:44
  • 1
    Its the `:sortBy and :sortOrder` that is the issue. You cannot use a parameter there or for tablenames or column names. The reason: The prepare submits the query for compilation, MySQL (or anything else) cannot compile something if it is not sure what to compile because key parts of a statement are missing. – RiggsFolly Dec 10 '20 at 13:44
  • 1
    You will need to inject `ASC` or `DESC` for the sort order. Make sure you hardwire these (i.e. don't use user input directly). – halfer Dec 10 '20 at 13:47

0 Answers0