Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -1319,11 +1319,11 @@ } } } if( nTerm==1 && zType && sqlite3StrICmp(zType, "INTEGER")==0 - && sortOrder==SQLITE_SO_ASC + && sortOrder!=SQLITE_SO_DESC ){ pTab->iPKey = iCol; pTab->keyConf = (u8)onError; assert( autoInc==0 || autoInc==1 ); pTab->tabFlags |= autoInc*TF_Autoincrement; @@ -2598,10 +2598,12 @@ int nCol; char *z; assert( pTo!=0 ); if( p==0 || IN_DECLARE_VTAB ) goto fk_end; + sqlite3RestrictColumnListSyntax(pParse, pFromCol); + sqlite3RestrictColumnListSyntax(pParse, pToCol); if( pFromCol==0 ){ int iCol = p->nCol-1; if( NEVER(iCol<0) ) goto fk_end; if( pToCol && pToCol->nExpr!=1 ){ sqlite3ErrorMsg(pParse, "foreign key on %s" @@ -3036,11 +3038,12 @@ if( pList==0 ){ pList = sqlite3ExprListAppend(pParse, 0, 0); if( pList==0 ) goto exit_create_index; pList->a[0].zName = sqlite3DbStrDup(pParse->db, pTab->aCol[pTab->nCol-1].zName); - pList->a[0].sortOrder = (u8)sortOrder; + assert( pList->nExpr==1 ); + sqlite3ExprListSetSortOrder(pList, sortOrder); } /* Figure out how many bytes of space are required to store explicitly ** specified collation sequence names. */ @@ -4280,10 +4283,36 @@ pKey = 0; } } return pKey; } + +/* +** Generate a syntax error if the expression list provided contains +** any COLLATE or ASC or DESC keywords. +** +** Some legacy versions of SQLite allowed constructs like: +** +** CREATE TABLE x(..., FOREIGN KEY(x COLLATE binary DESC) REFERENCES...); +** ^^^^^^^^^^^^^^^^^^^ +** +** The COLLATE and sort order terms were ignored. To prevent compatibility +** problems in case something like this appears in a legacy sqlite_master +** table, only enforce the restriction on new SQL statements, not when +** parsing the schema out of the sqlite_master table. +*/ +void sqlite3RestrictColumnListSyntax(Parse *pParse, ExprList *p){ + int i; + if( p==0 || pParse->db->init.busy ) return; + for(i=0; inExpr; i++){ + if( p->a[i].pExpr!=0 || p->a[i].bDefinedSO ){ + sqlite3ErrorMsg(pParse, "syntax error after column name \"%w\"", + p->a[i].zName); + return; + } + } +} #ifndef SQLITE_OMIT_CTE /* ** This routine is invoked once per CTE by the parser while parsing a ** WITH clause. @@ -4296,10 +4325,12 @@ Select *pQuery /* Query used to initialize the table */ ){ sqlite3 *db = pParse->db; With *pNew; char *zName; + + sqlite3RestrictColumnListSyntax(pParse, pArglist); /* Check that the CTE name is unique within this WITH clause. If ** not, store an error in the Parse structure. */ zName = sqlite3NameFromToken(pParse->db, pName); if( zName && pWith ){ Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -1157,10 +1157,25 @@ /* Avoid leaking memory if malloc has failed. */ sqlite3ExprDelete(db, pExpr); sqlite3ExprListDelete(db, pList); return 0; } + +/* +** Set the sort order for the last element on the given ExprList. +*/ +void sqlite3ExprListSetSortOrder(ExprList *p, int iSortOrder){ + if( p==0 ) return; + assert( SQLITE_SO_UNDEFINED<0 && SQLITE_SO_ASC>=0 && SQLITE_SO_DESC>0 ); + assert( p->nExpr>0 ); + if( iSortOrder<0 ){ + assert( p->a[p->nExpr-1].sortOrder==SQLITE_SO_ASC ); + return; + } + p->a[p->nExpr-1].sortOrder = (u8)iSortOrder; + p->a[p->nExpr-1].bDefinedSO = 1; +} /* ** Set the ExprList.a[].zName element of the most recently added item ** on the expression list. ** Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -678,22 +678,22 @@ orderby_opt(A) ::= . {A = 0;} orderby_opt(A) ::= ORDER BY sortlist(X). {A = X;} sortlist(A) ::= sortlist(X) COMMA expr(Y) sortorder(Z). { A = sqlite3ExprListAppend(pParse,X,Y.pExpr); - if( A ) A->a[A->nExpr-1].sortOrder = (u8)Z; + sqlite3ExprListSetSortOrder(A,Z); } sortlist(A) ::= expr(Y) sortorder(Z). { A = sqlite3ExprListAppend(pParse,0,Y.pExpr); - if( A && ALWAYS(A->a) ) A->a[0].sortOrder = (u8)Z; + sqlite3ExprListSetSortOrder(A,Z); } %type sortorder {int} sortorder(A) ::= ASC. {A = SQLITE_SO_ASC;} sortorder(A) ::= DESC. {A = SQLITE_SO_DESC;} -sortorder(A) ::= . {A = SQLITE_SO_ASC;} +sortorder(A) ::= . {A = SQLITE_SO_UNDEFINED;} %type groupby_opt {ExprList*} %destructor groupby_opt {sqlite3ExprListDelete(pParse->db, $$);} groupby_opt(A) ::= . {A = 0;} groupby_opt(A) ::= GROUP BY nexprlist(X). {A = X;} @@ -1227,18 +1227,18 @@ idxlist(A) ::= idxlist(X) COMMA nm(Y) collate(C) sortorder(Z). { Expr *p = sqlite3ExprAddCollateToken(pParse, 0, &C, 1); A = sqlite3ExprListAppend(pParse,X, p); sqlite3ExprListSetName(pParse,A,&Y,1); sqlite3ExprListCheckLength(pParse, A, "index"); - if( A ) A->a[A->nExpr-1].sortOrder = (u8)Z; + sqlite3ExprListSetSortOrder(A,Z); } idxlist(A) ::= nm(Y) collate(C) sortorder(Z). { Expr *p = sqlite3ExprAddCollateToken(pParse, 0, &C, 1); A = sqlite3ExprListAppend(pParse,0, p); sqlite3ExprListSetName(pParse, A, &Y, 1); sqlite3ExprListCheckLength(pParse, A, "index"); - if( A ) A->a[A->nExpr-1].sortOrder = (u8)Z; + sqlite3ExprListSetSortOrder(A,Z); } %type collate {Token} collate(C) ::= . {C.z = 0; C.n = 0;} collate(C) ::= COLLATE ids(X). {C = X;} Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1523,10 +1523,11 @@ /* ** A sort order can be either ASC or DESC. */ #define SQLITE_SO_ASC 0 /* Sort in ascending order */ #define SQLITE_SO_DESC 1 /* Sort in ascending order */ +#define SQLITE_SO_UNDEFINED -1 /* No sort order specified */ /* ** Column affinity types. ** ** These used to have mnemonic name like 'i' for SQLITE_AFF_INTEGER and @@ -2187,10 +2188,11 @@ char *zSpan; /* Original text of the expression */ u8 sortOrder; /* 1 for DESC or 0 for ASC */ unsigned done :1; /* A flag to indicate when processing is finished */ unsigned bSpanIsTab :1; /* zSpan holds DB.TABLE.COLUMN */ unsigned reusable :1; /* Constant expression is reusable */ + unsigned bDefinedSO :1; /* True if either DESC or ASC keywords present */ union { struct { u16 iOrderByCol; /* For ORDER BY, column number in result set */ u16 iAlias; /* Index into Parse.aAlias[] for zName */ } x; @@ -3242,10 +3244,11 @@ Expr *sqlite3ExprAnd(sqlite3*,Expr*, Expr*); Expr *sqlite3ExprFunction(Parse*,ExprList*, Token*); void sqlite3ExprAssignVarNumber(Parse*, Expr*); void sqlite3ExprDelete(sqlite3*, Expr*); ExprList *sqlite3ExprListAppend(Parse*,ExprList*,Expr*); +void sqlite3ExprListSetSortOrder(ExprList*,int); void sqlite3ExprListSetName(Parse*,ExprList*,Token*,int); void sqlite3ExprListSetSpan(Parse*,ExprList*,ExprSpan*); void sqlite3ExprListDelete(sqlite3*, ExprList*); u32 sqlite3ExprListFlags(const ExprList*); int sqlite3Init(sqlite3*, char**); @@ -3753,10 +3756,11 @@ const char *sqlite3JournalModename(int); #ifndef SQLITE_OMIT_WAL int sqlite3Checkpoint(sqlite3*, int, int, int*, int*); int sqlite3WalDefaultHook(void*,sqlite3*,const char*,int); #endif +void sqlite3RestrictColumnListSyntax(Parse*,ExprList*); #ifndef SQLITE_OMIT_CTE With *sqlite3WithAdd(Parse*,With*,Token*,ExprList*,Select*); void sqlite3WithDelete(sqlite3*,With*); void sqlite3WithPush(Parse*, With*, u8); #else ADDED test/parser1.test Index: test/parser1.test ================================================================== --- /dev/null +++ test/parser1.test @@ -0,0 +1,58 @@ +# 2014-08-24 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# The focus of this script is testing details of the SQL language parser. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +do_catchsql_test parser1-1.1 { + CREATE TABLE t1( + a TEXT PRIMARY KEY, + b TEXT, + FOREIGN KEY(b COLLATE nocase DESC) REFERENCES t1(a COLLATE binary ASC) + ); +} {1 {syntax error after column name "a"}} +do_execsql_test parser1-1.2 { + CREATE TABLE t1( + a TEXT PRIMARY KEY, + b TEXT, + FOREIGN KEY(b) REFERENCES t1(a) + ); + INSERT INTO t1 VALUES('abc',NULL),('xyz','abc'); + PRAGMA writable_schema=on; + UPDATE sqlite_master SET sql='CREATE TABLE t1( + a TEXT PRIMARY KEY, + b TEXT, + FOREIGN KEY(b COLLATE nocase) REFERENCES t1(a) + )' WHERE name='t1'; + SELECT name FROM sqlite_master WHERE sql LIKE '%collate%'; +} {t1} +sqlite3 db2 test.db +do_test parser1-1.3 { + sqlite3 db2 test.db + db2 eval {SELECT * FROM t1 ORDER BY 1} +} {abc {} xyz abc} + + +do_catchsql_test parser1-2.1 { + WITH RECURSIVE + c(x COLLATE binary) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5) + SELECT x FROM c; +} {1 {syntax error after column name "x"}} +do_catchsql_test parser1-2.2 { + WITH RECURSIVE + c(x ASC) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5) + SELECT x FROM c; +} {1 {syntax error after column name "x"}} + +finish_test