1.. _rfc-52:
2
3=======================================================================================
4RFC 52: Strict OGR SQL quoting
5=======================================================================================
6
7Author: Even Rouault
8
9Contact: even dot rouault at spatialys dot com
10
11Status: adopted, implemented in GDAL 2.0
12
13Summary
14-------
15
16This RFC proposes that OGR SQL enforces strict quoting rules for SQL
17literals and identifiers
18
19Details
20-------
21
22Currently the OGR SQL engine deals indifferently with single quote
23characters (') and double quote characters ("), although they can be
24used in 2 different contexts :
25
26-  to specify string literals
27-  to specify column or table names (when they need quoting)
28
29SQL 92 mandates that string literals are surrounded with single quote
30characters whereas quoted identifiers (column, table names) are
31surrounded with double quote characters.
32
33From
34`http://savage.net.au/SQL/sql-92.bnf.html <http://savage.net.au/SQL/sql-92.bnf.html>`__:
35
36::
37
38   <delimited identifier>     ::= <double quote> <delimited identifier body> <double quote>
39   <character string literal> ::= <quote> [ <character representation> ... ] <quote>
40
41Current OGR behavior is generally fine, except in some situations when
42specifying columns in a SELECT statement or in a WHERE expression. The
43OGR SQL engine tries to determine the intent with the following rule :
44if the quoted string matches a column name, then it is assumed to be a
45column identifier, otherwise a string literal. But sometimes (see
46`http://trac.osgeo.org/gdal/ticket/5428 <http://trac.osgeo.org/gdal/ticket/5428>`__),
47there are situations where filters such as "MyField" = 'MYFIELD' are
48needed, and currently they always evaluate to TRUE.
49
50To avoid any ambiguities and more conformant to SQL, string literals
51should be single-quoted, and identifiers (column/table names) unquoted
52or surrounded by double-quote character if conflicting with reserved
53keywords.
54
55Implementation
56--------------
57
58Implementation will be done by Even Rouault
59(`Spatialys <http://spatialys.com>`__).
60
61The proposed implementation lies in the "rfc52_stricter_sql" branch of
62the
63`https://github.com/rouault/gdal2/tree/rfc52_stricter_sql <https://github.com/rouault/gdal2/tree/rfc52_stricter_sql>`__
64repository.
65
66The list of changes :
67`https://github.com/rouault/gdal2/compare/rfc52_stricter_sql <https://github.com/rouault/gdal2/compare/rfc52_stricter_sql>`__
68
69Compatibility
70-------------
71
72This change affects compatibility in the sense that users that relied on
73the current permissive behavior will have to adapt their SQL
74expressions. This might be quite tricky to detect as it will not always
75cause obvious runtime failures. For example " SELECT
76'i_thought_this_would_be_interpreted_as_a_column_name_but_now_it_is_a_string_litteral'
77FROM atable " will run without error but not produce the previously
78expected result. So applications will have to review how they build SQL
79filters ( SetAttributeFilter() ) and expressions ( ExecuteSQL() )
80
81Expressions fixed to be compliant with stricter rules will also work
82with older GDAL versions.
83
84A prominent warning should also be put on the OGR SQL documentation page
85to advertise the change of behavior
86
87Discussion
88----------
89
90Tests done with SQLite show that it is sometimes tolerant to misuse of
91quote characters, but in the reverse way as current OGR. So it will
92accept SELECT "non_existing_column" FROM 'atable' and interpret it as
93SELECT 'non_existing_column' FROM "atable". On the contrary, PostgreSQL
94will not accept it. I'd be rather inclined to follow PostgreSQL stricter
95approach to keep things simple and understandable: "string literals
96should be single-quoted, and identifiers (column/table names) unquoted
97or surrounded by double-quote character"
98
99Testing
100-------
101
102The Python autotest suite will be fixed to still pass.
103
104Voting history
105--------------
106
107+1 from JukkaR, TamasS, DanielM and EvenR
108