Whitelisting helper function

Comments (3)

There are edge cases when we cannot use a prepared statement for a variable to be used in the query. Given placeholders can substitute only data literals (i.e. strings and numbers), we need something to protect other query pars, such as keywords or identifiers that may happen to be added to the query dynamically.

In this case we have to use the white list approach, filtering our data against a predefined list of allowed values. To ease the routine, I wrote a handy function that will let us to get the safe value in one line! Here it is:

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 value to be checked. 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!

Latest comments:

17.12.24 07:19
Charles Roth for UPDATE query using PDO:
As described in one of your other excellent :-) phpdelusions pages, fetch() can do a (say)...
read more
16.12.24 20:08
Myles for (The only proper) PDO tutorial:
You only have silver on SQL-INJECTION. No one has gold.
read more
06.12.24 22:23
Charles Roth for (The only proper) PDO tutorial:
In your wonderful phpdelusions.net/pdo, you state that the types of results will always be...
read more
03.12.24 14:37
Adrian for Articles:
Have you written any books on PHP, or recommend any books? Thank you very much, and keep up the...
read more
26.11.24 18:45
Hans for INSERT query using PDO:
Excellent, very well explained and didactic Thank you
read more

Add a comment

Please refrain from sending spam or advertising of any sort.
Messages with hyperlinks will be pending for moderator's review.

Markdown is now supported:

> before and an empty line after for a quote
to mark a block of code add four spaces before each line and empty lines before and after

Comments:

Benanamen, 23.02.22 18:53

Just an FYI: The else is not necessary. You already have the guard clause that will throw an exception and stop execution. You can just return the value without the else.
Chris, 08.02.22 14:12
Hi, Please disregard my old comment! No coffee or breakfast for me yet! I've re-phrased my question. Great website which I always use and find it very helpful.

I've used your function for a lot of backend stuff where I'm displaying data in tables with headers which are clickable and sortable, my question is, how would I use it to display part of a mySQL query safely?

I need to to able to sort also using the ORDER BY (PreviousPrice - Price) clause which shows the savings, I know I could add an extra DB field and add the savings in the additional field and do it that way instead. But can it be done safely using part of an SQL query like that? Or have I answered my own question by just creating it with the additional field?

I basically want to create a dropdown filter to sort by make/model/price & savings as above.
```
$stmt = $db->Query("SELECT * FROM vehicles 
WHERE ((PreviousPrice > Price) AND (Feed_id = 1 OR Feed_id = 2))
ORDER BY (PreviousPrice - Price) DESC LIMIT $offset,$rowsperpage");
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
//results here
}
```
Thanks!
Chris, 08.02.22 13:58
Hi, Great website which I always use and find it very helpful.

I've used your function for a lot of backend stuff where I'm displaying data in tables with headers which are clickable and sortable, my question is, how would I use it to display part of a mySQL query safely?

I need to to able to sort also using the ORDER BY (PreviousPrice - Price) clause which shows the savings, I know I could add an extra DB field and add them together via SUM and do it that way instead. Can it be done safely using part of an SQL query instead of $_GET parameters.

I basically want to create a dropdown filter to sort by make/model/price & savings as above.
```
 $stmt = $db->Query("SELECT * FROM vehicles WHERE ((PreviousPrice > Price) AND (Feed_id = 1 OR Feed_id = 2))  
ORDER BY (PreviousPrice - Price) DESC LIMIT $offset,$rowsperpage");
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
//results here
}
```
Thanks!

17.12.24 07:19
Charles Roth for UPDATE query using PDO:
As described in one of your other excellent :-) phpdelusions pages, fetch() can do a (say)...
read more

16.12.24 20:08
Myles for (The only proper) PDO tutorial:
You only have silver on SQL-INJECTION. No one has gold.
read more

06.12.24 22:23
Charles Roth for (The only proper) PDO tutorial:
In your wonderful phpdelusions.net/pdo, you state that the types of results will always be...
read more

03.12.24 14:37
Adrian for Articles:
Have you written any books on PHP, or recommend any books? Thank you very much, and keep up the...
read more

26.11.24 18:45
Hans for INSERT query using PDO:
Excellent, very well explained and didactic Thank you
read more

Whitelisting helper function

Related articles:

Got a question?

SEE ALSO:

Latest comments:

Add a comment

Comments: