mysql - Laravel-SQLSTATE[HY000] : General error: 2031

Question

$pois = Home::select(\DB::raw('*, st_distance_sphere(homes.geopoint, point(?, ?)) as dist'))
    ->whereRaw('st_within(homes.geopoint, ST_Buffer(point(?, ?), 1))')
    ->orderBy('dist')
    ->get();

返回 Laravel - SQLSTATE[HY000]: General error: 2031 但是下面的查询有效

$pois = Home::selectRaw('*, st_distance_sphere(homes.geopoint, point('.$data["lng"].', '.$data["lat"].')) as dist')
    ->whereRaw('st_within(homes.geopoint, ST_Buffer(point('.$data['lng'].', '.$data['lat'].'), 1))')
    ->orderBy('dist')
    ->get();

但它很容易受到 SQLInjection 的攻击。我已经完成了 stackoverflow 中提到的建议。 PDO error: General error: 2031 [duplicate]

Answer 1

selectRaw 和 whereRaw 允许您使用占位符并将值数组作为第二个参数传递:

selectRaw('*, st_distance_sphere(homes.geopoint, point(?, ?)) as dist', [$data['lng'], $data['lat']])