Adding a field name in the ORDER BY clause based on the user's choice

  1. Comments

Unfortunately, PDO has no placeholder for identifiers (table and field names), therefore a developer must filter them out manually. Such a filter is often called a "white list" (where we only list allowed values) as opposed to a "black list" where we list disallowed values.

So we have to explicitly list all possible variants in the PHP code and then choose from them.

Here is an example with the "soft" approach - in case a field name is not found in the white list, the first one will be chosen

$orderby $_GET['orderby'] ?: "name"// set the default value
$allowed = ["name","price","qty"]; // the white list of allowed field names
$key     array_search($orderby$allowed); // see if we have such a name
$orderby $allowed[$key]; //if not, the first one will be set automatically. smart enough :)

But it is generally considered a bad practice to let an invalid request, so it's better tho throw a an error. For this, just check if array_search returned false and throw the appropriate custom exception/error:

$key     array_search($orderby$allowed); // see if we have such a name
if ($key === false) { 
    
// given there is such a custom exception class
    
throw new http400Error("Invalid order by value"); 
}

Exactly the same approach should be used for the direction, although the code would be a bit simpler due to the fact that we have to choose from two values only

$direction _GET['direction'] == 'DESC' 'DESC' 'ASC';

having gotten these two variables this way will make them 100% safe to be used in the SQL query

$query   "SELECT * FROM `table` ORDER BY $orderby $direction"// sound and safe

Related articles: