Express控制器内多SQL事务执行数据完整性问题求助

核心问题 #

• 未启用数据库事务，所有操作独立提交，没有原子性保障，删除滞后、重复插入都是中间步骤执行顺序不可控、部分失败未回滚导致
• 大量SQL操作未加await，执行顺序完全混乱，删除操作还没完成就执行下一轮查询，自然会拿到重复商品ID
• 用SELECT MAX(orderID)获取刚插入的订单ID完全不可靠，高并发下会拿到其他用户的订单ID，应该直接取INSERT操作返回的自增ID
• 存在严重SQL注入风险，所有SQL都是字符串拼接，没有用参数化查询
• 循环逐行查询、删除、插入CartPosition，效率极低，完全可以用批量操作一次完成
• 变量名大小写混乱，请求中取的是CartID，后续误用cartID会导致查询异常
• 插入OrderPosition时写死orderID为'1'，属于低级逻辑错误

修复后代码 #

exports.order = async (req, res) => {
    const { CartID } = req.body;
    // 获取独立连接用于事务
    const connection = await db.promise().getConnection();
    try {
        // 开启事务
        await connection.beginTransaction();

        // 1. 参数化查询购物车基础信息，避免注入
        const [cartInfoResult] = await connection.query(
            `SELECT Dienstleister_ohne_TVID, PatientID FROM Cart WHERE CartID = ?`,
            [CartID]
        );
        if (cartInfoResult.length === 0) throw new Error("购物车不存在");
        const cartInfo = cartInfoResult[0];

        // 2. 插入订单，直接取返回的自增ID作为OrderID
        const [orderInsertResult] = await connection.query(
            `INSERT INTO Order (Dienstleister_ohne_TVID, PatientID, created_on) VALUES (?, ?, current_timestamp())`,
            [cartInfo.Dienstleister_ohne_TVID, cartInfo.PatientID]
        );
        const orderID = orderInsertResult.insertId;

        // 3. 一次性查询当前购物车所有商品
        const [cartPositions] = await connection.query(
            `SELECT ArtikelID, OrderQuantity FROM cartposition WHERE cartID = ?`,
            [CartID]
        );
        if (cartPositions.length === 0) throw new Error("购物车为空，无法下单");

        // 4. 批量插入订单商品明细，无需循环逐行插入
        const orderPositionValues = cartPositions.map(item => [item.ArtikelID, orderID, item.OrderQuantity]);
        await connection.query(
            `INSERT INTO Orderposition (ArtikelID, orderID, quantity) VALUES ?`,
            [orderPositionValues]
        );

        // 5. 一次性删除当前购物车所有商品，无需循环逐行删除
        await connection.query(
            `DELETE FROM cartposition WHERE cartID = ?`,
            [CartID]
        );

        // 所有操作正常完成，提交事务
        await connection.commit();
        res.status(200).send("Order was sent.");
    } catch (err) {
        // 任意步骤出错，回滚所有操作
        await connection.rollback();
        console.log(err);
        res.status(400).send({ msg: "Order could not be executed." });
    } finally {
        // 无论结果如何都释放连接
        connection.release();
    }
};

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