CODEWITHSUNDEEP
c#mysqlasp.netgispoint
CR7
CR7

Reputation: 99

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

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?

Upvotes: 0

Views: 258

Answers (1)

Charlieface
Charlieface

Reputation: 72268

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

Upvotes: 1

Related Questions