Opened 12 years ago

Closed 11 years ago

Last modified 11 years ago

#2220 closed defect (fixed)

Non standard constant string processing in OGR SQL

Reported by: trastourf Owned by: Mateusz Łoskot
Priority: normal Milestone: 1.5.3
Component: OGR_SF Version: svn-trunk
Severity: normal Keywords: sql
Cc: warmerdam, tamas

Description (last modified by Mateusz Łoskot)

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.

Attachments (1)

quoted-shp-ticket-2220.tar.gz (812 bytes) - added by Mateusz Łoskot 11 years ago.
Sample dataste used to test OGR SQL and fixed string literals escaping using single-quotes

Download all attachments as: .zip

Change History (6)

comment:1 Changed 12 years ago by warmerdam

Cc: warmerdam tamas added
Keywords: sql added
Milestone: 1.5.2
Owner: changed from warmerdam to Mateusz Łoskot

Matuesz,

To address when you are back under contract.

comment:2 Changed 11 years ago by Mateusz Łoskot

Description: modified (diff)

comment:3 Changed 11 years ago by Mateusz Łoskot

Status: newassigned
Version: unspecifiedsvn-trunk

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.

Changed 11 years ago by Mateusz Łoskot

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

comment:4 Changed 11 years ago by Mateusz Łoskot

Resolution: fixed
Status: assignedclosed

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

comment:5 Changed 11 years ago by 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)
    
Note: See TracTickets for help on using tickets.