使用PHP向列数可变的MySQL表中插入数据的实现方案咨询

Hey there! Let's walk through how to safely and effectively insert data into those variable-column tables you've built. First off, I need to flag a critical issue in your current code: using $_GET['list'] directly in your query is a massive SQL injection risk. We'll fix that first, then handle the dynamic insertion logic.

Step 1: Securely Validate the Table Name #

Since prepared statements don't support placeholders for table names, we need to verify that the incoming list_name is a valid, user-created table. The safest way is to maintain a whitelist of allowed tables (either from a dedicated tracking table or by querying the database schema):

require 'con.php';

// Fetch all valid user-created lists (adjust this to match your setup)
// Option 1: Use a tracking table (recommended for better control)
$safe_list_names = [];
$get_lists_query = "SELECT list_name FROM user_created_lists";
$lists_result = mysqli_query($conn, $get_lists_query);
while ($row = mysqli_fetch_assoc($lists_result)) {
    $safe_list_names[] = $row['list_name'];
}

// Option 2: Query the database schema if you don't have a tracking table
// $get_lists_query = "SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE()";
// $lists_result = mysqli_query($conn, $get_lists_query);
// while ($row = mysqli_fetch_assoc($lists_result)) {
//     $safe_list_names[] = $row['table_name'];
// }

// Validate the incoming list name
$list_name = $_GET['list'] ?? '';
if (!in_array($list_name, $safe_list_names)) {
    die("Invalid list name. Please select a valid list.");
}

Step 2: Fetch Columns (Exclude Auto-Increment Fields) #

Next, get the table's columns, but skip any auto-increment primary keys (like id) since those are populated automatically by MySQL:

$fields = [];
$columns_query = "SHOW COLUMNS FROM `$list_name`"; // Use backticks to escape table names with special chars
$columns_result = mysqli_query($conn, $columns_query);

while ($row = mysqli_fetch_assoc($columns_result)) {
    // Skip auto-increment columns (no need for user input)
    if ($row['Extra'] !== 'auto_increment') {
        $fields[] = $row['Field'];
    }
}

Step 3: Handle Form Submission & Dynamic Insertion #

Now we'll build a dynamic INSERT statement using prepared statements to avoid SQL injection for the data values. We'll also ensure we only process fields that exist in the table:

// Process form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Collect only valid field data from the form
    $form_data = [];
    foreach ($fields as $field) {
        // Use null if the field isn't submitted (adjust based on your table's nullability rules)
        $form_data[$field] = $_POST[$field] ?? null;
    }

    // Build the INSERT query dynamically
    $columns = implode('`, `', $fields);
    $placeholders = implode(', ', array_fill(0, count($fields), '?'));
    $insert_query = "INSERT INTO `$list_name` (`$columns`) VALUES ($placeholders)";

    // Prepare and execute the statement
    $stmt = mysqli_prepare($conn, $insert_query);
    if (!$stmt) {
        die("Query preparation failed: " . mysqli_error($conn));
    }

    // Bind parameters: Use 's' for strings (MySQL auto-converts types like int/date if needed)
    // If you need type specificity, map column types to mysqli types (i=int, d=double, s=string)
    $param_types = str_repeat('s', count($fields));
    mysqli_stmt_bind_param($stmt, $param_types, ...array_values($form_data));

    if (mysqli_stmt_execute($stmt)) {
        echo "Data saved successfully!";
    } else {
        echo "Error saving data: " . mysqli_stmt_error($stmt);
    }

    // Clean up
    mysqli_stmt_close($stmt);
}

Step 4: Improve the Dynamic Form (Optional but Useful) #

Make sure your generated form uses input names that match the column names exactly. You can also adjust input types based on the column's data type (e.g., date inputs for DATE columns):

<!-- Dynamic Form -->
<form method="POST">
    <?php foreach ($fields as $field): ?>
        <div class="form-group">
            <label for="<?php echo htmlspecialchars($field); ?>">
                <?php echo htmlspecialchars(ucfirst($field)); ?>:
            </label>
            <?php
            // Optional: Adjust input type based on column data type
            $column_type = mysqli_fetch_assoc(mysqli_query($conn, "SHOW COLUMNS FROM `$list_name` WHERE Field = '$field'"))['Type'];
            $input_type = 'text';
            if (str_contains($column_type, 'int')) $input_type = 'number';
            if (str_contains($column_type, 'date')) $input_type = 'date';
            ?>
            <input 
                type="<?php echo $input_type; ?>"
                id="<?php echo htmlspecialchars($field); ?>"
                name="<?php echo htmlspecialchars($field); ?>"
                required
            >
        </div>
    <?php endforeach; ?>
    <button type="submit">Add Entry</button>
</form>

Key Notes to Remember #

• Security First: Never skip table name validation, and always use prepared statements for data values to prevent SQL injection.
• Column Type Handling: If you need strict type enforcement, map the column's Type value to the appropriate mysqli parameter type (i, d, s, b).
• Error Handling: In production, replace die() with proper error logging and user-friendly messages instead of exposing raw database errors.
• Nullability: Adjust how you handle missing form fields based on whether your table columns allow NULL values.

内容的提问来源于stack exchange，提问作者Ankur pandey