/ Check-in [9e3f64a4]
Login

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

Overview
Comment:Ensure that tables names are dequoted exactly once by the trigger logic. Cherrypick [59e92bd9521f1e8] and [9d887b92f8086961e].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.7.11
Files: files | file ages | folders
SHA1: 9e3f64a4f4182ad80e82edb53095ed508e8b1d13
User & Date: drh 2015-05-21 02:20:47
Context
2015-05-21
02:24
Silently ignore any attempt to add a prefix index for prefixes zero bytes in size to an fts3/4 table. Or any prefix index size so large that it overflows a 32-bit signed integer. Cherrypick [ad4b19d2ac0889a] check-in: 000197cc user: drh tags: branch-3.7.11
02:20
Ensure that tables names are dequoted exactly once by the trigger logic. Cherrypick [59e92bd9521f1e8] and [9d887b92f8086961e]. check-in: 9e3f64a4 user: drh tags: branch-3.7.11
02:07
When parsing the schema, ignore any SQL that does not begin with "CREATE". Cherrypick of [d3c00d61581c] with additional changes. check-in: 09784f37 user: drh tags: branch-3.7.11
2015-04-21
16:38
Ensure that tables names are dequoted exactly once by the trigger logic. check-in: 9d887b92 user: dan tags: trunk
03:13
Fix some identifier name de-quoting issues in the foreign key and trigger logic. check-in: 59e92bd9 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/fkey.c.

  1014   1014   
  1015   1015         /* Create the expression "OLD.zToCol = zFromCol". It is important
  1016   1016         ** that the "OLD.zToCol" term is on the LHS of the = operator, so
  1017   1017         ** that the affinity and collation sequence associated with the
  1018   1018         ** parent table are used for the comparison. */
  1019   1019         pEq = sqlite3PExpr(pParse, TK_EQ,
  1020   1020             sqlite3PExpr(pParse, TK_DOT, 
  1021         -            sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld),
  1022         -            sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
         1021  +            sqlite3ExprAlloc(db, TK_ID, &tOld, 0),
         1022  +            sqlite3ExprAlloc(db, TK_ID, &tToCol, 0)
  1023   1023             , 0),
  1024         -          sqlite3PExpr(pParse, TK_ID, 0, 0, &tFromCol)
         1024  +          sqlite3ExprAlloc(db, TK_ID, &tFromCol, 0)
  1025   1025         , 0);
  1026   1026         pWhere = sqlite3ExprAnd(db, pWhere, pEq);
  1027   1027   
  1028   1028         /* For ON UPDATE, construct the next term of the WHEN clause.
  1029   1029         ** The final WHEN clause will be like this:
  1030   1030         **
  1031   1031         **    WHEN NOT(old.col1 IS new.col1 AND ... AND old.colN IS new.colN)
  1032   1032         */
  1033   1033         if( pChanges ){
  1034   1034           pEq = sqlite3PExpr(pParse, TK_IS,
  1035   1035               sqlite3PExpr(pParse, TK_DOT, 
  1036         -              sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld),
  1037         -              sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol),
         1036  +              sqlite3ExprAlloc(db, TK_ID, &tOld, 0),
         1037  +              sqlite3ExprAlloc(db, TK_ID, &tToCol, 0),
  1038   1038                 0),
  1039   1039               sqlite3PExpr(pParse, TK_DOT, 
  1040         -              sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew),
  1041         -              sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol),
         1040  +              sqlite3ExprAlloc(db, TK_ID, &tNew, 0),
         1041  +              sqlite3ExprAlloc(db, TK_ID, &tToCol, 0),
  1042   1042                 0),
  1043   1043               0);
  1044   1044           pWhen = sqlite3ExprAnd(db, pWhen, pEq);
  1045   1045         }
  1046   1046     
  1047   1047         if( action!=OE_Restrict && (action!=OE_Cascade || pChanges) ){
  1048   1048           Expr *pNew;
  1049   1049           if( action==OE_Cascade ){
  1050   1050             pNew = sqlite3PExpr(pParse, TK_DOT, 
  1051         -            sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew),
  1052         -            sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
         1051  +            sqlite3ExprAlloc(db, TK_ID, &tNew, 0),
         1052  +            sqlite3ExprAlloc(db, TK_ID, &tToCol, 0)
  1053   1053             , 0);
  1054   1054           }else if( action==OE_SetDflt ){
  1055   1055             Expr *pDflt = pFKey->pFrom->aCol[iFromCol].pDflt;
  1056   1056             if( pDflt ){
  1057   1057               pNew = sqlite3ExprDup(db, pDflt, 0);
  1058   1058             }else{
  1059   1059               pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0);
................................................................................
  1092   1092       /* Disable lookaside memory allocation */
  1093   1093       enableLookaside = db->lookaside.bEnabled;
  1094   1094       db->lookaside.bEnabled = 0;
  1095   1095   
  1096   1096       pTrigger = (Trigger *)sqlite3DbMallocZero(db, 
  1097   1097           sizeof(Trigger) +         /* struct Trigger */
  1098   1098           sizeof(TriggerStep) +     /* Single step in trigger program */
  1099         -        nFrom + 1                 /* Space for pStep->target.z */
         1099  +        nFrom + 1                 /* Space for pStep->zTarget */
  1100   1100       );
  1101   1101       if( pTrigger ){
  1102   1102         pStep = pTrigger->step_list = (TriggerStep *)&pTrigger[1];
  1103         -      pStep->target.z = (char *)&pStep[1];
  1104         -      pStep->target.n = nFrom;
  1105         -      memcpy((char *)pStep->target.z, zFrom, nFrom);
         1103  +      pStep->zTarget = (char *)&pStep[1];
         1104  +      memcpy((char *)pStep->zTarget, zFrom, nFrom);
  1106   1105     
  1107   1106         pStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE);
  1108   1107         pStep->pExprList = sqlite3ExprListDup(db, pList, EXPRDUP_REDUCE);
  1109   1108         pStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE);
  1110   1109         if( pWhen ){
  1111   1110           pWhen = sqlite3PExpr(pParse, TK_NOT, pWhen, 0, 0);
  1112   1111           pTrigger->pWhen = sqlite3ExprDup(db, pWhen, EXPRDUP_REDUCE);

Changes to src/sqliteInt.h.

  2337   2337    * "SELECT" statement. The meanings of the other members is determined by the 
  2338   2338    * value of "op" as follows:
  2339   2339    *
  2340   2340    * (op == TK_INSERT)
  2341   2341    * orconf    -> stores the ON CONFLICT algorithm
  2342   2342    * pSelect   -> If this is an INSERT INTO ... SELECT ... statement, then
  2343   2343    *              this stores a pointer to the SELECT statement. Otherwise NULL.
  2344         - * target    -> A token holding the quoted name of the table to insert into.
         2344  + * zTarget   -> Dequoted name of the table to insert into.
  2345   2345    * pExprList -> If this is an INSERT INTO ... VALUES ... statement, then
  2346   2346    *              this stores values to be inserted. Otherwise NULL.
  2347   2347    * pIdList   -> If this is an INSERT INTO ... (<column-names>) VALUES ... 
  2348   2348    *              statement, then this stores the column-names to be
  2349   2349    *              inserted into.
  2350   2350    *
  2351   2351    * (op == TK_DELETE)
  2352         - * target    -> A token holding the quoted name of the table to delete from.
         2352  + * zTarget   -> Dequoted name of the table to delete from.
  2353   2353    * pWhere    -> The WHERE clause of the DELETE statement if one is specified.
  2354   2354    *              Otherwise NULL.
  2355   2355    * 
  2356   2356    * (op == TK_UPDATE)
  2357         - * target    -> A token holding the quoted name of the table to update rows of.
         2357  + * zTarget   -> Dequoted name of the table to update.
  2358   2358    * pWhere    -> The WHERE clause of the UPDATE statement if one is specified.
  2359   2359    *              Otherwise NULL.
  2360   2360    * pExprList -> A list of the columns to update and the expressions to update
  2361   2361    *              them to. See sqlite3Update() documentation of "pChanges"
  2362   2362    *              argument.
  2363   2363    * 
  2364   2364    */
  2365   2365   struct TriggerStep {
  2366   2366     u8 op;               /* One of TK_DELETE, TK_UPDATE, TK_INSERT, TK_SELECT */
  2367   2367     u8 orconf;           /* OE_Rollback etc. */
  2368   2368     Trigger *pTrig;      /* The trigger that this step is a part of */
  2369         -  Select *pSelect;     /* SELECT statment or RHS of INSERT INTO .. SELECT ... */
  2370         -  Token target;        /* Target table for DELETE, UPDATE, INSERT */
         2369  +  Select *pSelect;     /* SELECT statement or RHS of INSERT INTO SELECT ... */
         2370  +  char *zTarget;       /* Target table for DELETE, UPDATE, INSERT */
  2371   2371     Expr *pWhere;        /* The WHERE clause for DELETE or UPDATE steps */
  2372   2372     ExprList *pExprList; /* SET clause for UPDATE.  VALUES clause for INSERT */
  2373   2373     IdList *pIdList;     /* Column names for INSERT */
  2374   2374     TriggerStep *pNext;  /* Next in the link-list */
  2375   2375     TriggerStep *pLast;  /* Last element in link-list. Valid for 1st elem only */
  2376   2376   };
  2377   2377   

Changes to src/trigger.c.

   369    369   static TriggerStep *triggerStepAllocate(
   370    370     sqlite3 *db,                /* Database connection */
   371    371     u8 op,                      /* Trigger opcode */
   372    372     Token *pName                /* The target name */
   373    373   ){
   374    374     TriggerStep *pTriggerStep;
   375    375   
   376         -  pTriggerStep = sqlite3DbMallocZero(db, sizeof(TriggerStep) + pName->n);
          376  +  pTriggerStep = sqlite3DbMallocZero(db, sizeof(TriggerStep) + pName->n + 1);
   377    377     if( pTriggerStep ){
   378    378       char *z = (char*)&pTriggerStep[1];
   379    379       memcpy(z, pName->z, pName->n);
   380         -    pTriggerStep->target.z = z;
   381         -    pTriggerStep->target.n = pName->n;
          380  +    sqlite3Dequote(z);
          381  +    pTriggerStep->zTarget = z;
   382    382       pTriggerStep->op = op;
   383    383     }
   384    384     return pTriggerStep;
   385    385   }
   386    386   
   387    387   /*
   388    388   ** Build a trigger step out of an INSERT statement.  Return a pointer
................................................................................
   663    663     if( pMask ){
   664    664       *pMask = mask;
   665    665     }
   666    666     return (mask ? pList : 0);
   667    667   }
   668    668   
   669    669   /*
   670         -** Convert the pStep->target token into a SrcList and return a pointer
          670  +** Convert the pStep->zTarget string into a SrcList and return a pointer
   671    671   ** to that SrcList.
   672    672   **
   673    673   ** This routine adds a specific database name, if needed, to the target when
   674    674   ** forming the SrcList.  This prevents a trigger in one database from
   675    675   ** referring to a target in another database.  An exception is when the
   676    676   ** trigger is in TEMP in which case it can refer to any other database it
   677    677   ** wants.
   678    678   */
   679    679   static SrcList *targetSrcList(
   680    680     Parse *pParse,       /* The parsing context */
   681    681     TriggerStep *pStep   /* The trigger containing the target token */
   682    682   ){
          683  +  sqlite3 *db = pParse->db;
   683    684     int iDb;             /* Index of the database to use */
   684    685     SrcList *pSrc;       /* SrcList to be returned */
   685    686   
   686         -  pSrc = sqlite3SrcListAppend(pParse->db, 0, &pStep->target, 0);
          687  +  pSrc = sqlite3SrcListAppend(db, 0, 0, 0);
   687    688     if( pSrc ){
   688    689       assert( pSrc->nSrc>0 );
   689         -    assert( pSrc->a!=0 );
   690         -    iDb = sqlite3SchemaToIndex(pParse->db, pStep->pTrig->pSchema);
          690  +    pSrc->a[pSrc->nSrc-1].zName = sqlite3DbStrDup(db, pStep->zTarget);
          691  +    iDb = sqlite3SchemaToIndex(db, pStep->pTrig->pSchema);
   691    692       if( iDb==0 || iDb>=2 ){
   692         -      sqlite3 *db = pParse->db;
   693         -      assert( iDb<pParse->db->nDb );
          693  +      assert( iDb<db->nDb );
   694    694         pSrc->a[pSrc->nSrc-1].zDatabase = sqlite3DbStrDup(db, db->aDb[iDb].zName);
   695    695       }
   696    696     }
   697    697     return pSrc;
   698    698   }
   699    699   
   700    700   /*

Changes to test/fkey1.test.

   113    113       );
   114    114       PRAGMA foreign_key_list(t9);
   115    115     }
   116    116   } [concat                        \
   117    117     {0 0 t5 d {} {SET DEFAULT} CASCADE NONE} \
   118    118     {0 1 t5 e {} {SET DEFAULT} CASCADE NONE} \
   119    119   ]
          120  +
          121  +# Stress the dequoting logic.  The first test is not so bad.
          122  +do_execsql_test fkey1-4.0 {
          123  +  PRAGMA foreign_keys=ON;
          124  +  CREATE TABLE "xx1"("xx2" TEXT PRIMARY KEY, "xx3" TEXT);
          125  +  INSERT INTO "xx1"("xx2","xx3") VALUES('abc','def');
          126  +  CREATE TABLE "xx4"("xx5" TEXT REFERENCES "xx1" ON DELETE CASCADE);
          127  +  INSERT INTO "xx4"("xx5") VALUES('abc');
          128  +  INSERT INTO "xx1"("xx2","xx3") VALUES('uvw','xyz');
          129  +  SELECT 1, "xx5" FROM "xx4";
          130  +  DELETE FROM "xx1";
          131  +  SELECT 2, "xx5" FROM "xx4";
          132  +} {1 abc}
          133  +
          134  +# This case is identical to the previous except the "xx" in each name
          135  +# is changed to a single escaped double-quote character.
          136  +do_execsql_test fkey1-4.1 {
          137  +  PRAGMA foreign_keys=ON;
          138  +  CREATE TABLE """1"("""2" TEXT PRIMARY KEY, """3" TEXT);
          139  +  INSERT INTO """1"("""2","""3") VALUES('abc','def');
          140  +  CREATE TABLE """4"("""5" TEXT REFERENCES """1" ON DELETE CASCADE);
          141  +  INSERT INTO """4"("""5") VALUES('abc');
          142  +  INSERT INTO """1"("""2","""3") VALUES('uvw','xyz');
          143  +  SELECT 1, """5" FROM """4";
          144  +  DELETE FROM """1";
          145  +  SELECT 2, """5" FROM """4";
          146  +} {1 abc}
          147  +do_execsql_test fkey1-4.2 {
          148  +  PRAGMA table_info="""1";
          149  +} {0 {"2} TEXT 0 {} 1 1 {"3} TEXT 0 {} 0}
   120    150   
   121    151   finish_test

Changes to test/triggerC.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   #
    12     12   
    13     13   set testdir [file dirname $argv0]
    14     14   source $testdir/tester.tcl
           15  +set testprefix triggerC
    15     16   ifcapable {!trigger} {
    16     17     finish_test
    17     18     return
    18     19   }
    19     20   
    20     21   #-------------------------------------------------------------------------
    21     22   # Test organization:
................................................................................
   946    947     END;
   947    948   } {}
   948    949   do_catchsql_test triggerC-13.2 {
   949    950     UPDATE t12 SET a=a+1, b=b+1;
   950    951   } {1 {too many levels of trigger recursion}}
   951    952   
   952    953   
          954  +
          955  +#-------------------------------------------------------------------------
          956  +# Check that table names used by trigger programs are dequoted exactly
          957  +# once.
          958  +#
          959  +do_execsql_test 15.1.1 {
          960  +  PRAGMA recursive_triggers = 1;
          961  +  CREATE TABLE node(
          962  +      id int not null primary key, 
          963  +      pid int not null default 0 references node,
          964  +      key varchar not null, 
          965  +      path varchar default '',
          966  +      unique(pid, key)
          967  +      );
          968  +  CREATE TRIGGER node_delete_referencing AFTER DELETE ON "node"
          969  +    BEGIN
          970  +    DELETE FROM "node" WHERE pid = old."id";
          971  +  END;
          972  +}
          973  +do_execsql_test 15.1.2 {
          974  +  INSERT INTO node(id, pid, key) VALUES(9, 0, 'test');
          975  +  INSERT INTO node(id, pid, key) VALUES(90, 9, 'test1');
          976  +  INSERT INTO node(id, pid, key) VALUES(900, 90, 'test2');
          977  +  DELETE FROM node WHERE id=9;
          978  +  SELECT * FROM node;
          979  +}
          980  +
          981  +do_execsql_test 15.2.1 {
          982  +  CREATE TABLE   x1  (x);
          983  +
          984  +  CREATE TABLE   x2  (a, b);
          985  +  CREATE TABLE '"x2"'(a, b);
          986  +
          987  +  INSERT INTO x2 VALUES(1, 2);
          988  +  INSERT INTO x2 VALUES(3, 4);
          989  +  INSERT INTO '"x2"' SELECT * FROM x2;
          990  +
          991  +  CREATE TRIGGER x1ai AFTER INSERT ON x1 BEGIN
          992  +    INSERT INTO """x2""" VALUES('x', 'y');
          993  +    DELETE FROM """x2""" WHERE a=1;
          994  +    UPDATE """x2""" SET b = 11 WHERE a = 3;
          995  +  END;
          996  +
          997  +  INSERT INTO x1 VALUES('go!');
          998  +}
          999  +
         1000  +do_execsql_test 15.2.2 { SELECT * FROM x2;       } {1 2 3 4}
         1001  +do_execsql_test 15.2.3 { SELECT * FROM """x2"""; } {3 11 x y}
   953   1002   
   954   1003   finish_test