PHP form data not saving in MySQL database using mysqli

by 
# Postmortem: Failed Data Insertion in PHP/MySQL Form Handler

## Summary
Form submissions weren't persisting in MySQL despite valid database connections and table structure verification. The key failure was inadequate error handling masking underlying SQL processing failures, compounded by SQL injection vulnerabilities.

## Root Cause
- **Absence of runtime error reporting** silenced critical MySQL constraint warnings
- **SQL injection vulnerabilities** through unsanitized input caused queries to break when special characters were present
- **Implicit transaction behavior** caused queries to abort mid-execution without visible errors

## Why This Happens in Real Systems
- Production environments suppress PHP errors by default
- Unexpected user input (like apostrophes) frequently breaks primitive SQL string assembly
- Legacy codebases often lack parameterized query patterns
- DevOps gaps allow error suppression settings in dev/test environments to propagate to production

## Real-World Impact
- Silent data loss corrupted analytics and reporting
- Key customer information submissions were discarded
- Transactional inconsistencies led to CRM record incompleteness
- Business impact assessments were delayed due to hidden failures

## Example or Code

### Original Vulnerable Code
```php
<?php 
$conn = mysqli_connect("localhost","root","","testdb"); 
$name = $_POST['name']; 
$email = $_POST['email']; 
$query = "INSERT INTO users(name, email) VALUES('$name', '$email')"; 
mysqli_query($conn, $query);
?>

Fixed Version

<?php
$conn = mysqli_connect("localhost","root","","testdb");
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Sanitization function
function sanitize($input){
    return htmlspecialchars(strip_tags(trim($input)));
}

// Verify POST data availability
if(empty($_POST['name']) || empty($_POST['email'])){
    die("Name and email required");
}

$name = sanitize($_POST['name']);
$email = sanitize($_POST['email']);

try {
    $stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
    $stmt->bind_param("ss", $name, $email);
    $stmt->execute();

    if($stmt->affected_rows > 0){
        echo "Record saved successfully";
    } else {
        error_log("Insert failed: ". $conn->error);
    }
} catch(Exception $e) {
    error_log("Database error: " . $e->getMessage());
}
?>

How Senior Engineers Fix It

  1. Implement MYSQLI_REPORT_ALL flag with try/catch blocks
  2. Replace string interpolation with parameterized statements
  3. Add input validation before database operations
  4. Verify affected row counts after write operations
  5. Introduce sanitization pipelines for freeform inputs
  6. Enable persistent error logging to monitoring systems
  7. Set up database constraint alerts (e.g., foreign key violations)

Why Juniors Miss It

  • Focuses only on “happy path” implementation without failure cases
  • Overlooks difference between development and production environments
  • Assumes database connectivity validates entire workflow
  • Unfamiliar with SQL injection pathology beyond trivial examples
  • Prioritizes functional results over operational robustness
  • Lack feedback loops without logging/monitoring