Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Disallow string constants enclosed in double-quotes within new CREATE TABLE and CREATE INDEX statements. It is still possible to enclose column names in double-quotes, and existing database schemas that use double-quotes for strings can still be loaded. This addresses ticket [9b78184b]. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
1685610ef8e0dc9218b02461ceab14dc |
User & Date: | dan 2019-05-20 17:14:25 |
References
2019-05-20
| ||
18:35 | A minor variation on check-in [1685610ef8e0dc] which (hopefully) makes the logic a little easier to follow. Also disallows the use of the double-quoted string hack in the query expression used for VACUUM INTO, which is not strictly required, but moves us toward the goal of disallowing the double-quoted string hack everywhere. (check-in: 3e1b55f3 user: drh tags: trunk) | |
17:15 | • Closed ticket [9b78184b]: Index on non-existing column results in a fabricated value being fetched plus 6 other changes (artifact: 6093aca9 user: dan) | |
Context
2019-05-20
| ||
18:35 | A minor variation on check-in [1685610ef8e0dc] which (hopefully) makes the logic a little easier to follow. Also disallows the use of the double-quoted string hack in the query expression used for VACUUM INTO, which is not strictly required, but moves us toward the goal of disallowing the double-quoted string hack everywhere. (check-in: 3e1b55f3 user: drh tags: trunk) | |
17:14 | Disallow string constants enclosed in double-quotes within new CREATE TABLE and CREATE INDEX statements. It is still possible to enclose column names in double-quotes, and existing database schemas that use double-quotes for strings can still be loaded. This addresses ticket [9b78184b]. (check-in: 1685610e user: dan tags: trunk) | |
10:36 | Update the sqlite3ExprCompare() routine so that it does not think "? IS NOT TRUE" is the same as "? IS TRUE". Fix for [d3e7f2ba5b3]. (check-in: 99eba69b user: dan tags: trunk) | |
Changes
Changes to src/resolve.c.
472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 .... 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 .... 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 |
** pExpr. ** ** Because no reference was made to outer contexts, the pNC->nRef ** fields are not changed in any context. */ if( cnt==0 && zTab==0 ){ assert( pExpr->op==TK_ID ); if( ExprHasProperty(pExpr,EP_DblQuoted) ){ /* If a double-quoted identifier does not match any known column name, ** then treat it as a string. ** ** This hack was added in the early days of SQLite in a misguided attempt ** to be compatible with MySQL 3.x, which used double-quotes for strings. ** I now sorely regret putting in this hack. The effect of this hack is ** that misspelled identifier names are silently converted into strings ................................................................................ ** An error message is left in pParse if anything is amiss. The number ** if errors is returned. */ int sqlite3ResolveExprNames( NameContext *pNC, /* Namespace to resolve expressions in. */ Expr *pExpr /* The expression to be analyzed. */ ){ u16 savedHasAgg; Walker w; if( pExpr==0 ) return SQLITE_OK; savedHasAgg = pNC->ncFlags & (NC_HasAgg|NC_MinMaxAgg|NC_HasWin); pNC->ncFlags &= ~(NC_HasAgg|NC_MinMaxAgg|NC_HasWin); w.pParse = pNC->pParse; w.xExprCallback = resolveExprStep; ................................................................................ sSrc.a[0].zName = pTab->zName; sSrc.a[0].pTab = pTab; sSrc.a[0].iCursor = -1; } sNC.pParse = pParse; sNC.pSrcList = &sSrc; sNC.ncFlags = type; if( (rc = sqlite3ResolveExprNames(&sNC, pExpr))!=SQLITE_OK ) return rc; if( pList ) rc = sqlite3ResolveExprListNames(&sNC, pList); return rc; } |
| > > | > > > |
472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 .... 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 .... 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 |
** pExpr. ** ** Because no reference was made to outer contexts, the pNC->nRef ** fields are not changed in any context. */ if( cnt==0 && zTab==0 ){ assert( pExpr->op==TK_ID ); if( ExprHasProperty(pExpr,EP_DblQuoted) && 0==(pTopNC->ncFlags&NC_NewSchema) ){ /* If a double-quoted identifier does not match any known column name, ** then treat it as a string. ** ** This hack was added in the early days of SQLite in a misguided attempt ** to be compatible with MySQL 3.x, which used double-quotes for strings. ** I now sorely regret putting in this hack. The effect of this hack is ** that misspelled identifier names are silently converted into strings ................................................................................ ** An error message is left in pParse if anything is amiss. The number ** if errors is returned. */ int sqlite3ResolveExprNames( NameContext *pNC, /* Namespace to resolve expressions in. */ Expr *pExpr /* The expression to be analyzed. */ ){ int savedHasAgg; Walker w; if( pExpr==0 ) return SQLITE_OK; savedHasAgg = pNC->ncFlags & (NC_HasAgg|NC_MinMaxAgg|NC_HasWin); pNC->ncFlags &= ~(NC_HasAgg|NC_MinMaxAgg|NC_HasWin); w.pParse = pNC->pParse; w.xExprCallback = resolveExprStep; ................................................................................ sSrc.a[0].zName = pTab->zName; sSrc.a[0].pTab = pTab; sSrc.a[0].iCursor = -1; } sNC.pParse = pParse; sNC.pSrcList = &sSrc; sNC.ncFlags = type; if( pTab && !pParse->db->init.busy && !sqlite3WritableSchema(pParse->db) ){ sNC.ncFlags |= NC_NewSchema; } if( (rc = sqlite3ResolveExprNames(&sNC, pExpr))!=SQLITE_OK ) return rc; if( pList ) rc = sqlite3ResolveExprListNames(&sNC, pList); return rc; } |
Changes to src/sqliteInt.h.
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
2775
2776
....
2789
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799
2800
2801
2802
|
ExprList *pEList; /* Optional list of result-set columns */ AggInfo *pAggInfo; /* Information about aggregates at this level */ Upsert *pUpsert; /* ON CONFLICT clause information from an upsert */ } uNC; NameContext *pNext; /* Next outer name context. NULL for outermost */ int nRef; /* Number of names resolved by this context */ int nErr; /* Number of errors encountered while resolving names */ u16 ncFlags; /* Zero or more NC_* flags defined below */ Select *pWinSelect; /* SELECT statement for any window functions */ }; /* ** Allowed values for the NameContext, ncFlags field. ** ** Value constraints (all checked via assert()): ................................................................................ #define NC_UEList 0x0080 /* True if uNC.pEList is used */ #define NC_UAggInfo 0x0100 /* True if uNC.pAggInfo is used */ #define NC_UUpsert 0x0200 /* True if uNC.pUpsert is used */ #define NC_MinMaxAgg 0x1000 /* min/max aggregates seen. See note above */ #define NC_Complex 0x2000 /* True if a function or subquery seen */ #define NC_AllowWin 0x4000 /* Window functions are allowed here */ #define NC_HasWin 0x8000 /* One or more window functions seen */ /* ** An instance of the following object describes a single ON CONFLICT ** clause in an upsert. ** ** The pUpsertTarget field is only set if the ON CONFLICT clause includes ** conflict-target clause. (In "ON CONFLICT(a,b)" the "(a,b)" is the |
|
>
|
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
2775
2776
....
2789
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799
2800
2801
2802
2803
|
ExprList *pEList; /* Optional list of result-set columns */ AggInfo *pAggInfo; /* Information about aggregates at this level */ Upsert *pUpsert; /* ON CONFLICT clause information from an upsert */ } uNC; NameContext *pNext; /* Next outer name context. NULL for outermost */ int nRef; /* Number of names resolved by this context */ int nErr; /* Number of errors encountered while resolving names */ int ncFlags; /* Zero or more NC_* flags defined below */ Select *pWinSelect; /* SELECT statement for any window functions */ }; /* ** Allowed values for the NameContext, ncFlags field. ** ** Value constraints (all checked via assert()): ................................................................................ #define NC_UEList 0x0080 /* True if uNC.pEList is used */ #define NC_UAggInfo 0x0100 /* True if uNC.pAggInfo is used */ #define NC_UUpsert 0x0200 /* True if uNC.pUpsert is used */ #define NC_MinMaxAgg 0x1000 /* min/max aggregates seen. See note above */ #define NC_Complex 0x2000 /* True if a function or subquery seen */ #define NC_AllowWin 0x4000 /* Window functions are allowed here */ #define NC_HasWin 0x8000 /* One or more window functions seen */ #define NC_NewSchema 0x10000 /* Currently resolving self-refs for new object */ /* ** An instance of the following object describes a single ON CONFLICT ** clause in an upsert. ** ** The pUpsertTarget field is only set if the ON CONFLICT clause includes ** conflict-target clause. (In "ON CONFLICT(a,b)" the "(a,b)" is the |
Changes to test/check.test.
113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 |
execsql { SELECT * FROM t1; } } {4 11.0} do_test check-2.1 { execsql { CREATE TABLE t2( x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=="integer" ), y REAL CONSTRAINT two CHECK( typeof(coalesce(y,0.1))=='real' ), z TEXT CONSTRAINT three CHECK( typeof(coalesce(z,''))=='text' ) ); } } {} do_test check-2.2 { execsql { INSERT INTO t2 VALUES(1,2.2,'three'); SELECT * FROM t2; } |
> > |
113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 |
execsql { SELECT * FROM t1; } } {4 11.0} do_test check-2.1 { execsql { PRAGMA writable_schema = 1; CREATE TABLE t2( x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=="integer" ), y REAL CONSTRAINT two CHECK( typeof(coalesce(y,0.1))=='real' ), z TEXT CONSTRAINT three CHECK( typeof(coalesce(z,''))=='text' ) ); PRAGMA writable_schema = 0; } } {} do_test check-2.2 { execsql { INSERT INTO t2 VALUES(1,2.2,'three'); SELECT * FROM t2; } |
Changes to test/quote.test.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
..
80
81
82
83
84
85
86
87
88
89
|
# focus of this file is the ability to specify table and column names # as quoted strings. # # $Id: quote.test,v 1.7 2007/04/25 11:32:30 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table with a strange name and with strange column names. # do_test quote-1.0 { catchsql {CREATE TABLE '@abc' ( '#xyz' int, '!pqr' text );} } {0 {}} ................................................................................ # do_test quote-1.6 { set r [catch { execsql {DROP TABLE '@abc'} } msg ] lappend r $msg } {0 {}} finish_test |
>
|
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
..
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
|
# focus of this file is the ability to specify table and column names # as quoted strings. # # $Id: quote.test,v 1.7 2007/04/25 11:32:30 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix quote # Create a table with a strange name and with strange column names. # do_test quote-1.0 { catchsql {CREATE TABLE '@abc' ( '#xyz' int, '!pqr' text );} } {0 {}} ................................................................................ # do_test quote-1.6 { set r [catch { execsql {DROP TABLE '@abc'} } msg ] lappend r $msg } {0 {}} #------------------------------------------------------------------------- # Check that it is not possible to use double-quotes for a string # constant in a CHECK constraint or CREATE INDEX statement. However, # SQLite can load such a schema from disk. # reset_db do_execsql_test 2.0 { CREATE TABLE t1(x, y, z); } foreach {tn sql errname} { 1 { CREATE TABLE xyz(a, b, c CHECK (c!="null") ) } null 2 { CREATE INDEX i2 ON t1(x, y, z||"abc") } abc 3 { CREATE INDEX i3 ON t1("w") } w 4 { CREATE INDEX i4 ON t1(x) WHERE z="w" } w } { do_catchsql_test 2.1.$tn $sql [list 1 "no such column: $errname"] } do_execsql_test 2.2 { PRAGMA writable_schema = 1; CREATE TABLE xyz(a, b, c CHECK (c!="null") ); CREATE INDEX i2 ON t1(x, y, z||"abc"); CREATE INDEX i3 ON t1("w"); CREATE INDEX i4 ON t1(x) WHERE z="w"; } db close sqlite3 db test.db do_execsql_test 2.3.1 { INSERT INTO xyz VALUES(1, 2, 3); } do_catchsql_test 2.3.2 { INSERT INTO xyz VALUES(1, 2, 'null'); } {1 {CHECK constraint failed: xyz}} do_execsql_test 2.4 { INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t1 VALUES(4, 5, 'w'); SELECT * FROM t1 WHERE z='w'; } {4 5 w} do_execsql_test 2.5 { SELECT sql FROM sqlite_master; } { {CREATE TABLE t1(x, y, z)} {CREATE TABLE xyz(a, b, c CHECK (c!="null") )} {CREATE INDEX i2 ON t1(x, y, z||"abc")} {CREATE INDEX i3 ON t1("w")} {CREATE INDEX i4 ON t1(x) WHERE z="w"} } finish_test |