[postgis-tickets] [PostGIS] #2383: [raster]: Create extension with backslash_quote off

[PostGIS]

#2383: [raster]: Create extension with backslash_quote off
-----------------------+----------------------------------------------------
 Reporter:  sbranchaw  |       Owner:  dustymugs    
     Type:  defect     |      Status:  assigned     
 Priority:  blocker    |   Milestone:  PostGIS 2.0.4
Component:  raster     |     Version:  2.0.x        
 Keywords:             |  
-----------------------+----------------------------------------------------
Description changed by dustymugs:

Old description:

> Problem: backslash_quote has to be on in order to create the PostGIS
> extension.
>
> This is an inconvenience because I have a nightly backup job that
> restores a compressed dump file to a database where backslash_quote is
> turned off for security reasons.
>
> To reproduce:
> 1. createdb postgis_testdb
> 2. psql postgis_testdb
> 3. SET backslash_quote = off;
> 4. CREATE EXTENSION postgis;
>
> Receive the error:
> postgis_testdb=# CREATE EXTENSION postgis;
> ERROR:  unsafe use of \' in a string literal
> LINE 29:                 RAISE WARNING E'Format \'%\' is not recogniz...
>                                        ^
> HINT:  Use '' to write quotes in strings. \' is insecure in client-only
> encodings.
> QUERY:
>     DECLARE
>         params text[];
>         rastout raster;
>     BEGIN
>         IF rast IS NULL THEN
>             RAISE WARNING 'Cannot set georeferencing on a null raster in
> st_setgeoreference.';
>             RETURN rastout;
>         END IF;
>
>         SELECT regexp_matches(georef,
> E'(-?\\d+(?:\\.\\d+)?)\\s(-?\\d+(?:\\.\\d+)?)\\s(-?\\d+(?:\\.\\d+)?)\\s'
> ||
> E'(-?\\d+(?:\\.\\d+)?)\\s(-?\\d+(?:\\.\\d+)?)\\s(-?\\d+(?:\\.\\d+)?)')
> INTO params;
>
>         IF NOT FOUND THEN
>             RAISE EXCEPTION 'st_setgeoreference requires a string with 6
> floating point values.';
>         END IF;
>
>         IF format = 'ESRI' THEN
>             -- params array is now:
>             -- {scalex, skewy, skewx, scaley, upperleftx, upperlefty}
>             rastout := st_setscale(rast, params[1]::float8,
> params[4]::float8);
>             rastout := st_setskew(rastout, params[3]::float8,
> params[2]::float8);
>             rastout := st_setupperleft(rastout,
>                                    params[5]::float8 - (params[1]::float8
> * 0.5),
>                                    params[6]::float8 - (params[4]::float8
> * 0.5));
>         ELSE
>             IF format != 'GDAL' THEN
>                 RAISE WARNING E'Format \'%\' is not recognized,
> defaulting to GDAL format.', format;
>             END IF;
>             -- params array is now:
>             -- {scalex, skewy, skewx, scaley, upperleftx, upperlefty}
>
>             rastout := st_setscale(rast, params[1]::float8,
> params[4]::float8);
>             rastout := st_setskew( rastout, params[3]::float8,
> params[2]::float8);
>             rastout := st_setupperleft(rastout, params[5]::float8,
> params[6]::float8);
>         END IF;
>         RETURN rastout;
>     END;

New description:

 Problem: backslash_quote has to be on in order to create the PostGIS
 extension.

 This is an inconvenience because I have a nightly backup job that restores
 a compressed dump file to a database where backslash_quote is turned off
 for security reasons.

 To reproduce:

 1. createdb postgis_testdb

 2. psql postgis_testdb

 3. SET backslash_quote = off;

 4. CREATE EXTENSION postgis;

 Receive the error:

 {{{
 postgis_testdb=# CREATE EXTENSION postgis;
 ERROR:  unsafe use of \' in a string literal
 LINE 29:                 RAISE WARNING E'Format \'%\' is not recogniz...
                                        ^
 HINT:  Use '' to write quotes in strings. \' is insecure in client-only
 encodings.
 QUERY:
     DECLARE
         params text[];
         rastout raster;
     BEGIN
         IF rast IS NULL THEN
             RAISE WARNING 'Cannot set georeferencing on a null raster in
 st_setgeoreference.';
             RETURN rastout;
         END IF;

         SELECT regexp_matches(georef,
 E'(-?\\d+(?:\\.\\d+)?)\\s(-?\\d+(?:\\.\\d+)?)\\s(-?\\d+(?:\\.\\d+)?)\\s'
 ||
 E'(-?\\d+(?:\\.\\d+)?)\\s(-?\\d+(?:\\.\\d+)?)\\s(-?\\d+(?:\\.\\d+)?)')
 INTO params;

         IF NOT FOUND THEN
             RAISE EXCEPTION 'st_setgeoreference requires a string with 6
 floating point values.';
         END IF;

         IF format = 'ESRI' THEN
             -- params array is now:
             -- {scalex, skewy, skewx, scaley, upperleftx, upperlefty}
             rastout := st_setscale(rast, params[1]::float8,
 params[4]::float8);
             rastout := st_setskew(rastout, params[3]::float8,
 params[2]::float8);
             rastout := st_setupperleft(rastout,
                                    params[5]::float8 - (params[1]::float8
 * 0.5),
                                    params[6]::float8 - (params[4]::float8
 * 0.5));
         ELSE
             IF format != 'GDAL' THEN
                 RAISE WARNING E'Format \'%\' is not recognized, defaulting
 to GDAL format.', format;
             END IF;
             -- params array is now:
             -- {scalex, skewy, skewx, scaley, upperleftx, upperlefty}

             rastout := st_setscale(rast, params[1]::float8,
 params[4]::float8);
             rastout := st_setskew( rastout, params[3]::float8,
 params[2]::float8);
             rastout := st_setupperleft(rastout, params[5]::float8,
 params[6]::float8);
         END IF;
         RETURN rastout;
     END;
 }}}

--

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/2383#comment:5>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.