Mapserver postgis driver does not quote field names with special characters

[szekerest]

Mapserver postgis driver does not quote field names with special characters, 
(eg with code<=32 or code >=128), that causes syntax error on the server.

Field with special characters must be placed between double quotes when 
constructing the SQL query string.

To fix this issue the following code fragments in mappostgis.c 

for(t = 0; t < layer->numitems; t++) {
            length += strlen(layer->items[t]) + 7;
        }
        columns_wanted = (char *) malloc(length + 1);
        *columns_wanted = 0;
        for(t = 0; t < layer->numitems; t++) {
            strcat(columns_wanted, layer->items[t]);
            strcat(columns_wanted, "::text,");
        }

might be replaced with

for(t = 0; t < layer->numitems; t++) {
			if (must_quote(layer->items[t]))
				length += strlen(layer->items[t]) + 9;
			else
				length += strlen(layer->items[t]) + 7;
        }
        columns_wanted = (char *) malloc(length + 1);
        *columns_wanted = 0;
        for(t = 0; t < layer->numitems; t++) {
			if (must_quote(layer->items[t])) {
				strcat(columns_wanted, "\"");
				strcat(columns_wanted, layer->items[t]);
				strcat(columns_wanted, "\"");
			}
			else
				strcat(columns_wanted, layer->items[t]);
            strcat(columns_wanted, "::text,");
        }

and the following function might be added:

static int must_quote(char* field_name)
{
	int i;
	int len = strlen(field_name);
	for (i = 0; i < len; i++)
	{
		if (field_name[i] <= 32 || field_name[i] >= 128)
			return 1;
	}
	return 0;
}

Tamas

[jerry.pisk@…]

A better solution would be to simply quote all object names (tables, columns 
and so on), that way you do not need to worry about any other cases you missed 
when checking whether you need to quote or not.

[szekerest]

Does this problem not to be sneezed at?

Tamas

[mark@…]

I've taken the middle road for this fix.  Column names being requested are
quoted regardless of special character content, but table names, unique columns
or geometry columns are not.  Quoting these changes the behaviour with respect
to case sensitivity and geometry column identification.

[jerry.pisk@…]

Mark, your fix only introduced more problems - expressions are now being 
quoted as well, which is incorrect. If one of the requested "columns", for 
example a CLASSITEM, is an expression instead of a column name, it will be 
incorrectly quoted in the generated SQL.

I have been trying to address this issue earlier and it came down to keeping 
track which of the "column" names came from the map file (for those quoting is 
the user's responsiblity) and which were deducted from the query results 
(those must be quoted by the PostGIS layer code).

[jerry.pisk@…]

*** Bug 1622 has been marked as a duplicate of this bug. ***

[martin]

Replying to jerry.pisk@…:

Mark, your fix only introduced more problems - expressions are now being quoted as well, which is incorrect. If one of the requested "columns", for example a CLASSITEM, is an expression instead of a column name, it will be incorrectly quoted in the generated SQL. [...]

Adding to that, think of applying a filter to fulfill the following task:

SELECT the_geom FROM a, b WHERE a.ref = b.id AND b.ref = 0;

.... where 'a.id' is Primary Key, thus "USING UNIQUE a.id" is required in the "DATA" declaration. In consequence, MapServer is going to double-quote "a.id" in the PG query - resulting in an Error, since the column "a.id" does not exist.

[tbonfort]

clsoing now we can manually escape quotes inside the mapserver data string.