几何MySQL查询在ASP.为函数st_geometryfromtext提供了无效的GIS数据 &g

Question

我想在ASP.NET Web API项目中使用本地查询，以防止SQL注入攻击的方式。对于这个几何查询，我必须使用MySQL函数ST_Contains，但似乎不可能使用以下方法正确绑定参数。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();

任何想法?

Answer 1

您可以在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();

或者在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();