pgr_pointsAsPolygon doesn't play nice if you have string comparisons in the WHERE clause

[iboates]

I recently had some difficulty in using a ``WHERE` clause that included a string comparison in this function. The problem stems from the fact that this function immediately calls another function which uses the typical pgRouting "query as a string parameter" methodology.

Expected behavior and actual behavior

Consider this query:

SELECT
	pgr_pointsAsPolygon(
	'SELECT
		id,
		ST_X(geom)::float AS x,
		ST_Y(geom)::float AS y
	FROM
		myschema.mytable
	WHERE
		mode = ''walk'';'
	)

This crashes with a syntax error generated from the subsequent call to pgr_alphaShape(...), because it is just forwarding the pgr string, which results in this:

SELECT x, y FROM pgr_alphashape('SELECT
	id,
	ST_X(geom)::float AS x,
	ST_Y(geom)::float AS y
FROM
  	myschema.mytable
WHERE
	mode = 'walk';',
0)

Because the single quotes around walk are not escaped, it results in a mangled query and therefore a syntax error.
After fiddling around with this for a while with a colleague, we were finally able to get the desired behaviour with this query:

WITH tbl AS (SELECT '''walk''' AS walk)

SELECT
	pgr_pointsAsPolygon(
		FORMAT(
			'SELECT
				id,
				ST_X(geom)::float AS x,
				ST_Y(geom)::float AS y
			FROM
				myschema.mytable
			WHERE
				mode = ''%s'';
			',
			(SELECT walk FROM tbl)
		)
	) AS pap

It's not pretty, but it works. Basically we wrap the pgr query string in a FORMAT() call and load a string from a common table expression (which itself has to contain escaped single quotes).

Steps to reproduce the problem

Sorry that I don't have a sample dataset, but the idea is rather simple. Just try to call pgr_pointsAsPolygon with a query string which makes a string comparison in the WHERE clause.

Specifications like the version of pgRouting/PostGIS and PostgreSQL as well as Operating System

• OS: Windows 10
• PostgreSQL: 10.7
• PostGIS: 2.5
• pgRouting: 2.6.2