Parse error in PostgreSQL parametrised query from C# but works in pgAdmin III

Question

I am trying to execute a parametrised query from C# and I get the error:

ERROR: XX000: parse error - invalid geometry

with the hint

"POLYGON((:m" <-- parse error at position 11 within geometry

But when I run the query in pgAdmin III replacing the parameters with their values the query works. The code is

command.CommandText = "SELECT area_code FROM area WHERE ST_INTERSECTS(ST_GeographyFromText('POLYGON((:minx :miny, :minx :maxy, :maxx :maxy, :maxx :miny, :minx :miny))'), shape) AND area_type_code = :typecode";
command.CommandType = CommandType.Text;
var typeCodeParameter = new NpgsqlParameter
{
    DbType = DbType.String,
    ParameterName = "typecode",
    Value = _typeCode
};
var minxParameter = new NpgsqlParameter
{
    DbType = DbType.Double,
    ParameterName = "minx",
    Value = _minX
};
var minyParameter = new NpgsqlParameter
{
    DbType = DbType.Double,
    ParameterName = "miny",
    Value = _minY
};
var maxxParameter = new NpgsqlParameter
{
    DbType = DbType.Double,
    ParameterName = "maxx",
    Value = _maxX
};
var maxyParameter = new NpgsqlParameter
{
    DbType = DbType.Double,
    ParameterName = "maxy",
    Value = _maxY
};
command.Parameters.Add(typeCodeParameter);
command.Parameters.Add(maxxParameter);
command.Parameters.Add(maxyParameter);
command.Parameters.Add(minxParameter);
command.Parameters.Add(minyParameter);
using (var reader = command.ExecuteReader())
    while (reader.Read())
        areas.Add((string)reader["area_code"]);

and the working query is

SELECT area_code FROM area WHERE ST_INTERSECTS(ST_GeographyFromText('POLYGON((-1.0042576967558934 50.78431084582985, -1.0042576967558934 51.199216033050647, 1.9400782407441057 51.199216033050647, 1.9400782407441057 50.78431084582985, -1.0042576967558934 50.78431084582985))'), shape) AND area_type_code = 'County'

What am I doing wrong? How should I be setting the minx, miny, maxx, maxy parameters?

Luaan · Accepted Answer · 2015-01-14 12:28:08Z

2

The problem is that you're separating the coordinates. The parameters aren't just slapped together inside the SQL (it's not just a template replace), it need to be a syntactically valid place to put a parameter. A good sanity check is to try the same query but instead of replacing the parameters with values directly, use procedural SQL and variables.

You will see that the problem is that the ST_GeographyFromText function doesn't expand parameters inside of the string it gets on input - this is an expected behaviour. If you want to use this function, you can't use parameters inside of the string - you still need to just all the values up and pass them as a string - the same thing you've done when you "removed the parameters". The simplest solution would probably be to pass the whole string as a parameter, or just add the parameters to a string in the query (e.g. ST_GeographyFromText('POLYGON((' || cast(:minx ...) || '), ' || ... || ')') etc.)

It seems like you're converting old "concatenate a bunch of strings" SQL with parametrized queries. Do persevere, but you need to take the syntax into account. Just like you can't just put a subquery in a parameter, you can't just separate a single value into two parameters like this.

So you need to either use a better type, or have some helper conversion (like a function that takes two floats, and returns the type you need).

answered Jan 14, 2015 at 12:28

Luaan

64.1k7 gold badges108 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andy Nichols Over a year ago

Thanks, this makes sense. I'd already come up with a workaround where I use string.Format to place the co-ordinate parameters into the SQL string. This should be safe from SQL injection as they are of type double so nothing nasty can get into the SQL command.

Luaan Over a year ago

@AndyNichols You actually don't need to put it in the SQL string directly - just pass the whole argument of the ST_GeographyFromText as a single string parameter. Unless the ST_GeographyFromText function does something really weird, it should be completely safe, and you're going to get execution plan reuse.

Mike T · Accepted Answer · 2015-01-15 00:06:06Z

1

The correct way to use this parametrised query is to use a function that takes numeric parameters to build an envelope, such as ST_MakeEnvelope(xmin, ymin, xmax, ymax).

SELECT area_code
FROM area
WHERE ST_Intersects(ST_MakeEnvelope(:minx, :miny, :maxx, :maxy)::geography, shape)
   AND area_type_code = :typecode;

Other attempts to format text (WKT) are slower and lossy, since the numbers are converted to text, then parsed back to numbers.

answered Jan 15, 2015 at 0:06

Mike T

44.4k18 gold badges166 silver badges213 bronze badges

Collectives™ on Stack Overflow

Parse error in PostgreSQL parametrised query from C# but works in pgAdmin III

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related