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