Disallow the use of COLLATE clauses and the ASC and DESC keywords within
foreign key constraints and in the argument list to common table expressions.
FossilOrigin-Name: 83cbc4d8761498647794affffa961a4fca311be7
diff --git a/src/build.c b/src/build.c
index e45908d..0a816b3 100644
--- a/src/build.c
+++ b/src/build.c
@@ -1321,7 +1321,7 @@
}
if( nTerm==1
&& zType && sqlite3StrICmp(zType, "INTEGER")==0
- && sortOrder==SQLITE_SO_ASC
+ && sortOrder!=SQLITE_SO_DESC
){
pTab->iPKey = iCol;
pTab->keyConf = (u8)onError;
@@ -2600,6 +2600,8 @@
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;
@@ -3038,7 +3040,8 @@
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
@@ -4283,6 +4286,32 @@
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; i<p->nExpr; 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
@@ -4299,6 +4328,8 @@
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);
diff --git a/src/expr.c b/src/expr.c
index 04cd36e..1c57ecc 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -1161,6 +1161,21 @@
}
/*
+** 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.
**
diff --git a/src/parse.y b/src/parse.y
index 3d186b2..bc193ec 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -680,18 +680,18 @@
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, $$);}
@@ -1229,14 +1229,14 @@
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}
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 96a7700..18a0fd7 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -1525,6 +1525,7 @@
*/
#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.
@@ -2189,6 +2190,7 @@
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 */
@@ -3244,6 +3246,7 @@
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*);
@@ -3755,6 +3758,7 @@
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*);