How do I translate this sql-injection-susceptible function to a prepared function in MySQLi?

Question

I have two functions which are sql-injection-susceptible, I have managed to translate the first one into a prepared function but I'm failing to see how I would translate the second one. This is the original first one:

    function modify($sql, &$id)
    {
        $link = database_link();

        $result = mysqli_query($link, $sql);

        $insertId = mysqli_insert_id($link);

        return mysqli_affected_rows($link);
    }

Which I translated to:

    function preparedModify($sql, $types, &$insertId, ...$value)
    {
        $statement = mysqli_prepare(database_link(), $sql);

        $statement->bind_param($types, ...$value);

        $statement->execute();

        $insertId = $statement->insert_id;

        return $statement->affected_rows;
    }

Which worked and I'm happy with. This is my second function that needs to be translated:

    function select($sql, &$rows)
    {

        $link = database_link();

        $result = mysqli_query($link, $sql);

        $rows = array();

        while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
            $rows[] = $row;
        }

        return mysqli_num_rows($result);
    }

Which is totally unclear to me how I would translate it as a beginner. Could you help me while at the same time breaking down your answer(s) to point out what the strategy behind your translation is? Because my strategy for the first function was just googling for alternative functions and that way I managed to do the first one but I reached nowhere with the second one using the same approach.

Answer 1

If you have mysqlnd installed (to allow use of mysqli_stmt::get_result), the changes can be fairly simple. The first part is the same as preparedModify, then we just use a call to get_result and the rest of the function remains the same:

function preparedSelect($sql, $types, &$rows, ...$value)
{
    $statement = mysqli_prepare(database_link(), $sql);

    $statement->bind_param($types, ...$value);

    $statement->execute();

    $result = $statement->get_result();

    $rows = array();

    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
        $rows[] = $row;
    }

    return $statement->num_rows;
}

As has been commented, you don't need the loop to generate $rows, you can simply use mysqli_result::fetch_all:

$rows = $result->fetch_all(MYSQLI_ASSOC);

Again this requires the native mysqlnd driver to be installed.