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. It could be either strict or soft. For the soft one the code could be a bit simpler due to the fact that we have to choose from two values only:

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

However, nowadays PHP is becoming more and more stricter language, so it's better to follow this way as well. Given the code for the strict checking is almost identical, it's a good reason to create a function:

function white_list(&$value$default$allowed$message) {
    if (empty(
$value)) {
        return 
$default;
    }
    
$key array_search($value$allowedtrue);
    if (
$key === false) { 
        throw new 
InvalidArgumentException($message); 
    } else {
        return 
$value;
    }
}

Having such a function at hand we can have our code much more tidy and concise:

$orderby white_list($_GET['orderby'], "name", ["name","price","qty"], "Invalid field name");
$direction white_list($_GET['direction'], "ASC", ["ASC","DESC"], "Invalid ORDER BY direction");
$query  "SELECT * FROM `table` ORDER BY `$orderby$direction"// sound and safe

Related articles: