What are the pitfalls of using mysqli_stmt::$error in transactions?

In PHP programming, the mysqli extension is used to interact with the MySQL database, and mysqli_stmt is an important component of it, often used to execute preprocessing statements. mysqli_stmt::$error is a property in the mysqli_stmt object that returns the error message that occurs when executing SQL statements. If SQL execution is successful, $error will be empty. If an error occurs, it will contain detailed error information to facilitate the developer to troubleshoot problems.

However, when using mysqli_stmt::$error in transactions, developers may encounter some pitfalls. This article will discuss these common problems and give solutions to how to avoid them.

Common pit 1: Transactions are not rolled back correctly

In transactions, mysqli provides methods such as begin_transaction() , commit() , and rollback() to manage database transactions. If an error occurs during transaction execution, you should make sure to roll back the transaction using rollback() to prevent data inconsistency. However, if you only rely on mysqli_stmt::$error to determine whether the transaction is successful, some errors may be missed, resulting in the transaction failing to rollback.

Solution:

Use mysqli::errno to check if an error occurs and always roll back the transaction when an error occurs. This ensures that no matter where errors occur, they can be handled in time.

 $mysqli->begin_transaction();

$stmt = $mysqli->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $email);

if (!$stmt->execute()) {
    echo "Error executing query: " . $stmt->error;
    $mysqli->rollback();  // If an error occurs，Roll back transactions
    exit();
}

// If everything is OK，Submit transactions
$mysqli->commit();

Why do this?

mysqli_stmt::$error only returns error information for a single statement execution, it does not cover the errors of the entire transaction. If you execute multiple statements and rely only on $stmt->error to check for errors, you may miss the overall error of the transaction, resulting in data inconsistency.

Common pit 2: Transaction management under different connections

mysqli allows transactions to be executed in multiple database connections. When a developer executes a transaction, he may mistakenly start a transaction on one connection and execute a query on another connection, causing transaction management chaos. When mysqli_stmt::$error is called, if an error occurs on the operation performed on different connections, the error may not be captured in time, resulting in inconsistent status of the application.

Solution:

Always ensure that all operations of the transaction are performed on the same database connection. You can use mysqli object connection to ensure that the context of the entire transaction is executed under the same connection.

 $mysqli->begin_transaction();  // Make sure all transactions are performed on the same connection

$stmt1 = $mysqli->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt1->bind_param("ss", $username, $email);

$stmt2 = $mysqli->prepare("INSERT INTO orders (user_id, amount) VALUES (?, ?)");
$stmt2->bind_param("id", $user_id, $amount);

// Perform an action
if (!$stmt1->execute() || !$stmt2->execute()) {
    echo "Error executing queries: " . $mysqli->error;
    $mysqli->rollback();
    exit();
}

$mysqli->commit();  // Make sure to submit

This ensures that all database operations are performed under the same connection, avoiding the confusion of cross-connection transaction management.

Common pit 3: Ignore database error levels

In MySQL, the error levels of transactions are divided into different types. Some errors can be fatal and require immediate rollback of transactions; while others may be warning levels that allow you to continue execution. mysqli_stmt::$error will only return SQL error messages, but will not tell you the severity of the error.

Solution:

In a transaction, first check if mysqli_stmt::$error is empty, and then take appropriate action based on the type of error. If it is a fatal error, the transaction should be rolled back; if it is a warning error, you can choose to continue.

 if (!$stmt1->execute()) {
    if ($stmt1->errno) {
        echo "Fatal error: " . $stmt1->error;
        $mysqli->rollback();  // 致命错误时Roll back transactions
        exit();
    } else {
        echo "Warning: " . $stmt1->error;
        // You can choose to continue performing other operations
    }
}

Common pit 4: Ignoring SQL injection protection

Although mysqli 's preprocessing statements have helped prevent SQL injection attacks, if developers do not use the bind_param() method correctly, the application will be exposed to the risk of SQL injection attacks. Even if you use mysqli_stmt::$error in your transaction to catch errors, if SQL injection is allowed, then these error messages may be exposed to the attacker.

Solution:

Always use bind_param() or bind_value() methods to bind parameters to ensure that the data type of the parameter is set correctly.

 $stmt = $mysqli->prepare("SELECT * FROM users WHERE username = ?");
$stmt->bind_param("s", $username);  // Bind username parameters，prevent SQL injection
$stmt->execute();

In this way, even if some errors are captured, the structure or sensitive information of the database will not be exposed to the attacker.

Summarize

When using mysqli_stmt::$error , developers need to be careful about handling errors in transactions to avoid omissions that lead to data inconsistency. The most common pitfalls include: transactions not rolling back correctly, transaction management messes under different connections, ignoring database error levels, and incorrectly preventing SQL injection. These problems can be greatly reduced through reasonable error handling, ensuring that transactions are executed under the same connection, and the reasonable use of bind_param() .