将已清理的输入作为where子句中的列名称传递

Question

I have a function that accepts a $filter argument and then pulls data from an SQL table based on the filters in the argument. At first I tried overloading the function so that one function took a single $filter variable and another took an array for multiple filters. But then, I started wondering how I could sanitize the filter tag.

That may have been confusing so here are some examples. For example, a user types in the search box to display all users with the name John. So, $filter_tag would be set to say 'name' and $filter would be set to say 'John'. My PDO query would look something like this:

$query = "SELECT `name` FROM `users` WHERE ";
$query .= $filter_tag." = ?";

The issue is that $filter_tag is not sanitized. If I do sanitize it and the variable is escaped, then the query will not work. Maybe I am making this more complicated than it needs to be and there is some simple solution.

Please comment if you do not understand something that I am asking.

Answer 1

You could create a whitelist of valid tags:

if (in_array($filter_tag, ['name', ...], true)) {
    $query .= $filter_tag . = '?';
}

Alternately you could remove all invalid characters, but I prefer the whitelist approach, because there are only that many valid column names :)

Lastly, instead of the above code you could also turn the condition around and raise an error if the given tag doesn't appear in the whitelist. In some cases this may be the better approach, because otherwise you may get an error later on because the number of arguments passed to ->execute() should match the number of placeholders in the query.