使用PDO PHP实现防SQL注入的CSV导入MySQL：数据无法插入问题

Hey Zac, let's break down how to fix your CSV-to-MySQL upload form—we'll get the data inserting properly while locking it down against SQL injection at the same time.

First, the reason your data isn't inserting (even though it outputs) is almost certainly missing or flawed database insertion logic. Let's start with debugging basics, then build the secure version.

Step 1: Enable Debugging to Spot Hidden Issues #

First, add error reporting at the top of your script to catch database errors you might be missing right now:

error_reporting(E_ALL);
ini_set('display_errors', 1);

This will show you exactly why inserts fail—like mismatched column types, missing database permissions, or typos in your table/column names.

Step 2: Secure CSV Upload & Parsing #

Never trust raw user-uploaded files. We'll validate the file type and use PHP's built-in fgetcsv (way safer than splitting strings manually) to handle CSV data correctly, even if fields contain commas or quotes.

Step 3: Use Prepared Statements for SQL Injection Protection #

This is non-negotiable. Directly concatenating CSV data into SQL queries is a massive injection risk. We'll use PDO prepared statements (works with most custom DB classes, including your USER class if it uses PDO).

Here's the full, fixed code:

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

require_once("session.php");
require_once("class.user.php");

$user = new USER();
$user_id = $_SESSION['user_session'];

// Redirect unauthenticated users
if (!isset($_SESSION['user_session'])) {
    header("Location: login.php");
    exit;
}

if (isset($_POST['submit'])) {
    // Validate uploaded file
    $allowed_extensions = ['csv'];
    $uploaded_file = $_FILES['csv_file'];
    $file_ext = strtolower(pathinfo($uploaded_file['name'], PATHINFO_EXTENSION));

    if (!in_array($file_ext, $allowed_extensions)) {
        die("Error: Only CSV files are allowed!");
    }

    // Open the uploaded CSV file
    if (($csv_handle = fopen($uploaded_file['tmp_name'], "r")) !== FALSE) {
        // Skip CSV header row (remove this line if your CSV has no header)
        fgetcsv($csv_handle);

        // Get your database connection from the USER class (adjust if your class uses a different method)
        $db_connection = $user->getDB(); 

        // Prepare the INSERT statement (update table/column names to match your database)
        $insert_stmt = $db_connection->prepare("
            INSERT INTO your_target_table (column1, column2, column3, uploaded_by)
            VALUES (?, ?, ?, ?)
        ");

        // Bind parameters (optional but makes code cleaner)
        $insert_stmt->bindParam(1, $csv_col_1);
        $insert_stmt->bindParam(2, $csv_col_2);
        $insert_stmt->bindParam(3, $csv_col_3);
        $insert_stmt->bindParam(4, $user_id);

        $success_count = 0;

        // Loop through each CSV row
        while (($csv_row = fgetcsv($csv_handle, 1000, ",")) !== FALSE) {
            // Clean and assign CSV values (adjust indexes to match your CSV columns)
            $csv_col_1 = trim($csv_row[0]);
            $csv_col_2 = trim($csv_row[1]);
            $csv_col_3 = trim($csv_row[2]);

            // Execute the prepared statement
            if ($insert_stmt->execute()) {
                $success_count++;
            } else {
                // Print error details for debugging
                $error_details = $insert_stmt->errorInfo();
                echo "Failed to insert row: {$error_details[2]}<br>";
            }
        }

        fclose($csv_handle);
        echo "Upload complete! Successfully inserted $success_count rows.";
    } else {
        die("Error: Could not open the uploaded CSV file.");
    }
}
?>

<!-- Your HTML Upload Form -->
<form method="post" enctype="multipart/form-data">
    <label for="csv_file">Select CSV File:</label>
    <input type="file" id="csv_file" name="csv_file" accept=".csv" required>
    <button type="submit" name="submit">Upload & Import</button>
</form>

Key Fixes & Security Notes: #

• SQL Injection Protection: Prepared statements separate SQL logic from user data—no direct string concatenation means attackers can't inject malicious SQL.
• Robust CSV Handling: fgetcsv correctly parses fields with commas (e.g., "Smith, Jane") and skips empty rows better than manual splitting.
• Error Visibility: The error reporting and errorInfo() calls will show you exactly why an insert fails (like a duplicate primary key or mismatched data type).
• Upload Validation: We restrict file types to CSV and use accept=".csv" in the form for client-side hinting.
• Authentication Check: We added a redirect for unauthenticated users to lock down the form.

Bonus: Add Transaction Support #

If you want to ensure all rows insert successfully (or none do, if there's an error), wrap the loop in a transaction:

$db_connection->beginTransaction();

// ... your insert loop ...

if ($success_count > 0) {
    $db_connection->commit();
    echo "All rows inserted successfully!";
} else {
    $db_connection->rollback();
    echo "No rows inserted—rolled back changes.";
}

Just make sure your database table uses a storage engine that supports transactions (like InnoDB for MySQL).

内容的提问来源于stack exchange，提问作者Zac Ram