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();

returns Laravel - SQLSTATE[HY000]: General error: 2031 However this query below works

$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();

but it is vulnerable to SQLInjection. I've done the suggestions mentioned in stackoverflow. PDO error: General error: 2031 [duplicate]

It probably is possible, you've got a rather specific query, and I don't have a Laravel instance to play with right now. But query builder uses PDO parameter binding to protect against SQL injection. If you need to set specific bindings you can use `setBindings()` method to not create injection points. — LegenJerry, Jul 25 '18 at 12:38

score 0 · Accepted Answer · answered Jul 25 '18 at 12:02

0

selectRaw and whereRaw allow you to use placeholders and pass an array of values as the second argument:

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

answered Jul 25 '18 at 12:02

Devon Bessemer

34,461
9
69
95

It returned `strtolower() expects parameter 1 to be string, array given` – kodfire Jul 25 '18 at 12:11
On the selectRaw? Are either of the values of $data['lng'] and $data['lat'] arrays? I'm not sure why strtolower would even be called here. The raw methods are documented here: https://laravel.com/docs/5.6/queries#raw-expressions – Devon Bessemer Jul 25 '18 at 13:11
yes i'm sure about that. I have `dd` it was an string. – kodfire Jul 26 '18 at 15:14
I just don't see a strtolower error being caused by selectRaw, it seems unrelated. If you think it's related, post an update with a backtrace in your question. – Devon Bessemer Jul 26 '18 at 15:18

Laravel - SQLSTATE[HY000]: General error: 2031

1 Answers1