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

1. Comments (2)

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. Means we shouldn't silently use the default value if invalid value is provided.

Given the code for the strict checking would almost identical, it's a good reason to create a function:

function white_list(&$value, $allowed, $message) {
    if ($value === null) {
        return $allowed[0];
    }
    $key = array_search($value, $allowed, true);
    if ($key === false) { 
        throw new InvalidArgumentException($message); 
    } else {
        return $value;
    }
}

The function accepts three parameters:

• the checked value. It is passed by reference so it won't raise an error in case a variable is not set. It would allow us to assign a default value if no value is provided.
• the list of allowed values. the first one would serve as a default value
• the error message to throw so a programmer would know what caused the error.

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

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