指定入住离店时段查询空闲房间的SQL语句错误排查

通过AJAX向控制器传入checkin_date（入住日期）、checkout_date（退房日期）两个参数，需要查询两个日期构成的时段内的空闲房间，现有如下查询代码，需排查其中的错误：

public function available_rooms($checkin_date,$checkout_date ){
       $arooms = DB::SELECT("SELECT * FROM tbl_rooms WHERE room_number NOT IN (SELECT room_number FROM bookings WHERE ('$checkin_date' BETWEEN checkin_date AND checkout_date) AND ('$checkin_date' BETWEEN checkin_date AND checkout_date) )"); 
}

• 参数使用错误：子查询的两个时间判断条件都传入了$checkin_date，完全没有用到$checkout_date参数，仅能判断入住日落在已有订单区间的场景，漏判绝大多数时间冲突情况。
• 时间冲突逻辑错误：即使修正参数，仅判断「用户入住、退房日期都落在已有订单的时间区间内」也无法覆盖所有冲突场景。比如已有订单为1日-5日，用户预订3日-7日，用户退房日7日不在1-5区间内，但实际房间是被占用的，原逻辑会误判为空闲；再比如用户预订1日-3日，已有订单为2日-4日，用户入住日1日不在2-4区间内，同样会被误判为空闲。
• 存在严重安全隐患：直接将PHP变量拼接进SQL语句，没有做参数转义/绑定，存在SQL注入风险，恶意构造的参数可以非法获取、篡改数据库数据。
• 边界逻辑未对齐业务：BETWEEN是闭区间判断，如果业务规则允许「前客当日退房、后客当日入住」的衔接场景，原逻辑会把这类无冲突的情况误判为房间已占用。

以下是基于Laravel框架的修正代码，使用参数绑定规避注入风险，同时使用正确的时间段重叠判断逻辑：

public function available_rooms($checkin_date, $checkout_date){
    // 可根据业务需要增加日期格式校验，确保传入参数是合法日期
    $arooms = DB::SELECT("
        SELECT * FROM tbl_rooms 
        WHERE room_number NOT IN (
            SELECT room_number FROM bookings 
            WHERE 
                -- 时间段重叠核心判断：两个时段有交集即为冲突，无交集的条件是「用户退房早于订单入住」或「用户入住晚于订单退房」，取反即为冲突
                :checkin_date < checkout_date
                AND :checkout_date > checkin_date
        )
    ", [
        ':checkin_date' => $checkin_date,
        ':checkout_date' => $checkout_date
    ]);

    return $arooms;
}

上述判断逻辑默认适配「当日退房/入住可衔接」的通用酒店业务规则，如果业务要求同一天不能安排两批客人，可将判断中的<和>调整为<=和>=即可。

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