/ Check-in [83cbc4d8]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment: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.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 83cbc4d8761498647794affffa961a4fca311be7
User & Date: drh 2015-08-24 15:39:42
Context
2015-08-24
17:18
Enhancements to the batch build tool for MSVC. check-in: a1ae20cd user: mistachkin tags: trunk
15:39
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. check-in: 83cbc4d8 user: drh tags: trunk
12:42
Improvements to JSON string dequoting. check-in: 196d66d3 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  1317   1317             break;
  1318   1318           }
  1319   1319         }
  1320   1320       }
  1321   1321     }
  1322   1322     if( nTerm==1
  1323   1323      && zType && sqlite3StrICmp(zType, "INTEGER")==0
  1324         -   && sortOrder==SQLITE_SO_ASC
         1324  +   && sortOrder!=SQLITE_SO_DESC
  1325   1325     ){
  1326   1326       pTab->iPKey = iCol;
  1327   1327       pTab->keyConf = (u8)onError;
  1328   1328       assert( autoInc==0 || autoInc==1 );
  1329   1329       pTab->tabFlags |= autoInc*TF_Autoincrement;
  1330   1330       if( pList ) pParse->iPkSortOrder = pList->a[0].sortOrder;
  1331   1331     }else if( autoInc ){
................................................................................
  2596   2596     int nByte;
  2597   2597     int i;
  2598   2598     int nCol;
  2599   2599     char *z;
  2600   2600   
  2601   2601     assert( pTo!=0 );
  2602   2602     if( p==0 || IN_DECLARE_VTAB ) goto fk_end;
         2603  +  sqlite3RestrictColumnListSyntax(pParse, pFromCol);
         2604  +  sqlite3RestrictColumnListSyntax(pParse, pToCol);
  2603   2605     if( pFromCol==0 ){
  2604   2606       int iCol = p->nCol-1;
  2605   2607       if( NEVER(iCol<0) ) goto fk_end;
  2606   2608       if( pToCol && pToCol->nExpr!=1 ){
  2607   2609         sqlite3ErrorMsg(pParse, "foreign key on %s"
  2608   2610            " should reference only one column of table %T",
  2609   2611            p->aCol[iCol].zName, pTo);
................................................................................
  3034   3036     ** So create a fake list to simulate this.
  3035   3037     */
  3036   3038     if( pList==0 ){
  3037   3039       pList = sqlite3ExprListAppend(pParse, 0, 0);
  3038   3040       if( pList==0 ) goto exit_create_index;
  3039   3041       pList->a[0].zName = sqlite3DbStrDup(pParse->db,
  3040   3042                                           pTab->aCol[pTab->nCol-1].zName);
  3041         -    pList->a[0].sortOrder = (u8)sortOrder;
         3043  +    assert( pList->nExpr==1 );
         3044  +    sqlite3ExprListSetSortOrder(pList, sortOrder);
  3042   3045     }
  3043   3046   
  3044   3047     /* Figure out how many bytes of space are required to store explicitly
  3045   3048     ** specified collation sequence names.
  3046   3049     */
  3047   3050     for(i=0; i<pList->nExpr; i++){
  3048   3051       Expr *pExpr = pList->a[i].pExpr;
................................................................................
  4278   4281       if( pParse->nErr ){
  4279   4282         sqlite3KeyInfoUnref(pKey);
  4280   4283         pKey = 0;
  4281   4284       }
  4282   4285     }
  4283   4286     return pKey;
  4284   4287   }
         4288  +
         4289  +/*
         4290  +** Generate a syntax error if the expression list provided contains
         4291  +** any COLLATE or ASC or DESC keywords.
         4292  +**
         4293  +** Some legacy versions of SQLite allowed constructs like:
         4294  +**
         4295  +**      CREATE TABLE x(..., FOREIGN KEY(x COLLATE binary DESC) REFERENCES...);
         4296  +**                                        ^^^^^^^^^^^^^^^^^^^
         4297  +**
         4298  +** The COLLATE and sort order terms were ignored.  To prevent compatibility
         4299  +** problems in case something like this appears in a legacy sqlite_master
         4300  +** table, only enforce the restriction on new SQL statements, not when
         4301  +** parsing the schema out of the sqlite_master table.
         4302  +*/
         4303  +void sqlite3RestrictColumnListSyntax(Parse *pParse, ExprList *p){
         4304  +  int i;
         4305  +  if( p==0 || pParse->db->init.busy ) return;
         4306  +  for(i=0; i<p->nExpr; i++){
         4307  +    if( p->a[i].pExpr!=0 || p->a[i].bDefinedSO ){
         4308  +      sqlite3ErrorMsg(pParse, "syntax error after column name \"%w\"",
         4309  +                      p->a[i].zName);
         4310  +      return;
         4311  +    }
         4312  +  }
         4313  +}
  4285   4314   
  4286   4315   #ifndef SQLITE_OMIT_CTE
  4287   4316   /* 
  4288   4317   ** This routine is invoked once per CTE by the parser while parsing a 
  4289   4318   ** WITH clause. 
  4290   4319   */
  4291   4320   With *sqlite3WithAdd(
................................................................................
  4294   4323     Token *pName,           /* Name of the common-table */
  4295   4324     ExprList *pArglist,     /* Optional column name list for the table */
  4296   4325     Select *pQuery          /* Query used to initialize the table */
  4297   4326   ){
  4298   4327     sqlite3 *db = pParse->db;
  4299   4328     With *pNew;
  4300   4329     char *zName;
         4330  +
         4331  +  sqlite3RestrictColumnListSyntax(pParse, pArglist);
  4301   4332   
  4302   4333     /* Check that the CTE name is unique within this WITH clause. If
  4303   4334     ** not, store an error in the Parse structure. */
  4304   4335     zName = sqlite3NameFromToken(pParse->db, pName);
  4305   4336     if( zName && pWith ){
  4306   4337       int i;
  4307   4338       for(i=0; i<pWith->nCte; i++){

Changes to src/expr.c.

  1155   1155   
  1156   1156   no_mem:     
  1157   1157     /* Avoid leaking memory if malloc has failed. */
  1158   1158     sqlite3ExprDelete(db, pExpr);
  1159   1159     sqlite3ExprListDelete(db, pList);
  1160   1160     return 0;
  1161   1161   }
         1162  +
         1163  +/*
         1164  +** Set the sort order for the last element on the given ExprList.
         1165  +*/
         1166  +void sqlite3ExprListSetSortOrder(ExprList *p, int iSortOrder){
         1167  +  if( p==0 ) return;
         1168  +  assert( SQLITE_SO_UNDEFINED<0 && SQLITE_SO_ASC>=0 && SQLITE_SO_DESC>0 );
         1169  +  assert( p->nExpr>0 );
         1170  +  if( iSortOrder<0 ){
         1171  +    assert( p->a[p->nExpr-1].sortOrder==SQLITE_SO_ASC );
         1172  +    return;
         1173  +  }
         1174  +  p->a[p->nExpr-1].sortOrder = (u8)iSortOrder;
         1175  +  p->a[p->nExpr-1].bDefinedSO = 1;
         1176  +}
  1162   1177   
  1163   1178   /*
  1164   1179   ** Set the ExprList.a[].zName element of the most recently added item
  1165   1180   ** on the expression list.
  1166   1181   **
  1167   1182   ** pList might be NULL following an OOM error.  But pName should never be
  1168   1183   ** NULL.  If a memory allocation fails, the pParse->db->mallocFailed flag

Changes to src/parse.y.

   676    676   %type sortlist {ExprList*}
   677    677   %destructor sortlist {sqlite3ExprListDelete(pParse->db, $$);}
   678    678   
   679    679   orderby_opt(A) ::= .                          {A = 0;}
   680    680   orderby_opt(A) ::= ORDER BY sortlist(X).      {A = X;}
   681    681   sortlist(A) ::= sortlist(X) COMMA expr(Y) sortorder(Z). {
   682    682     A = sqlite3ExprListAppend(pParse,X,Y.pExpr);
   683         -  if( A ) A->a[A->nExpr-1].sortOrder = (u8)Z;
          683  +  sqlite3ExprListSetSortOrder(A,Z);
   684    684   }
   685    685   sortlist(A) ::= expr(Y) sortorder(Z). {
   686    686     A = sqlite3ExprListAppend(pParse,0,Y.pExpr);
   687         -  if( A && ALWAYS(A->a) ) A->a[0].sortOrder = (u8)Z;
          687  +  sqlite3ExprListSetSortOrder(A,Z);
   688    688   }
   689    689   
   690    690   %type sortorder {int}
   691    691   
   692    692   sortorder(A) ::= ASC.           {A = SQLITE_SO_ASC;}
   693    693   sortorder(A) ::= DESC.          {A = SQLITE_SO_DESC;}
   694         -sortorder(A) ::= .              {A = SQLITE_SO_ASC;}
          694  +sortorder(A) ::= .              {A = SQLITE_SO_UNDEFINED;}
   695    695   
   696    696   %type groupby_opt {ExprList*}
   697    697   %destructor groupby_opt {sqlite3ExprListDelete(pParse->db, $$);}
   698    698   groupby_opt(A) ::= .                      {A = 0;}
   699    699   groupby_opt(A) ::= GROUP BY nexprlist(X). {A = X;}
   700    700   
   701    701   %type having_opt {Expr*}
................................................................................
  1225   1225   idxlist_opt(A) ::= .                         {A = 0;}
  1226   1226   idxlist_opt(A) ::= LP idxlist(X) RP.         {A = X;}
  1227   1227   idxlist(A) ::= idxlist(X) COMMA nm(Y) collate(C) sortorder(Z).  {
  1228   1228     Expr *p = sqlite3ExprAddCollateToken(pParse, 0, &C, 1);
  1229   1229     A = sqlite3ExprListAppend(pParse,X, p);
  1230   1230     sqlite3ExprListSetName(pParse,A,&Y,1);
  1231   1231     sqlite3ExprListCheckLength(pParse, A, "index");
  1232         -  if( A ) A->a[A->nExpr-1].sortOrder = (u8)Z;
         1232  +  sqlite3ExprListSetSortOrder(A,Z);
  1233   1233   }
  1234   1234   idxlist(A) ::= nm(Y) collate(C) sortorder(Z). {
  1235   1235     Expr *p = sqlite3ExprAddCollateToken(pParse, 0, &C, 1);
  1236   1236     A = sqlite3ExprListAppend(pParse,0, p);
  1237   1237     sqlite3ExprListSetName(pParse, A, &Y, 1);
  1238   1238     sqlite3ExprListCheckLength(pParse, A, "index");
  1239         -  if( A ) A->a[A->nExpr-1].sortOrder = (u8)Z;
         1239  +  sqlite3ExprListSetSortOrder(A,Z);
  1240   1240   }
  1241   1241   
  1242   1242   %type collate {Token}
  1243   1243   collate(C) ::= .                 {C.z = 0; C.n = 0;}
  1244   1244   collate(C) ::= COLLATE ids(X).   {C = X;}
  1245   1245   
  1246   1246   

Changes to src/sqliteInt.h.

  1521   1521   };
  1522   1522   
  1523   1523   /*
  1524   1524   ** A sort order can be either ASC or DESC.
  1525   1525   */
  1526   1526   #define SQLITE_SO_ASC       0  /* Sort in ascending order */
  1527   1527   #define SQLITE_SO_DESC      1  /* Sort in ascending order */
         1528  +#define SQLITE_SO_UNDEFINED -1 /* No sort order specified */
  1528   1529   
  1529   1530   /*
  1530   1531   ** Column affinity types.
  1531   1532   **
  1532   1533   ** These used to have mnemonic name like 'i' for SQLITE_AFF_INTEGER and
  1533   1534   ** 't' for SQLITE_AFF_TEXT.  But we can save a little space and improve
  1534   1535   ** the speed a little by numbering the values consecutively.  
................................................................................
  2185   2186       Expr *pExpr;            /* The list of expressions */
  2186   2187       char *zName;            /* Token associated with this expression */
  2187   2188       char *zSpan;            /* Original text of the expression */
  2188   2189       u8 sortOrder;           /* 1 for DESC or 0 for ASC */
  2189   2190       unsigned done :1;       /* A flag to indicate when processing is finished */
  2190   2191       unsigned bSpanIsTab :1; /* zSpan holds DB.TABLE.COLUMN */
  2191   2192       unsigned reusable :1;   /* Constant expression is reusable */
         2193  +    unsigned bDefinedSO :1; /* True if either DESC or ASC keywords present */
  2192   2194       union {
  2193   2195         struct {
  2194   2196           u16 iOrderByCol;      /* For ORDER BY, column number in result set */
  2195   2197           u16 iAlias;           /* Index into Parse.aAlias[] for zName */
  2196   2198         } x;
  2197   2199         int iConstExprReg;      /* Register in which Expr value is cached */
  2198   2200       } u;
................................................................................
  3240   3242   void sqlite3ExprAttachSubtrees(sqlite3*,Expr*,Expr*,Expr*);
  3241   3243   Expr *sqlite3PExpr(Parse*, int, Expr*, Expr*, const Token*);
  3242   3244   Expr *sqlite3ExprAnd(sqlite3*,Expr*, Expr*);
  3243   3245   Expr *sqlite3ExprFunction(Parse*,ExprList*, Token*);
  3244   3246   void sqlite3ExprAssignVarNumber(Parse*, Expr*);
  3245   3247   void sqlite3ExprDelete(sqlite3*, Expr*);
  3246   3248   ExprList *sqlite3ExprListAppend(Parse*,ExprList*,Expr*);
         3249  +void sqlite3ExprListSetSortOrder(ExprList*,int);
  3247   3250   void sqlite3ExprListSetName(Parse*,ExprList*,Token*,int);
  3248   3251   void sqlite3ExprListSetSpan(Parse*,ExprList*,ExprSpan*);
  3249   3252   void sqlite3ExprListDelete(sqlite3*, ExprList*);
  3250   3253   u32 sqlite3ExprListFlags(const ExprList*);
  3251   3254   int sqlite3Init(sqlite3*, char**);
  3252   3255   int sqlite3InitCallback(void*, int, char**, char**);
  3253   3256   void sqlite3Pragma(Parse*,Token*,Token*,Token*,int);
................................................................................
  3751   3754   CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *);
  3752   3755   int sqlite3TempInMemory(const sqlite3*);
  3753   3756   const char *sqlite3JournalModename(int);
  3754   3757   #ifndef SQLITE_OMIT_WAL
  3755   3758     int sqlite3Checkpoint(sqlite3*, int, int, int*, int*);
  3756   3759     int sqlite3WalDefaultHook(void*,sqlite3*,const char*,int);
  3757   3760   #endif
         3761  +void sqlite3RestrictColumnListSyntax(Parse*,ExprList*);
  3758   3762   #ifndef SQLITE_OMIT_CTE
  3759   3763     With *sqlite3WithAdd(Parse*,With*,Token*,ExprList*,Select*);
  3760   3764     void sqlite3WithDelete(sqlite3*,With*);
  3761   3765     void sqlite3WithPush(Parse*, With*, u8);
  3762   3766   #else
  3763   3767   #define sqlite3WithPush(x,y,z)
  3764   3768   #define sqlite3WithDelete(x,y)

Added test/parser1.test.

            1  +# 2014-08-24
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +# The focus of this script is testing details of the SQL language parser.
           13  +# 
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +
           18  +do_catchsql_test parser1-1.1 {
           19  +  CREATE TABLE t1(
           20  +    a TEXT PRIMARY KEY,
           21  +    b TEXT,
           22  +    FOREIGN KEY(b COLLATE nocase DESC) REFERENCES t1(a COLLATE binary ASC)
           23  +  );
           24  +} {1 {syntax error after column name "a"}}
           25  +do_execsql_test parser1-1.2 {
           26  +  CREATE TABLE t1(
           27  +    a TEXT PRIMARY KEY,
           28  +    b TEXT,
           29  +    FOREIGN KEY(b) REFERENCES t1(a)
           30  +  );
           31  +  INSERT INTO t1 VALUES('abc',NULL),('xyz','abc');
           32  +  PRAGMA writable_schema=on;
           33  +  UPDATE sqlite_master SET sql='CREATE TABLE t1(
           34  +    a TEXT PRIMARY KEY,
           35  +    b TEXT,
           36  +    FOREIGN KEY(b COLLATE nocase) REFERENCES t1(a)
           37  +  )' WHERE name='t1';
           38  +  SELECT name FROM sqlite_master WHERE sql LIKE '%collate%';
           39  +} {t1}
           40  +sqlite3 db2 test.db
           41  +do_test parser1-1.3 {
           42  +  sqlite3 db2 test.db
           43  +  db2 eval {SELECT * FROM t1 ORDER BY 1}
           44  +} {abc {} xyz abc}
           45  +
           46  +
           47  +do_catchsql_test parser1-2.1 {
           48  +  WITH RECURSIVE
           49  +    c(x COLLATE binary) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5)
           50  +  SELECT x FROM c;
           51  +} {1 {syntax error after column name "x"}}
           52  +do_catchsql_test parser1-2.2 {
           53  +  WITH RECURSIVE
           54  +    c(x ASC) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<5)
           55  +  SELECT x FROM c;
           56  +} {1 {syntax error after column name "x"}}
           57  +
           58  +finish_test