How can I dynamically build a parameterized query for RedBeanPHP 4?

Question

So I have some data coming in via POST from a form with a large number of checkboxes and I'm trying to find records in the database that match the options checked. There are four sets of checkboxes, each being sent as an array. Each set of checkboxes represents a single column in the database and the values from the checked boxes are stored as a comma-delimited string. The values I'm searching for will not necessarily be consecutive so rather than a single LIKE %value% I think I have to break it up into a series of LIKE statements joined with AND. Here's what I've got:

$query = "";
        $i = 1;
        $vals = [];
        foreach($_POST["category"] as $val){
            $query .= "category LIKE :cat".$i." AND ";
            $vals[":cat".$i] = "%".$val."%";
            $i++;
        }
        $i = 1;
        foreach($_POST["player"] as $val){
            $query .= "player LIKE :plyr".$i." AND ";
            $vals[":plyr".$i] = "%".$val."%";
            $i++;
        }
        $i = 1;
        foreach($_POST["instrument"] as $val){
            $query .= "instrument LIKE :inst".$i." AND ";
            $vals[":inst".$i] = "%".$val."%";
            $i++;
        }
        $i = 1;
        foreach($_POST["material"] as $val){
            $query .= "material LIKE :mat".$i." AND ";
            $vals[":mat".$i] = "%".$val."%";
            $i++;
        }
        $query = rtrim($query, " AND ");

        $tubas = R::convertToBeans("tuba", R::getAll("SELECT * FROM tuba WHERE ".$query, $vals));

This does seem to work in my preliminary testing but is it the best way to do it? Will it be safe from SQL injection? Thanks!

Answer 1

As long as you use parameterized queries (like you do), you should be safe from SQL injection. There are edge cases though, using UTF-7 PDO is vulnerable (I think redbean is based on PDO)

I would change the code to something like this, minimizes the foreach clutter.

$query = 'SELECT * FROM tuba';
$where = [];
$params = [];

$checkboxes = [
    'category',
    'player',
    'instrument',
    'material'
];

foreach ($checkboxes as $checkbox) {
    if (!isset($_POST[$checkbox])) {
        // no checkboxes of this type submitted, move on
        continue;
    }
    foreach ($_POST[$checkbox] as $val) {
        $where[] = $checkbox . ' LIKE ?';
        $params[] = '%' . $val . '%';
    }
}

$query .= ' WHERE ' . implode($where, ' AND ');

$tubas = R::convertToBeans('tuba', R::getAll($query, $params));