Geometry MySQL Query in ASP.NET | Invalid GIS data provided to function st

I would like to use native query's in ASP.NET Web API project, in a way that protects against SQL injection attacks. For this Geometry query I have to use the MySQL function ST_Contains, but it seems not possible to bind parameters correctly with this following method. MySqlException: Invalid GIS data provided to function st_geometryfromtext.

List<Flight> result = await _context.Flight
            .FromSqlRaw("SELECT * FROM flight WHERE ST_Contains(GeomFromText("  
                        "'POLYGON(({0} {1}, {2} {3}, {4} {5}, {6} {7}, {8} {9}))')"  
                        ", POINT(StartLongitude, StartLatitude))", long1, lat1, long2, lat2, long3, lat3, long4, lat4, long1, lat1)
            .ToListAsync();

Any ideas?

CodePudding user response：

You can either concat it in MySQL

List<Flight> result = await _context.Flight
            .FromSqlRaw(@"
SELECT *
FROM flight
WHERE ST_Contains(GeomFromText(
    CONCAT(
      'POLYGON((',
      {0},' ',{1},', ',
      {2},' ',{3},', ',
      {4},' ',{5},', ',
      {6},' ',{7},', ',
      {8},' ',{9},'))'
    ), POINT(StartLongitude, StartLatitude))
"
               , long1, lat1, long2, lat2, long3, lat3, long4, lat4, long1, lat1)
            .ToListAsync();

Or concat or format it in C#

List<Flight> result = await _context.Flight
            .FromSqlRaw(@"
SELECT *
FROM flight
WHERE ST_Contains(GeomFromText({0}, POINT(StartLongitude, StartLatitude))
",
      $@"POLYGON(({long1} {lat1}, {long2} {lat2}, {long3} {lat3}, {long4} {lat4}, {long1} {lat1}))"
    )
 long1, lat1, long2, lat2, long3, lat3, long4, lat4, long1, lat1)
            .ToListAsync();