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:

02.04.24 23:57
Pablo for How to call stored procedures with mysqli:
Muchas gracias por el aporte, esta instruccion es la que me faltaba ($mysqli->next_results(); no...
read more
27.03.24 11:10
Grespin for How to run a SELECT query using Mysqli:
Its not working and im going insane
read more
26.03.24 10:45
Hello world for Articles:
to mark a block of code add four spaces before each line and empty lines before and after
read more
05.03.24 15:35
Anders Blume for (The only proper) PDO tutorial:
I have this code that works perfectly in Sequel Pro for Mac. SET @a = 202401; UPDATE...
read more
01.03.24 04:03
Rick Pizzi for How to run an INSERT query using Mysqli:
I'm trying to create a workable INSERT query but am having trouble with NULL as an input...
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!

02.04.24 23:57
Pablo for How to call stored procedures with mysqli:
Muchas gracias por el aporte, esta instruccion es la que me faltaba ($mysqli->next_results(); no...
read more

27.03.24 11:10
Grespin for How to run a SELECT query using Mysqli:
Its not working and im going insane
read more

26.03.24 10:45
Hello world for Articles:
to mark a block of code add four spaces before each line and empty lines before and after
read more

05.03.24 15:35
Anders Blume for (The only proper) PDO tutorial:
I have this code that works perfectly in Sequel Pro for Mac. SET @a = 202401; UPDATE...
read more

01.03.24 04:03
Rick Pizzi for How to run an INSERT query using Mysqli:
I'm trying to create a workable INSERT query but am having trouble with NULL as an input...
read more

Whitelisting helper function

Related articles:

Got a question?

SEE ALSO:

Latest comments:

Add a comment

Comments: