Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Handle an SQLITE_IGNORE returned when requesting authorization to read parent key columns by pretending the parent key columns contain NULL values. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
3c24df38e6ae5dfe999bbf3133b65df0 |
User & Date: | dan 2009-10-03 07:04:12.000 |
Context
2009-10-05
| ||
05:40 | Fix some errors in the guttman versions (disabled by default) of the algorithms in rtree.c. (check-in: 64bad00b4f user: dan tags: trunk) | |
2009-10-03
| ||
07:04 | Handle an SQLITE_IGNORE returned when requesting authorization to read parent key columns by pretending the parent key columns contain NULL values. (check-in: 3c24df38e6 user: dan tags: trunk) | |
2009-10-02
| ||
15:29 | Add one more authentication test to fkey2.test to cover an untested branch. (check-in: e4fa8be770 user: dan tags: trunk) | |
Changes
Changes to src/auth.c.
︙ | ︙ | |||
96 97 98 99 100 101 102 | ** table zTab in database zDb. This function assumes that an authorization ** callback has been registered (i.e. that sqlite3.xAuth is not NULL). ** ** If SQLITE_IGNORE is returned and pExpr is not NULL, then pExpr is changed ** to an SQL NULL expression. Otherwise, if pExpr is NULL, then SQLITE_IGNORE ** is treated as SQLITE_DENY. In this case an error is left in pParse. */ | | | < < < | < < > > > | 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 | ** table zTab in database zDb. This function assumes that an authorization ** callback has been registered (i.e. that sqlite3.xAuth is not NULL). ** ** If SQLITE_IGNORE is returned and pExpr is not NULL, then pExpr is changed ** to an SQL NULL expression. Otherwise, if pExpr is NULL, then SQLITE_IGNORE ** is treated as SQLITE_DENY. In this case an error is left in pParse. */ int sqlite3AuthReadCol( Parse *pParse, /* The parser context */ const char *zTab, /* Table name */ const char *zCol, /* Column name */ int iDb /* Index of containing database. */ ){ sqlite3 *db = pParse->db; /* Database handle */ char *zDb = db->aDb[iDb].zName; /* Name of attached database */ int rc; /* Auth callback return code */ rc = db->xAuth(db->pAuthArg, SQLITE_READ, zTab,zCol,zDb,pParse->zAuthContext); if( rc==SQLITE_DENY ){ if( db->nDb>2 || iDb!=0 ){ sqlite3ErrorMsg(pParse, "access to %s.%s.%s is prohibited",zDb,zTab,zCol); }else{ sqlite3ErrorMsg(pParse, "access to %s.%s is prohibited", zTab, zCol); } pParse->rc = SQLITE_AUTH; }else if( rc!=SQLITE_IGNORE && rc!=SQLITE_OK ){ sqliteAuthBadReturnCode(pParse); } return rc; } /* ** The pExpr should be a TK_COLUMN expression. The table referred to ** is in pTabList or else it is the NEW or OLD table of a trigger. ** Check to see if it is OK to read this particular column. ** |
︙ | ︙ | |||
177 178 179 180 181 182 183 | }else if( pTab->iPKey>=0 ){ assert( pTab->iPKey<pTab->nCol ); zCol = pTab->aCol[pTab->iPKey].zName; }else{ zCol = "ROWID"; } assert( iDb>=0 && iDb<db->nDb ); | | > > | 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 | }else if( pTab->iPKey>=0 ){ assert( pTab->iPKey<pTab->nCol ); zCol = pTab->aCol[pTab->iPKey].zName; }else{ zCol = "ROWID"; } assert( iDb>=0 && iDb<db->nDb ); if( SQLITE_IGNORE==sqlite3AuthReadCol(pParse, pTab->zName, zCol, iDb) ){ pExpr->op = TK_NULL; } } /* ** Do an authorization check using the code and arguments given. Return ** either SQLITE_OK (zero) or SQLITE_IGNORE or SQLITE_DENY. If SQLITE_DENY ** is returned, then the error count and error message in pParse are ** modified appropriately. |
︙ | ︙ |
Changes to src/fkey.c.
︙ | ︙ | |||
315 316 317 318 319 320 321 | Parse *pParse, /* Parse context */ int iDb, /* Index of database housing pTab */ Table *pTab, /* Parent table of FK pFKey */ Index *pIdx, /* Unique index on parent key columns in pTab */ FKey *pFKey, /* Foreign key constraint */ int *aiCol, /* Map from parent key columns to child table columns */ int regData, /* Address of array containing child table row */ | | > | 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 | Parse *pParse, /* Parse context */ int iDb, /* Index of database housing pTab */ Table *pTab, /* Parent table of FK pFKey */ Index *pIdx, /* Unique index on parent key columns in pTab */ FKey *pFKey, /* Foreign key constraint */ int *aiCol, /* Map from parent key columns to child table columns */ int regData, /* Address of array containing child table row */ int nIncr, /* Increment constraint counter by this */ int isIgnore /* If true, pretend pTab contains all NULL values */ ){ int i; /* Iterator variable */ Vdbe *v = sqlite3GetVdbe(pParse); /* Vdbe to add code to */ int iCur = pParse->nTab - 1; /* Cursor number to use */ int iOk = sqlite3VdbeMakeLabel(v); /* jump here if parent key found */ /* If nIncr is less than zero, then check at runtime if there are any |
︙ | ︙ | |||
337 338 339 340 341 342 343 | sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, iOk); } for(i=0; i<pFKey->nCol; i++){ int iReg = aiCol[i] + regData + 1; sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk); } | > | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | > | 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 | sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, iOk); } for(i=0; i<pFKey->nCol; i++){ int iReg = aiCol[i] + regData + 1; sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk); } if( isIgnore==0 ){ if( pIdx==0 ){ /* If pIdx is NULL, then the parent key is the INTEGER PRIMARY KEY ** column of the parent table (table pTab). */ int iMustBeInt; /* Address of MustBeInt instruction */ int regTemp = sqlite3GetTempReg(pParse); /* Invoke MustBeInt to coerce the child key value to an integer (i.e. ** apply the affinity of the parent key). If this fails, then there ** is no matching parent key. Before using MustBeInt, make a copy of ** the value. Otherwise, the value inserted into the child key column ** will have INTEGER affinity applied to it, which may not be correct. */ sqlite3VdbeAddOp2(v, OP_SCopy, aiCol[0]+1+regData, regTemp); iMustBeInt = sqlite3VdbeAddOp2(v, OP_MustBeInt, regTemp, 0); /* If the parent table is the same as the child table, and we are about ** to increment the constraint-counter (i.e. this is an INSERT operation), ** then check if the row being inserted matches itself. If so, do not ** increment the constraint-counter. */ if( pTab==pFKey->pFrom && nIncr==1 ){ sqlite3VdbeAddOp3(v, OP_Eq, regData, iOk, regTemp); } sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead); sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regTemp); sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk); sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2); sqlite3VdbeJumpHere(v, iMustBeInt); sqlite3ReleaseTempReg(pParse, regTemp); }else{ int nCol = pFKey->nCol; int regTemp = sqlite3GetTempRange(pParse, nCol); int regRec = sqlite3GetTempReg(pParse); KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx); sqlite3VdbeAddOp3(v, OP_OpenRead, iCur, pIdx->tnum, iDb); sqlite3VdbeChangeP4(v, -1, (char*)pKey, P4_KEYINFO_HANDOFF); for(i=0; i<nCol; i++){ sqlite3VdbeAddOp2(v, OP_SCopy, aiCol[i]+1+regData, regTemp+i); } /* If the parent table is the same as the child table, and we are about ** to increment the constraint-counter (i.e. this is an INSERT operation), ** then check if the row being inserted matches itself. If so, do not ** increment the constraint-counter. */ if( pTab==pFKey->pFrom && nIncr==1 ){ int iJump = sqlite3VdbeCurrentAddr(v) + nCol + 1; for(i=0; i<nCol; i++){ int iChild = aiCol[i]+1+regData; int iParent = pIdx->aiColumn[i]+1+regData; sqlite3VdbeAddOp3(v, OP_Ne, iChild, iJump, iParent); } sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk); } sqlite3VdbeAddOp3(v, OP_MakeRecord, regTemp, nCol, regRec); sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), 0); sqlite3VdbeAddOp3(v, OP_Found, iCur, iOk, regRec); sqlite3ReleaseTempReg(pParse, regRec); sqlite3ReleaseTempRange(pParse, regTemp, nCol); } } if( !pFKey->isDeferred && !pParse->pToplevel && !pParse->isMultiWrite ){ /* Special case: If this is an INSERT statement that will insert exactly ** one row into the table, raise a constraint immediately instead of ** incrementing a counter. This is necessary as the VM code is being ** generated for will not open a statement transaction. */ |
︙ | ︙ | |||
702 703 704 705 706 707 708 709 710 711 712 713 714 715 | for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){ Table *pTo; /* Parent table of foreign key pFKey */ Index *pIdx = 0; /* Index on key columns in pTo */ int *aiFree = 0; int *aiCol; int iCol; int i; /* Find the parent table of this foreign key. Also find a unique index ** on the parent key columns in the parent table. If either of these ** schema items cannot be located, set an error in pParse and return ** early. */ if( pParse->disableTriggers ){ pTo = sqlite3FindTable(db, pFKey->zTo, zDb); | > | 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 | for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){ Table *pTo; /* Parent table of foreign key pFKey */ Index *pIdx = 0; /* Index on key columns in pTo */ int *aiFree = 0; int *aiCol; int iCol; int i; int isIgnore = 0; /* Find the parent table of this foreign key. Also find a unique index ** on the parent key columns in the parent table. If either of these ** schema items cannot be located, set an error in pParse and return ** early. */ if( pParse->disableTriggers ){ pTo = sqlite3FindTable(db, pFKey->zTo, zDb); |
︙ | ︙ | |||
729 730 731 732 733 734 735 | aiCol = &iCol; } for(i=0; i<pFKey->nCol; i++){ if( aiCol[i]==pTab->iPKey ){ aiCol[i] = -1; } #ifndef SQLITE_OMIT_AUTHORIZATION | | > > > | > | | | 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 | aiCol = &iCol; } for(i=0; i<pFKey->nCol; i++){ if( aiCol[i]==pTab->iPKey ){ aiCol[i] = -1; } #ifndef SQLITE_OMIT_AUTHORIZATION /* Request permission to read the parent key columns. If the ** authorization callback returns SQLITE_IGNORE, behave as if any ** values read from the parent table are NULL. */ if( db->xAuth ){ int rcauth; char *zCol = pTo->aCol[pIdx ? pIdx->aiColumn[i] : pTo->iPKey].zName; rcauth = sqlite3AuthReadCol(pParse, pTo->zName, zCol, iDb); isIgnore = (rcauth==SQLITE_IGNORE); } #endif } /* Take a shared-cache advisory read-lock on the parent table. Allocate ** a cursor to use to search the unique index on the parent key columns ** in the parent table. */ sqlite3TableLock(pParse, iDb, pTo->tnum, 0, pTo->zName); pParse->nTab++; if( regOld!=0 ){ /* A row is being removed from the child table. Search for the parent. ** If the parent does not exist, removing the child row resolves an ** outstanding foreign key constraint violation. */ fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1,isIgnore); } if( regNew!=0 ){ /* A row is being added to the child table. If a parent row cannot ** be found, adding the child row has violated the FK constraint. */ fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1,isIgnore); } sqlite3DbFree(db, aiFree); } /* Loop through all the foreign key constraints that refer to this table */ for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2730 2731 2732 2733 2734 2735 2736 | void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int); void sqlite3DeferForeignKey(Parse*, int); #ifndef SQLITE_OMIT_AUTHORIZATION void sqlite3AuthRead(Parse*,Expr*,Schema*,SrcList*); int sqlite3AuthCheck(Parse*,int, const char*, const char*, const char*); void sqlite3AuthContextPush(Parse*, AuthContext*, const char*); void sqlite3AuthContextPop(AuthContext*); | | | 2730 2731 2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 | void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int); void sqlite3DeferForeignKey(Parse*, int); #ifndef SQLITE_OMIT_AUTHORIZATION void sqlite3AuthRead(Parse*,Expr*,Schema*,SrcList*); int sqlite3AuthCheck(Parse*,int, const char*, const char*, const char*); void sqlite3AuthContextPush(Parse*, AuthContext*, const char*); void sqlite3AuthContextPop(AuthContext*); int sqlite3AuthReadCol(Parse*, const char *, const char *, int); #else # define sqlite3AuthRead(a,b,c,d) # define sqlite3AuthCheck(a,b,c,d,e) SQLITE_OK # define sqlite3AuthContextPush(a,b,c) # define sqlite3AuthContextPop(a) ((void)(a)) #endif void sqlite3Attach(Parse*, Expr*, Expr*, Expr*); |
︙ | ︙ |
Changes to test/fkey2.test.
︙ | ︙ | |||
1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 | INSERT INTO one VALUES(101, 102); } set authargs [list] execsql { INSERT INTO two VALUES(100, 101); } set authargs } {SQLITE_INSERT two {} main {} SQLITE_READ one a main {}} rename auth {} proc auth {args} { if {[lindex $args 1] == "long"} {return SQLITE_IGNORE} return SQLITE_OK } do_test fkey2-18.8 { catchsql { INSERT INTO short VALUES(1, 3, 2) } | > > > > > > > > > | > > > | 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 | INSERT INTO one VALUES(101, 102); } set authargs [list] execsql { INSERT INTO two VALUES(100, 101); } set authargs } {SQLITE_INSERT two {} main {} SQLITE_READ one a main {}} # Return SQLITE_IGNORE to requests to read from the parent table. This # causes inserts of non-NULL keys into the child table to fail. # rename auth {} proc auth {args} { if {[lindex $args 1] == "long"} {return SQLITE_IGNORE} return SQLITE_OK } do_test fkey2-18.8 { catchsql { INSERT INTO short VALUES(1, 3, 2) } } {1 {foreign key constraint failed}} do_test fkey2-18.9 { execsql { INSERT INTO short VALUES(1, 3, NULL) } } {} do_test fkey2-18.10 { execsql { SELECT * FROM short } } {1 3 2 1 3 {}} do_test fkey2-18.11 { catchsql { UPDATE short SET f = 2 WHERE f IS NULL } } {1 {foreign key constraint failed}} db auth {} unset authargs } #------------------------------------------------------------------------- # The following block of tests, those prefixed with "fkey2-genfkey.", are |
︙ | ︙ |