Verifying Success in MySQLi Update Queries
When implementing a password reset flow, the logic for updating the database often feels straightforward: match the email and reset code, then overwrite the password hash. However, detecting whether that update actually persisted is where many implementations stumble. The snippet you are working with uses mysqli_query to execute the statement, followed by a check on mysqli_affected_rows. While this pattern works for basic scenarios, relying solely on it introduces fragility that can lead to poor user experiences or security vulnerabilities if not handled with precision.
The core issue lies in how MySQLi distinguishes between a successful update and a query that simply failed due to a syntax error or a constraint violation. When you run mysqli_query, the function returns a boolean indicating whether the execution was successful. If the SQL string is invalid, the query fails immediately, and affected_rows might return 0 or -1 depending on the specific version of MySQL and the exact nature of the failure.
In your current code, if the SQL syntax is correct but no rows match the WHERE clause (meaning the email or reset code was incorrect), the update executes without errors, but zero rows are modified. Your check for == 1 correctly identifies this specific case. However, there is a critical flaw in the error handling logic:
$update = mysqli_query($mysqli, $uinsert) or die(mysqli_error($mysqli));
If mysqli_query returns false because of a syntax error (e.g., missing quotes in the SQL string), the script dies immediately. This prevents you from reaching the conditional check on affected_rows. While stopping execution is sometimes acceptable, it provides no context to the user beyond a generic database error message. A more robust approach separates the query execution from the result validation.
Consider refactoring the logic to explicitly handle the boolean return of mysqli_query:
$result = mysqli_query($mysqli, $uinsert);
if ($result === false) {
// Handle SQL syntax errors or server issues
die('Database error: ' . mysqli_error($mysqli));
}
if (mysqli_affected_rows($mysqli) > 0) {
header("Location: ../index.php");
exit;
} else {
echo "<script>alert('Incorrect code, try again!');</script>";
}
This separation ensures that if the SQL itself is malformed, you can diagnose the specific error via mysqli_error rather than masking it or crashing unexpectedly. It also clarifies the flow: first ensure the query ran, then check if it changed any data.
The Limitation of Affected Rows
It is important to understand what mysqli_affected_rows actually measures. In an UPDATE statement, this function returns the number of rows that were actually modified. If your WHERE clause fails to match any records in the member table, the function returns 0. This is exactly the behavior you want to detect for invalid reset codes.
However, there is a significant pitfall when dealing with unique constraints or foreign keys. If the update statement attempts to write a duplicate value into a column marked as UNIQUE, MySQL will reject the change and set an error flag. In this scenario, mysqli_query returns false, and affected_rows might not reflect the intended business logic (setting the password) because the operation was aborted at the database engine level due to the constraint violation.
In the context of a password reset, you typically want to allow overwriting the existing password hash without checking for uniqueness unless that column is strictly enforced as such. If your password column does not have a unique constraint, the only way the update fails logically is if no matching row exists.
Modern Alternatives and Best Practices
While mysqli_affected_rows is the standard tool for this task in procedural PHP, using prepared statements is non-negotiable for any production system handling user credentials. Your current SQL string interpolates variables directly:
$uinsert = "UPDATE member SET password = '$password' WHERE emailadd = '$emailadd' AND resetCode = '$resetcode'";
This approach is vulnerable to SQL injection if the input data contains malicious characters. Furthermore, direct interpolation can sometimes cause issues with special characters in strings or numbers (e.g., an ID starting with 0).
Switching to prepared statements changes how you validate success:
$stmt = $mysqli->prepare("UPDATE member SET password = ? WHERE emailadd = ? AND resetCode = ?");
if (!$stmt) {
die('Prepare failed: ' . $mysqli->error);
}
$result = $stmt->bind_param("sss", $password, $emailadd, $resetcode); // Assuming strings
$result = $stmt->execute();
if ($result && $stmt->affected_rows > 0) {
header("Location: ../index.php");
exit;
} else {
echo "<script>alert('Incorrect code, try again!');</script>";
}
Using bind_param handles the escaping automatically and ensures that only valid data reaches the database. The execute() method returns a boolean, and the affected_rows property on the statement object provides the count of modified rows. This pattern is cleaner, safer, and easier to maintain than manual string concatenation.
If you are working within the Laravel ecosystem, similar logic exists but is encapsulated by Eloquent or Query Builder methods that handle these checks internally, often returning a boolean true or false directly from the update call depending on whether rows were affected. For pure PHP projects, however, explicit checking of affected_rows remains the definitive way to confirm an update operation succeeded.
Final Recommendation
To summarize the fix: always separate query execution from result verification. Check the boolean return of mysqli_query or $stmt->execute() first to catch syntax errors and constraint violations. Then, inspect affected_rows to distinguish between "query failed" and "no rows matched." This two-step validation ensures your application responds correctly whether the user entered the wrong code or if a database error occurred.