Opened 10 months ago

Closed 6 months ago

Last modified 6 months ago

#3701 closed defect (fixed)

Escape double quotes issue in pgsql2shp

Reported by: kalxas Owned by: robe
Priority: medium Milestone: PostGIS 2.3.3
Component: postgis Version: 2.2.x
Keywords: Cc:

Description

On behalf of Sergej Dugokontov:

Error executing main scan query: ERROR: zero-length delimited identifier at or near """" LINE 1: DECLARE cur CURSOR FOR SELECT ""quoteColumn",ST_AsEWKB(ST_Se...

If I change sql to:

SELECT
c1 AS "geom",
c2 AS """""quoteColumn"
FROM t1

I got different error:

Error executing main scan query: ERROR:  column ""quoteColumn" does not
exist
LINE 1: DECLARE cur CURSOR FOR SELECT """quoteColumn",ST_AsEWKB(ST_S...
                                      ^
HINT:  Perhaps you meant to reference the column
"__pgsql2shp22020_tmp_table.""quoteColumn".

I use pgsql2shp RELEASE: 2.2.1 (r14555) on Ubuntu 16.04 LTS and don't have any issues if column alias in sql doesn't contain double quotes.

Change History (7)

comment:1 Changed 10 months ago by kalxas

Reposting:

I have an issue when I try to create shapefile using pgsql2shp if column alias contains double quotes. For example, my SQL query is:

SELECT
c1 AS "geom",
c2 AS """quoteColumn"
FROM t1

I store this sql in a file, and if I run from console

$ pgsql2shp -r -g geom -f results -h localhost -u userName -P password
databaseName "`cat query.sql`" 1>/dev/null

I get following error:

Error executing main scan query: ERROR:  zero-length delimited identifier
at or near """"
LINE 1: DECLARE cur CURSOR FOR SELECT ""quoteColumn",ST_AsEWKB(ST_Se...

If I change sql to:

SELECT
c1 AS "geom",
c2 AS """""quoteColumn"
FROM t1

I got different error:

Error executing main scan query: ERROR:  column ""quoteColumn" does not
exist
LINE 1: DECLARE cur CURSOR FOR SELECT """quoteColumn",ST_AsEWKB(ST_S...
                                      ^
HINT:  Perhaps you meant to reference the column
"__pgsql2shp22020_tmp_table.""quoteColumn".

I use pgsql2shp RELEASE: 2.2.1 (r14555) on Ubuntu 16.04 LTS and don't have any issues if column alias in sql doesn't contain double quotes.

comment:2 Changed 10 months ago by robe

Milestone: PostGIS PostgreSQLPostGIS 2.3.3

I don't think that DBFs support columns with quotes in them to being with. Why is it you are wanting to do this? Seems strange to want to alias a column to have quotes.

I did this for example and it worked using

RELEASE: 2.3.1dev (r15238)

pgsql2shp -r -g geom -f test pgr "SELECT ST_SetSRID(ST_Point(1,2)::geometry,4326)  AS "geom", 1 AS """quoteColumn""

However the output column gets output as QUOTECOLUMN

If you use the -k option to preserve case, the quotes still get stripped I think

pgsql2shp -k -r -g geom -f test pgr "SELECT ST_SetSRID(ST_Point(1,2)::geometry,4326)  AS "geom", 1 AS """quoteColumn""

So my dbf column ends up being quoteColumn.

Anyrate, I think your quoting might still be off by a bit and seems like a waste of effort.

comment:3 Changed 10 months ago by robe

okay I tried again with this, which might be what you meant.

I started off with a table that has quotes in column:

So my dbf column ends up being quoteColumn.

Anyrate, I think your quoting might still be off by a bit and seems like a waste of effort.

I also tried this first creating a table:


{{{
SELECT ST_SetSRID(ST_Point(1,2)::geometry,4326)  AS "geom", 1 AS """quoteColumn" INTO testq;
}}}


Then outputing the table




{{{  --run from psql
SELECT ST_SetSRID(ST_Point(1,2)::geometry,4326)  AS "geom", 1 AS """quoteColumn" INTO testq;

Then I tried to export the table

pgsql2shp -r -g geom -f testq pgr testq

and it failed with your error.

Initializing...
Error executing main scan query: ERROR:  zero-length delimited identifier at or near """"
LINE 1: DECLARE cur CURSOR FOR SELECT ""quoteColumn",ST_AsEWKB(ST_Se...
                                      ^

So seems there is something wrong with our quote escaping logic.

Last edited 10 months ago by robe (previous) (diff)

comment:4 Changed 6 months ago by robe

Owner: changed from pramsey to robe

comment:5 Changed 6 months ago by robe

In 15441:

Escape double quotes issue in pgsql2shp
Include test for boolean and quotes
References #3701 for PostGIS 2.4 (trunk)

comment:6 Changed 6 months ago by robe

Resolution: fixed
Status: newclosed

In 15442:

Backport fixes for pgsql2shp for Boolean field length and quote escaping
Includes tests for both
Closes #3682
Closes #3701

comment:7 Changed 6 months ago by robe

In 15443:

Backport test sql script missed in last commit
References #3682 for 2.3.3
References #3701 for 2.3.3

Note: See TracTickets for help on using tickets.