如何为动态表格行数据使用Prepared Statement提交至数据库

我有一个带动态表格行的模态框，用普通SQL查询能正常提交数据到数据库，但现在要改成Prepared Statement（预处理语句）来提交，不知道怎么修改当前代码。

原PHP处理代码 #

if (isset($_POST["save_landd_btn"])){
    for ($a = 0; $a < count($_POST["training_title"]); $a++)
    {
        $training_title = $_POST['training_title'][$a];
        $empnum = $_SESSION['empnum'];

        $query = "INSERT INTO tbl_landd (empnum, training_title, date_from, date_to, num_hours, l_n_d_type, conducted_by) 
                    VALUES ('$empnum', '" . $_POST["training_title"][$a] . "', '" . $_POST["date_from"][$a] . "', '" . $_POST["date_to"][$a] . "',
                     '" . $_POST["num_hours"][$a] . "', '" . $_POST["l_n_d_type"][$a] . "', '" . $_POST["conducted_by"][$a] . "')";
        mysqli_query($connection, $query);
    }
    $_SESSION['landd_updated']= 'The information about your training attended entitled <b>&quot;'.$training_title.'&quot;</b> has been added, successfully.';
    $_SESSION['alert-class'] = "alert-success";
    
    header ('Location: about_page2.php');
    exit(0);
}

模态框表单代码 #

<form action="updateController.php" method="POST">
    <div class="modal-body">
        <div class="table-responsive">
            <table class="table text-muted table-striped table-bordered small">
                <thead class="align-middle text-center">
                    <tr>
                        <th scope="col" rowspan="2">Title of the Learning and Development (L&D)Interventions/Training/Programs</th>
                        <th scope="col" colspan="2">Inclusive Dates<br><span class="small">(Ex: December 20, 2018)</span></th>
                        <th scope="col" rowspan="2">Number of Hours</th>
                        <th scope="col" rowspan="2">Type of LD<br>(Managerial/ Supervisory/ Technical/etc.)</th>
                        <th scope="col" rowspan="2">Conducted/Sponsored by</th>
                        <th scope="col" rowspan="2">Action</th>
                    </tr>
                    <tr>
                        <th>From</th>
                        <th>To</th>
                    </tr>
                </thead>
                <tbody id="table_body"></tbody>
            </table>
        </div>
        <button type="button" class="btn btn-sm text-bg-primary" onclick="add_Item();">
            <i class="fa fa-plus" aria-hidden="true"></i> LnD
        </button>
    </div>
    <div class="modal-footer">
        <input type="hidden" name="id" value="<?=$row['id'];?>">
        <button type="button" class="btn btn-outline-secondary" data-bs-dismiss="modal">Close</button>
        <button type="submit" class="btn btn-outline-success" name="save_landd_btn">Save changes</button>
    </div>
</form>

动态行脚本代码 #

<script>
    var newitems = 0;

    function add_Item() {
        newitems++;

        var html = "<tr>";
        html += "<td><textarea style='height: 8px' class='form-control' name='training_title[]' required></textarea></td>";
        html += "<td><input class='form-control' type='text' name='date_from[]' required></td>";
        html += "<td><input class='form-control' type='text' name='date_to[]' required></td>";
        html += "<td><input class='form-control' type='text' name='num_hours[]' required></td>";
        html += "<td><input class='form-control' type='text' name='l_n_d_type[]' required></td>";
        html += "<td><input class='form-control' type='text' name='conducted_by[]' required></td>";
        html +=
            "<td><button class='btn text-danger' type='button' onclick='delete_Row(this);'><i class='fa fa-times' aria-hidden='true'></i></button></td>"
        html += "</tr>";

        var row = document.getElementById("table_body").insertRow();
        row.innerHTML = html;
    }

    function delete_Row(button) {
        newitems--
        button.parentElement.parentElement.remove();
    }
</script>

修改后的PHP预处理语句版本 #

if (isset($_POST["save_landd_btn"])){
    $empnum = $_SESSION['empnum'];
    $last_training_title = '';

    // 1. 准备预处理语句，用?作为占位符
    $query = "INSERT INTO tbl_landd (empnum, training_title, date_from, date_to, num_hours, l_n_d_type, conducted_by) 
              VALUES (?, ?, ?, ?, ?, ?, ?)";
    $stmt = mysqli_prepare($connection, $query);

    // 2. 绑定参数：s=字符串，若num_hours是整数可改为i，根据数据库字段类型调整
    mysqli_stmt_bind_param($stmt, "sssssss", $empnum, $training_title, $date_from, $date_to, $num_hours, $l_n_d_type, $conducted_by);

    // 3. 循环处理每一行数据
    $total_rows = count($_POST["training_title"]);
    for ($a = 0; $a < $total_rows; $a++)
    {
        $training_title = $_POST['training_title'][$a];
        $date_from = $_POST['date_from'][$a];
        $date_to = $_POST['date_to'][$a];
        $num_hours = $_POST['num_hours'][$a];
        $l_n_d_type = $_POST['l_n_d_type'][$a];
        $conducted_by = $_POST['conducted_by'][$a];
        
        // 执行预处理语句
        mysqli_stmt_execute($stmt);
        
        $last_training_title = $training_title;
    }

    // 关闭预处理语句
    mysqli_stmt_close($stmt);

    // 转义特殊字符防止XSS
    $_SESSION['landd_updated']= '您参加的名为 <b>"'.htmlspecialchars($last_training_title).'"</b> 的培训信息已成功添加。';
    $_SESSION['alert-class'] = "alert-success";
    
    header ('Location: about_page2.php');
    exit(0);
}

关键说明 #

• 安全优化：用?占位符替代直接拼接变量，彻底避免SQL注入风险，mysqli_stmt_bind_param会自动处理数据转义
• 性能优化：只准备一次预处理语句，循环中重复执行，比原代码每次重新编译SQL效率更高
• 细节调整：
  • 把$empnum提到循环外，避免重复获取
  • 用htmlspecialchars转义提示信息中的标题，防止XSS攻击
  • 提前计算总行数，减少循环内的重复计算

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