Convert array into a SQL query securely (PHP7+PDO+SQLite)

Using PHP7+PDO+SQLite, I am looking for a way to filter the entries in a table using a user-defined filter in the form of an array generated on the frontend. For clarity, here is an example of what I'm looking for:

The Example:

Table in the database:

 ID             Firstname      Lastname       Age
+--------------+--------------+--------------+-------------+
| 0            | John         | Doe          | 21          |
| 1            | John         | Smith        | 35          |
| 2            | Alice        | Smith        | 35          |
| 3            | Bob          | Smith        | 40          |
+--------------+--------------+--------------+-------------+

Filters:

/*
Filters follow the structure:
[
    "Table Column Name 1" => [Match1 OR Match2 OR ...],
    AND
    "TCN 2" ...
]
Any column name not provided should match any value.
*/

[
    "Firstname" => ["John"]
]
// ^ fetchAll() returns rows with ID 0 and 1

[
    "Firstname" => ["John", "Alice"],
    "Lastname" => ["Smith"]
]
// ^ ID 1 and 2 (but not 3)

I need to find a function which can convert the arrays above into a SQL query to fetch all rows which match the filter. I would like to do this using SQL (which I don't have much experience with) instead of fetching all rows and filtering them with PHP for performance reasons.

I know that I can do something along the lines of:

// Untested - for illustration purposes only

$filter = [
    "Firstname" => ["John", "Alice"],
    "Lastname" => ["Smith"]
];

$sql = "SELECT * FROM table_name WHERE ";

foreach ($filter as $column => $matching_values) {

    foreach ($matching_values as $match) {

        $sql .= $column . " == " . $match . " OR ";

    }

    // Ugly way of removing trailing ` OR `
    $sql = substr($sql, 0, -4);

    $sql .= " AND ";

}
// Ugly way of removing trailing ` AND `
$sql = substr($sql, 0, -5);

echo $sql;

But this introduces a massive SQL injection security flaw. I want to know if there is a simple yet efficient and secure way to achieve this. The format of the filters can also be changed if required, for example to RegEx (bonus points if someone can replace the innermost arrays in the examples above with RegEx).

Alternatively (or additionally), a simple way to describe my problem in a searchable way might be helpful since I couldn't do much research about this because I couldn't word it well.

Solution

That will be an interesting combination of several techniques I am teaching on my site already, namely

creating WHERE clause dynamically
creating a prepared statement for IN clause
whitelisting to protect from SQL injection

In the end it will be something like this

$allowed = ["Firstname", "Lastname"];

$conditions = [];
$parameters = [];
foreach ($filter as $key => $values) {
    if (!in_array($key, $allowed, true)) { 
        throw new InvalidArgumentException("invalid field name!"); 
    }
    $conditions[] = sprintf(
        "`%s` IN (%s)",
        $key,
        str_repeat('?,', count($values) - 1) . '?'
    );
    array_push($parameters, ...$values);
}
$sql = "SELECT * FROM table_name";
if ($conditions)
{
    $sql .= " WHERE " . implode(" AND ", $conditions);
}

will produce a query you are looking for,

SELECT * FROM table_name WHERE `Firstname` IN (?,?) AND `Lastname` IN (?)

as well as an array of values to be used in PDO::execute(), making a bullet proof solution where values are protected via parameters and filed names protected from via filtering against a white list

My only doubt is that the case present in the question could be oversimplified. As soon as you will need not only exact matching, but partial matching or greater/less comparison the complexity of the code will skyrocket.