Handsontable删除行同步MySQL数据库问题排查

问题背景 #

用Handsontable展示MySQL数据库数据时，期望点击删除行同步调用delete_com.php删除对应数据库行。最初遇到索引错误（删除ID5的行却传递ID6），已通过beforeRemoveRow钩子解决索引问题，但数据库仍未执行删除操作，控制台显示“Rows deleted succesfullyArray”却无实际效果。

关键代码回顾 #

已修复索引问题的前端代码 #

function deleteRowsFromDatabase(rowToDelete) {
    var idToDelete = rowToDelete[0];
    $.ajax({
        type: 'POST',
        url: 'delete_com.php',
        data: { ids: [idToDelete] }, 
        success: function(response) {
            console.log('Rows deleted successfully:', response);

            if (response.length > 0 && response[0].ID) {
                var deletedID = response[0].ID;
                var rowIndex = hot.getData().findIndex(function(row) {
                    return row[0] === deletedID;
                });

                if (rowIndex !== -1) {
                    hot.alter('remove_row', rowIndex);
                }
            }
        },
        error: function(xhr, status, error) {
            console.error('Error deleting rows:', error);
        }
    });

    var rowIndex = hot.getSourceData().indexOf(rowToDelete);
    if (rowIndex !== -1) {
        hot.alter('remove_row', rowIndex);
    }
}

hot.addHook('beforeRemoveRow', function (index, amount) {
    var removedRows = loadedData.slice(index, index + amount);
    console.log('amount', amount, 'index', index);

    hot.addHookOnce('afterRemoveRow', function () {
        deleteRowsFromDatabase(removedRows);
    });
});

后端PHP代码 #

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_POST['ids'])) {
        $ids = $_POST['ids'];
        
        $idList = implode("','", $ids);
        $stmt = $conn->prepare("DELETE FROM com WHERE ID IN ('$idList') AND company = ?");
        $stmt->bind_param("i", $compid);
        $stmt->execute();
        $stmt->close();

        echo "Rows deleted successfully", $idList;
    } else {
        echo "No IDs sent";
    }
}

问题排查与解决方案 #

1. SQL预处理语句参数化错误 #

当前PHP代码直接将拼接的$idList插入预处理语句，破坏了预处理的安全性，且如果ID是数字类型，单引号会导致类型不匹配，进而无法匹配到数据库行。同时这种写法存在SQL注入风险。

修正后的PHP代码：

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // 校验$compid有效性
    if (!isset($compid) || !is_numeric($compid)) {
        echo "Invalid company ID";
        exit;
    }

    if (isset($_POST['ids']) && is_array($_POST['ids'])) {
        // 强制转换为整数，防止注入
        $ids = array_map('intval', $_POST['ids']);
        // 为每个ID创建占位符
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        
        $stmt = $conn->prepare("DELETE FROM com WHERE ID IN ($placeholders) AND company = ?");
        
        // 绑定参数：ID列表+company参数
        $types = str_repeat('i', count($ids)) . 'i';
        $params = array_merge($ids, [$compid]);
        
        call_user_func_array([$stmt, 'bind_param'], array_merge([$types], $params));
        
        $stmt->execute();
        // 返回受影响行数，方便前端确认删除结果
        echo "Rows deleted successfully. Affected rows: " . $stmt->affected_rows;
        $stmt->close();
    } else {
        echo "No valid IDs sent";
    }
}

2. 前端冗余行删除操作 #

前端deleteRowsFromDatabase函数中，Ajax请求外的hot.alter('remove_row', rowIndex)会导致行被提前删除，且和afterRemoveRow钩子的操作重复。应移除该冗余代码，保证前后端操作同步：

修正后的前端函数：

function deleteRowsFromDatabase(rowToDelete) {
    var idToDelete = rowToDelete[0];
    $.ajax({
        type: 'POST',
        url: 'delete_com.php',
        data: { ids: [idToDelete] }, 
        success: function(response) {
            console.log('Rows deleted successfully:', response);
            // 行已通过钩子删除，此处仅做结果校验或提示即可
        },
        error: function(xhr, status, error) {
            console.error('Error deleting rows:', error);
            // 数据库删除失败时恢复前端行
            var rowIndex = loadedData.indexOf(rowToDelete);
            if (rowIndex !== -1) {
                hot.alter('insert_row', rowIndex, 1);
                hot.setDataAtRow(rowIndex, rowToDelete);
            }
            alert('删除失败，请重试');
        }
    });
}

3. 关键变量校验 #

确保后端$compid变量已正确赋值，否则AND company = ?条件会导致无匹配行，删除操作无效果。

总结 #

1. 修复SQL预处理语句的参数化逻辑，避免类型不匹配和注入风险
2. 移除前端冗余的行删除操作，保证前后端操作一致性
3. 校验后端关键变量$compid的有效性

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