Pragma, not Dogma

It's easy enough to create a prepared statement where one has a number of discrete pieces of information. It's not always obvious how one might do this with an array posted from an HTML form. This article will show you how to do it safely.

The requirement

Imagine that you have an HTML form with a number of rows and checkboxes, and your server is to produce some output based on the selected rows.

<form action="processRows.php" method="post">
    <table>
        <tbody>
        <tr><td>Row One</td><td><input type="checkbox" value="1" name="selected[]"></td></tr>
        <tr><td>Row Two</td><td><input type="checkbox" value="2" name="selected[]"></td></tr>
        <tr><td>Row Three</td><td><input type="checkbox" value="3" name="selected[]"></td></tr>
        <tr><td>Row Four</td><td><input type="checkbox" value="4" name="selected[]"></td></tr>
        </tbody>
    </table>
    <input type="submit" name="submit">
</form>

When the form is submitted to your PHP program, your code will see an array in $_POST['selected'] that contains all the values of the checked checkboxes.

Your code has to retrieve all the rows in the table that match the values in the selected[] array. You could just loop through the array and retrieve the rows one at a time but that's horribly inefficient. What you need is a query like SELECT * FROM myTable WHERE `IDX` in ('1','2','4'), but how to do that safely?

The contents of $selected[] are user data and cannot be trusted. The best* way to handle user data is to use a prepared statement, but we don't know how many values we might have to process. We need a dynamic way to produce a statement that we can prepare.

Creating the query

The basic form of statement we need is

SELECT * FROM myTable WHERE `IDX` in (?,?,?)

We know how many values we have to process from the size of $_POST['selected'] so we can use that to create a list of placeholders:

// Create a string of the form ?,?,?,?,
$placeholders = str_repeat('?,', count($_POST['selected']);
// strip the trailing comma, and concatenate the parts of the query
$placeholders = trim($placeholders,',');
$query = "SELECT * FROM myTable WHERE `IDX` in (".$placeholders.).")";
// Now prepare the statement. Note that we have not included any user data in the query we're preparing
$stmt = $conn->prepare($query); 

Binding the data

For PDO, binding the data is very easy. We could use bindParam(), but there's an easier way: supply the $_POST['selected'] array as a parameter to the execute() method: $stmt->execute($_POST['selected']);

For MySQLi we need to do a little more. We can bind the entire array in one operation, but we need to create the types string and expand the array to match:

$types = str_repeat('s', count($_POST['selected']));  // All $_POST values are strings.
$stmt->bind_param($types, ...$_POST['selected']); // Use the ... spread operator too unpack the array

From this point on, retrieve the data in the usual way.

Complete PDO code

    $pdo = new PDO('mysql:dbname=schemaName;host=localhost', 'user', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $paramList = str_repeat("?,", count($_POST['selected']));
    $query = "select * from myTable where idx in (".trim($paramList, ',').")";
    $stmt = $pdo->prepare($query);
    $stmt->execute($_POST['selected']);
    while ($row = $stmt->fetch()) {
        echo $row['result'];
        echo "<br>\n";
    }

Complete MySQLI code

    $mysqli = new mysqli('localhost','user','password','schema');
    $paramList = str_repeat("?,", count($_POST['selected']));
    $query = "select result from myTable where idx in (".trim($paramList, ',').")";
    $stmt = $mysqli->prepare($query);
    $types = str_repeat('s', count($_POST['selected']));  // All $_POST values are strings.
    $stmt->bind_param($types, ...$_POST['selected']); // Use the ... spread operator to unpack the array
    $stmt->execute();
    $stmt->bind_result($resultData);
    while ($row = $stmt->fetch()) {
        echo $resultData;
        echo "<br>\n";
    }

This is the data used for the table myTable

id	idx	result
1	1	Result 1
2	2	Result 2
3	3	Result 3
4	4	Result 4
5	5	Result 5

* What's 'best' can vary. In this case, where all the values are expected to be integers, using a filter can guarantee safety without needing a prepared statement. That's a subject for a different article.

Note: This code was written and tested using PHP8. It should work for some earlier versions of PHP.