Non standard constant string processing in OGR SQL

[trastourf]

Sql ogr implementation is not standard about string constant processing : it doesn't support ” ( two quotes ) escape ùethod for specifying a quote in a quoted string.

In addition a string is closed by a quote (') or a double quote (") independently of the the character used to open it.

This means that :

"toto'titi"

is interpreted as toto, not toto'titi. and

'toto''titi'

is interpreted as toto, not toto'titi.

[warmerdam]

Matuesz,

To address when you are back under contract.

[Mateusz Łoskot]

I have managed to reproduce this problem against Shapefile dataset:

$ ogrinfo quoted.shp -sql "SELECT * FROM quoted WHERE name = 'O''Reilly' "
OGR: OGROpen(/home/mloskot/data/test/quoted.shp/0x804e5d0) succeeded as ESRI Shapefile.
INFO: Open of `/home/mloskot/data/test/quoted.shp'
      using driver `ESRI Shapefile' successful.
ERROR 1: SQL: Syntax error, 1 extra tokens
OGR: GetLayerCount() = 1

The tested Shapefile stores following 4 features:

$ ogrinfo ~/data/test/quoted.shp quoted
INFO: Open of `/home/mloskot/data/test/quoted.shp'
      using driver `ESRI Shapefile' successful.
 
Layer name: quoted
Geometry: Point
Feature Count: 4
Extent: (1.000000, 1.000000) - (3.000000, 3.000000)
Layer SRS WKT:
(unknown)
ID: Real (11.0)
NAME: String (13.0)
OGRFeature(quoted):0
  ID (Real) =           1
  NAME (String) = O'Reilly
  POINT (0 0)
 
OGRFeature(quoted):1
  ID (Real) =           2
  NAME (String) = toto'titi
  POINT (1 1)
 
OGRFeature(quoted):2
  ID (Real) =           3
  NAME (String) = John's father
  POINT (2 2)
 
OGRFeature(quoted):3
  ID (Real) =           4
  NAME (String) = Mateusz
  POINT (3 3)

I've also confirmed that this problem does not leak with DBMS drivers, like PostgreSQL. This is expected behavior, because SQL statement is forwarded directly to DBMS being used, without using OGR SQL compiler.

[Mateusz Łoskot]

Sample dataste used to test OGR SQL and fixed string literals escaping using single-quotes

[Mateusz Łoskot]

Fixed in trunk (r15146) and branches/1.5 (r15147)

[Mateusz Łoskot]

Testing fix for Ticket #2220

• name = O'Reilly

  $ ogrinfo quoted.shp -sql "SELECT * FROM quoted WHERE name = 'O''Reilly' "
  INFO: Open of `/home/mloskot/data/test/quoted.shp'
        using driver `ESRI Shapefile' successful.
  
  Layer name: quoted
  Geometry: Point
  Feature Count: 1
  Extent: (1.000000, 1.000000) - (3.000000, 3.000000)
  Layer SRS WKT:
  (unknown)
  ID: Real (11.0)
  NAME: String (13.0)
  OGRFeature(quoted):0
    ID (Real) =           1
    NAME (String) = O'Reilly
    POINT (0 0)
  

• name = to''to'titi

  $ ogrinfo quoted.shp -sql "SELECT * FROM quoted WHERE name = 'to''''to''titi' "
  INFO: Open of `/home/mloskot/data/test/quoted.shp'
        using driver `ESRI Shapefile' successful.
  
  Layer name: quoted
  Geometry: Point
  Feature Count: 1
  Extent: (1.000000, 1.000000) - (3.000000, 3.000000)
  Layer SRS WKT:
  (unknown)
  ID: Real (11.0)
  NAME: String (11.0)
  OGRFeature(quoted):2
    ID (Real) =           2
    NAME (String) = to''to'titi
    POINT (1 1)
  

• name = 'abc

  $ ogrinfo ~/data/test/quoted.shp -sql "SELECT * FROM quoted WHERE name = '''abc' "
  INFO: Open of `/home/mloskot/data/test/quoted.shp'
        using driver `ESRI Shapefile' successful.
  
  Layer name: quoted
  Geometry: Point
  Feature Count: 1
  Extent: (1.000000, 1.000000) - (3.000000, 3.000000)
  Layer SRS WKT:
  (unknown)
  ID: Real (11.0)
  NAME: String (11.0)
  OGRFeature(quoted):1
    ID (Real) =           3
    NAME (String) = 'abc
    POINT (2 2)
  

• name = Mat'eu\sz

  $ ogrinfo ~/data/test/quoted.shp -sql "SELECT * FROM quoted WHERE name = 'Mat''eu\\sz' "
  INFO: Open of `/home/mloskot/data/test/quoted.shp'
        using driver `ESRI Shapefile' successful.
  
  Layer name: quoted
  Geometry: Point
  Feature Count: 1
  Extent: (1.000000, 1.000000) - (3.000000, 3.000000)
  Layer SRS WKT:
  (unknown)
  ID: Real (11.0)
  NAME: String (11.0)
  OGRFeature(quoted):3
    ID (Real) =           4
    NAME (String) = Mat'eu\sz
    POINT (3 3)