Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

mehranabi's avatar

Error for Valid MySQL Query!

Hello everyone,

I'm working on a project that needs to work with MySql spatial features in Laravel, I currently have a model that has a location field (POINT type in mysql) which represents its location on map (earth). In a part of app, I want to select all models that are inside a Polygon, so I'm trying this code:

Laravel Code (a part of code - some WHEREs and a ORDER_BY are added before this code and a SELECT is added after this):

// Bounds are actually generated from request inputs...
$bounds = "-71.177658505292 42.390290973957,-71.177682026887 42.390370174324,-71.17760630126 42.390382566075,-71.177582658308 42.390303365353,-71.177658505292 42.390290973957";
$properties->whereRaw("ST_Within(`location`, ST_GeomFromText('POLYGON(( ? ))', 4326))", [$bounds]);

Which produces this query:

Produced Query:

select `id`, `address`, `bedrooms`, `bathrooms`, `price`, `offer_type` from `properties` where `offer_type` = Lease and ST_Within(`location`, ST_GeomFromText('POLYGON(( -71.177658505292 42.390290973957,-71.177682026887 42.390370174324,-71.17760630126 42.390382566075,-71.177582658308 42.390303365353,-71.177658505292 42.390290973957 ))', 4326)) and `properties`.`deleted_at` is null order by `days_on_market` asc

Now, when I run this query inside MySql Workbench, it runs the query successfully with no error... BUT when I run this by Laravel (by calling ->get() appended to that piece of code in controller to get the results), I get this error as response:

Error:

{
    "message": "SQLSTATE[22023]: Invalid parameter value: 3037 Invalid GIS data provided to function st_geomfromtext. (SQL: select `id`, `address`, `bedrooms`, `bathrooms`, `price`, `offer_type` from `properties` where `offer_type` = Lease and ST_Within(`location`, ST_GeomFromText('POLYGON((-71.177658505292 42.390290973957,-71.177682026887 42.390370174324,-71.17760630126 42.390382566075,-71.177582658308 42.390303365353,-71.177658505292 42.390290973957))', 4326)) and `properties`.`deleted_at` is null order by `days_on_market` asc)",
    "exception": "Illuminate\Database\QueryException",
    "file": "C:\Users\abgha\Documents\Projects\MSS\vendor\laravel\framework\src\Illuminate\Database\Connection.php",
    "line": 669,
    "trace": [...]
}

Environment:

  • Laravel 7.0
  • PHP 7.2 and 7.4.3 (both tested)
  • MySQL 8.0

I'm not sure if it's a Laravel bug, or Database Driver or something else. so, any help is appreciated :)

0 likes
6 replies
rodrigo.pedra's avatar
Level 56

Can you test this:

// Bounds are actually generated from request inputs...
$bounds = "-71.177658505292 42.390290973957,-71.177682026887 42.390370174324,-71.17760630126 42.390382566075,-71.177582658308 42.390303365353,-71.177658505292 42.390290973957";
$properties->whereRaw("ST_Within(`location`, ST_GeomFromText('POLYGON(( {$bounds} ))', 4326))");

I guess when using binding, PDO is adding quotes around the coordinates, at least when I tried that was the reason.

If that solves your problem, you have an issue on sanitizing user input before interpolating as it could led to a SQL injection attack.

Maybe the package recommended by @sinnbeck would be a better way to go it, among other features, abstracts away sanitizing user input

mehranabi's avatar

Hello @sinnbeck , Yes, I tried this package, but it doesn't support custom SRID yet! So I have to use raw select to achieve what I wanna do.

mehranabi's avatar

Thank you @rodrigo.pedra you are totally right about PDO, The problem is fixed now :) I tried using that package, but it doesn't support SRID yet! so i have to use raw sql. I know about SQL Injection danger, Actually my code is not exactly what i posted. user sends an array of points and then I check if they are valid and then create this string $bounds and pass this to mysql, so I think I'm safe currently :) (hope so!)

1 like

Please or to participate in this conversation.