[gdal-dev] How to escape "desc" with OGR SQL?

[Jukka Rahkonen]

Frank Broniewski <frank.broniewski <at> gmail.com> writes:

> 
> Hi Jukka,
> 
> you will need to escape anything that can be interpreted as a control 
> character on the command line. But it really depends on the 
> interpretation context (console), but backslashes often need escaping as 
> well like \n for a newline. This may even happen if you write something 
> like \newspaper ... but for sql strings on the command line there's 
> usually just the double quotes where you have to pay attention.
> The problem you have here is that you are using sql keywords (desc and 
> union) for your table aliases so you have to put double quotes around 
> them. One way around this would be to not use sql keywords as table 
> aliases at all.

Yes, but if you want to utilize SQL for inserting some contents into the
description field of GPX which is nailed to have a name "desc" you can't
avoid that. 

> The next best solution would be to enclose your sql 
> string into single quotes like Even already mentioned.

Yes, but that works only on Linux. On Windows you will see - Surprise! - this:
ogrinfo -sql 'select attr_1 as "desc" from test' test.shp
FAILURE:
Unable to open datasource `attr_1' with the following drivers.

> Your double 
> quotes don't "break" the sql string because they are different. But if 
> you intend to use only double quotes for your sql string and your 
> keywords you will need to escape the quotes around the keywords because 
> otherwise they would break or signal an end to the sql string like it 
> would on a normal sententence:
> "I have " written "a sql string"
> wouldn't be a correctly encapsulated sentence, would it? A backslash \" 
> would remove the meaning of the " and not terminating the string.

I am trying to think how and where we could document this for the users. We
did not remember to think this case in
https://trac.osgeo.org/gdal/wiki/rfc52_strict_sql_quoting

Ogr2ogr documentation does not mention that SQL that is OK when executed
with pgAdmin, spatialite-gui or other direct database tools may not work as
is when used in the -sql parameter.

http://www.gdal.org/ogr2ogr.html
-sql sql_statement:
    SQL statement to execute. The resulting table/layer will be saved to the
output. Starting with GDAL 2.1, the syntax can be used to indicate that the
content is in the pointed filename. 

Good thing is that at least we have now the need to use \" sometimes on
Windows documented in the mail archives.

-Jukka-
 
> HTH
> 
> Frank