Opened 8 years ago

Closed 5 years ago

#6567 closed defect (wontfix)

PostGIS raster needs to quote schema, table and column names

Reported by: hrz Owned by: warmerdam
Priority: normal Milestone: closed_because_of_github_migration
Component: GDAL_Raster Version: svn-trunk
Severity: normal Keywords:
Cc:

Description

Schema, table and column names are not quoted by the PostGIS raster driver. This leads to malformed SQL in some cases. E.g.

gdalinfo 'PG:dbname=mydb host=myhost user=myuser password=mypass port=5432 sslmode=require mode=2 schema=raw column=rast table=100m_topography'
Warning 1: Cannot find information about raw.100m_topography table in raster_columns view. The raster table load would take a lot of time. Please, execute AddRasterConstraints PostGIS function to register this table as raster table in raster_columns view. This will save a lot of time.
ERROR 1: Error browsing database for PostGIS Raster properties : ERROR:  syntax error at or near ".100"
LINE 1: ...t)) scale_x, avg(ST_ScaleY(rast)) scale_y from raw.100m_topo...
                                                             ^

gdalinfo failed - unable to open 'PG:dbname=mydb host=myhost user=myuser password=mypass port=5432 sslmode=require mode=2 schema=raw column=rast table=100m_topography'.

The bad SQL generated from this command (extracted from the database logs) is:

select srid, nbband, st_xmin(geom) as xmin, st_xmax(geom) as xmax, st_ymin(geom) as ymin, st_ymax(geom) as ymax, scale_x, scale_y from (select st_srid(rast) srid, st_extent(rast::geometry) geom, max(ST_NumBands(rast)) nbband, avg(ST_ScaleX(rast)) scale_x, avg(ST_ScaleY(rast)) scale_y from raw.100m_topography group by st_srid(rast)) foo

Quoting the object names as follows generates valid SQL:

select srid, nbband, st_xmin(geom) as xmin, st_xmax(geom) as xmax, st_ymin(geom) as ymin, st_ymax(geom) as ymax, scale_x, scale_y from (select st_srid("rast") srid, st_extent("rast"::geometry) geom, max(ST_NumBands("rast")) nbband, avg(ST_ScaleX("rast")) scale_x, avg(ST_ScaleY("rast")) scale_y from "raw"."100m_topography" group by st_srid("rast")) foo

In this case the SQL failed because the table name was invalid, but it makes sense to quote all three classes of identifier as illustrated.

Change History (4)

comment:1 by hrz, 8 years ago

Version: unspecifiedsvn-trunk

comment:2 by Jukka Rahkonen, 8 years ago

I wonder how to define when to quote and when not. Quoting everything would be explicit but could it have some side effects with for example case sensitivity?

select "srid", "nbband", st_xmin("geom") as "xmin", st_xmax("geom") as "xmax", st_ymin("geom") as "ymin", st_ymax("geom") as "ymax", "scale_x", "scale_y" from (select st_srid("rast") "srid", st_extent("rast"::geometry) "geom", max(ST_NumBands("rast")) "nbband", avg(ST_ScaleX("rast")) "scale_x", avg(ST_ScaleY("rast")) "scale_y" from "raw"."100m_topography" group by st_srid("rast")) AS "foo";

comment:3 by hrz, 8 years ago

Yes, quoting identifiers will also preserve case, which would seem to be a benefit when dealing with user input as long as the fact is documented. Quoting could have unintentional side effects when applied to all identifiers which may be a reason to limit it just to user input. Having said that, the current implementation assumes all identifiers will be lower case: if any were upper case then the SQL would fail, so your example of quoting all identifiers shouldn't cause any more problems than the current implementation.

comment:4 by Even Rouault, 5 years ago

Milestone: closed_because_of_github_migration
Resolution: wontfix
Status: newclosed

This ticket has been automatically closed because Trac is no longer used for GDAL bug tracking, since the project has migrated to GitHub. If you believe this ticket is still valid, you may file it to https://github.com/OSGeo/gdal/issues if it is not already reported there.

Note: See TracTickets for help on using tickets.