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: |
9e3f64a4f4182ad80e82edb53095ed50 |
| 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
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