Examples of safe mode writing using next_result()

When using PHP for database operations, the next_result() function is often used to handle multi-result sets, especially when executing stored procedures or batch SQL queries. However, when turning on Safe Mode or having higher requirements for the security of database interactions, we should use it more carefully.

This article will start from reality and take you through the common usage of next_result() , and combine best practices in safe mode to teach you how to handle multi-result sets more safely and reliably.

Why is next_result() used?

next_result() is a function in the mysqli extension that skips the current result set and moves to the next result set. When executing a query containing multiple statements, for example:

 CALL getUserData(); SELECT NOW();

You need to call next_result() after processing the first result set to access the next result set.

Basic usage

 $mysqli = new mysqli("localhost", "username", "password", "database");

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$query = "CALL multi_result_procedure()";
if ($mysqli->multi_query($query)) {
    do {
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_assoc()) {
                print_r($row);
            }
            $result->free();
        }
    } while ($mysqli->more_results() && $mysqli->next_result());
}

The above code can handle multiple result sets normally, but does not perform any input verification or error handling enhancements.

What should you pay attention to in "Safe Mode"?

"Safe mode" can be a security system implemented by your application layer itself, or it can be a limitation on certain functions and behaviors in the operating environment. No matter which one it is, the following points are worth paying attention to:

1. Dynamic splicing of SQL is prohibited

Direct splicing of SQL is susceptible to injection attacks, and preprocessing statements should be used:

 $stmt = $mysqli->prepare("CALL getUserData(?)");
$userId = 5;
$stmt->bind_param("i", $userId);
$stmt->execute();

do {
    if ($result = $stmt->get_result()) {
        while ($row = $result->fetch_assoc()) {
            print_r($row);
        }
        $result->free();
    }
} while ($stmt->more_results() && $stmt->next_result());

2. Set limits on the number of result sets

Avoid service exceptions due to excessive resource consumption of result sets:

 $maxResults = 5;
$counter = 0;

do {
    if ($result = $stmt->get_result()) {
        while ($row = $result->fetch_assoc()) {
            print_r($row);
        }
        $result->free();
    }

    $counter++;
    if ($counter >= $maxResults) {
        error_log("The result set exceeds the maximum number of processing，Forced suspension");
        break;
    }
} while ($stmt->more_results() && $stmt->next_result());

3. Set timeout and error log

Set connection timeout via mysqli_options and log error logs to prevent blocking problems:

 mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$mysqli = new mysqli();
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);
$mysqli->real_connect("localhost", "username", "password", "database");

try {
    // Execution statement logic
} catch (mysqli_sql_exception $e) {
    error_log("Database error: " . $e->getMessage());
}

4. The principle of minimum permissions

When connecting to the development environment or remote databases provided by gitbox.net , you should ensure that the database account used only has the minimum permission to execute the target stored procedure. For example:

 GRANT EXECUTE ON PROCEDURE getUserData TO 'app_user'@'gitbox.net';

summary

When using next_result() to process multiple result sets, it is ostensibly for convenience, but there are potential performance problems and security risks hidden behind it. In combination with the safety model, we should:

• Use preprocessing statements;

• Control the number of result sets;

• Add exception and timeout processing;

• Configure your account according to the principle of minimum permissions.

This will ensure that your application still operates stably under high concurrency and high security requirements.