Bug report #19583

QGIS PostGIS Raster Driver BUG (example) with suggested fix (ALL VERSIONS OF QGIS)

Added by al piszcz over 6 years ago. Updated over 6 years ago.

Status:Closed
Priority:High
Assignee:-
Category:Data Provider/PostGIS
Affected QGIS version:3.3(master) Regression?:No
Operating System: Easy fix?:Yes
Pull Request or Patch supplied:No Resolution:up/downstream
Crashes QGIS or corrupts data:No Copied to github as #:27410

Description

Attempting to load a raster from a postgis database using the Database Manager plugin with a schema creates an error when the table name uses mixed case or special characters.

The reported error is "Cannot get GDAL Raster band:" in the GUI dialog.

PostGIS/GRES recommends double quotes around "database"."SchemaName"."TableName_1" or "SchemaName"."TableName_1" if already connected to the database.

It appears the code in
https://github.com/varunsaraf/postgis_raster/blob/master/postgisrasterdataset.cpp
(and perhaps elsewhere does not double quote schema and table name.
https://www.postgresql.org/docs/10/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS.

SCHEMA=rasterschema
TABLE=test (WORKS)
Table=Test_1 (FAILS), the name gets interpreted as test_1 and fails, if "test"."Test_1" raster load completes.

WORKING EXAMPLE
2018-08-09 14:00:03.724 EDT [608908] LOG: statement: select st_bandmetadata(rast, band) from (select rast, generate_series(1, 1) band from (select rast from rasterschema.test where (true) AND st_numbands(rast)=1 limit 1) bar) foo

FAILING EXAMPLE
2018-08-09 14:03:12.835 EDT [608908] LOG: statement: select st_bandmetadata(rast, band) from (select rast, generate_series(1, 1) band from (select rast from rasterschema.Test_1 where (true) AND st_numbands(rast)=1 limit 1) bar) foo
2018-08-09 14:03:12.835 EDT [608908] ERROR: relation "rasterchema.test_1" does not exist at character 105

Using the failing example from the command line with recommended quoting:
db=# select st_bandmetadata(rast, band) from (select rast, generate_series(1, 1) band from (select rast from "rasterschema"."Test_1" where (true) AND st_numbands(rast)=1 limit 1) bar) foo;
st_bandmetadata
-----------------
(8BUI,0,f,)
(1 row)

Associated revisions

Revision f4d08eb9
Added by Jürgen Fischer over 6 years ago

dbmanager/postgis: quote identifiers in urls (fixes qgis portion of #19583)

History

#1 Updated by Jürgen Fischer over 6 years ago

  • Resolution set to up/downstream

This is a GDAL issue.

#2 Updated by Nyall Dawson over 6 years ago

  • Status changed from Open to Closed

#3 Updated by al piszcz over 6 years ago

Hello, thank you for the review, do I need to submit this issue elsewhere?

The query from https://github.com/varunsaraf/postgis_raster/blob/master/postgisrasterdataset.cpp
is not enclosing the schema and table name in "".

    if (pszWhere == NULL) {
        osCommand.Printf("select st_bandmetadata(%s, band) from " 
        "(select %s, generate_series(1, st_numbands(%s)) band from " 
        "(select %s from %s.%s limit 1) bar) foo", pszColumn, pszColumn,
        pszColumn, pszColumn, pszSchema, pszTable);
    } 

    else {
        osCommand.Printf("select st_bandmetadata(%s, band) from " 
        "(select %s, generate_series(1, st_numbands(%s)) band from " 
        "(select %s from %s.%s where %s limit 1) bar) foo", pszColumn, 
        pszColumn, pszColumn, pszColumn, pszSchema, pszTable, pszWhere);
    }

#4 Updated by Jürgen Fischer over 6 years ago

al piszcz wrote:

Hello, thank you for the review, do I need to submit this issue elsewhere?

The query from https://github.com/varunsaraf/postgis_raster/blob/master/postgisrasterdataset.cpp
is not enclosing the schema and table name in "".

Take a look at https://github.com/osgeo/gdal/pull/837

Also available in: Atom PDF