/ Check-in [da587d18]
Login

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

Overview
Comment:Add new sqlite3_prepare_v3() flag SQLITE_PREPARE_NO_VTAB, for preparing statements that are not allowed to use any virtual tables. Use this to prevent circular references in triggers on virtual table shadow tables from causing resource leaks.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:da587d18575ac06a6b65fec1d106f0cc65bc10f493ca6c6b99117a2162d15a52
User & Date: dan 2018-12-21 20:18:06
Context
2018-12-21
22:08
Improved detection of shadow table corruption in RTREE. check-in: b39bf435 user: drh tags: trunk
20:18
Add new sqlite3_prepare_v3() flag SQLITE_PREPARE_NO_VTAB, for preparing statements that are not allowed to use any virtual tables. Use this to prevent circular references in triggers on virtual table shadow tables from causing resource leaks. check-in: da587d18 user: dan tags: trunk
19:55
Use SQLITE_PREPARE_NO_VTAB in rtree as well. Closed-Leaf check-in: 82a2ae71 user: dan tags: prepare-no-vtab
18:51
In FTS3, avoid calling memcpy() with a NULL source pointer, even if the transfer amount is zero bytes. check-in: 1abb83d2 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/fts3/fts3_write.c.

   392    392     sqlite3_stmt *pStmt;
   393    393   
   394    394     assert( SizeofArray(azSql)==SizeofArray(p->aStmt) );
   395    395     assert( eStmt<SizeofArray(azSql) && eStmt>=0 );
   396    396     
   397    397     pStmt = p->aStmt[eStmt];
   398    398     if( !pStmt ){
          399  +    int f = SQLITE_PREPARE_PERSISTENT|SQLITE_PREPARE_NO_VTAB;
   399    400       char *zSql;
   400    401       if( eStmt==SQL_CONTENT_INSERT ){
   401    402         zSql = sqlite3_mprintf(azSql[eStmt], p->zDb, p->zName, p->zWriteExprlist);
   402    403       }else if( eStmt==SQL_SELECT_CONTENT_BY_ROWID ){
          404  +      f &= ~SQLITE_PREPARE_NO_VTAB;
   403    405         zSql = sqlite3_mprintf(azSql[eStmt], p->zReadExprlist);
   404    406       }else{
   405    407         zSql = sqlite3_mprintf(azSql[eStmt], p->zDb, p->zName);
   406    408       }
   407    409       if( !zSql ){
   408    410         rc = SQLITE_NOMEM;
   409    411       }else{
   410         -      rc = sqlite3_prepare_v3(p->db, zSql, -1, SQLITE_PREPARE_PERSISTENT,
   411         -                              &pStmt, NULL);
          412  +      rc = sqlite3_prepare_v3(p->db, zSql, -1, f, &pStmt, NULL);
   412    413         sqlite3_free(zSql);
   413    414         assert( rc==SQLITE_OK || pStmt==0 );
   414    415         p->aStmt[eStmt] = pStmt;
   415    416       }
   416    417     }
   417    418     if( apVal ){
   418    419       int i;

Changes to ext/fts5/fts5_index.c.

   725    725     Fts5Index *p,
   726    726     sqlite3_stmt **ppStmt,
   727    727     char *zSql
   728    728   ){
   729    729     if( p->rc==SQLITE_OK ){
   730    730       if( zSql ){
   731    731         p->rc = sqlite3_prepare_v3(p->pConfig->db, zSql, -1,
   732         -                                 SQLITE_PREPARE_PERSISTENT, ppStmt, 0);
          732  +          SQLITE_PREPARE_PERSISTENT|SQLITE_PREPARE_NO_VTAB,
          733  +          ppStmt, 0);
   733    734       }else{
   734    735         p->rc = SQLITE_NOMEM;
   735    736       }
   736    737     }
   737    738     sqlite3_free(zSql);
   738    739     return p->rc;
   739    740   }
................................................................................
   766    767   **
   767    768   **     DELETE FROM %_data WHERE id BETWEEN $iFirst AND $iLast
   768    769   */
   769    770   static void fts5DataDelete(Fts5Index *p, i64 iFirst, i64 iLast){
   770    771     if( p->rc!=SQLITE_OK ) return;
   771    772   
   772    773     if( p->pDeleter==0 ){
   773         -    int rc;
   774    774       Fts5Config *pConfig = p->pConfig;
   775    775       char *zSql = sqlite3_mprintf(
   776    776           "DELETE FROM '%q'.'%q_data' WHERE id>=? AND id<=?", 
   777    777             pConfig->zDb, pConfig->zName
   778    778       );
   779         -    if( zSql==0 ){
   780         -      rc = SQLITE_NOMEM;
   781         -    }else{
   782         -      rc = sqlite3_prepare_v3(pConfig->db, zSql, -1,
   783         -                              SQLITE_PREPARE_PERSISTENT, &p->pDeleter, 0);
   784         -      sqlite3_free(zSql);
   785         -    }
   786         -    if( rc!=SQLITE_OK ){
   787         -      p->rc = rc;
   788         -      return;
   789         -    }
          779  +    if( fts5IndexPrepareStmt(p, &p->pDeleter, zSql) ) return;
   790    780     }
   791    781   
   792    782     sqlite3_bind_int64(p->pDeleter, 1, iFirst);
   793    783     sqlite3_bind_int64(p->pDeleter, 2, iLast);
   794    784     sqlite3_step(p->pDeleter);
   795    785     p->rc = sqlite3_reset(p->pDeleter);
   796    786   }

Changes to ext/fts5/fts5_storage.c.

   132    132           zSql = sqlite3_mprintf(azStmt[eStmt], pC->zDb, pC->zName);
   133    133           break;
   134    134       }
   135    135   
   136    136       if( zSql==0 ){
   137    137         rc = SQLITE_NOMEM;
   138    138       }else{
   139         -      rc = sqlite3_prepare_v3(pC->db, zSql, -1,
   140         -                              SQLITE_PREPARE_PERSISTENT, &p->aStmt[eStmt], 0);
          139  +      int f = SQLITE_PREPARE_PERSISTENT;
          140  +      if( eStmt>FTS5_STMT_LOOKUP ) f |= SQLITE_PREPARE_NO_VTAB;
          141  +      rc = sqlite3_prepare_v3(pC->db, zSql, -1, f, &p->aStmt[eStmt], 0);
   141    142         sqlite3_free(zSql);
   142    143         if( rc!=SQLITE_OK && pzErrMsg ){
   143    144           *pzErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(pC->db));
   144    145         }
   145    146       }
   146    147     }
   147    148   

Added ext/fts5/test/fts5circref.test.

            1  +# 2018 Dec 22
            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.  The
           12  +# focus of this script is testing the FTS5 module.
           13  +#
           14  +
           15  +source [file join [file dirname [info script]] fts5_common.tcl]
           16  +set testprefix fts5circref
           17  +
           18  +# If SQLITE_ENABLE_FTS5 is not defined, omit this file.
           19  +ifcapable !fts5 {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +do_execsql_test 1.0 {
           25  +  CREATE VIRTUAL TABLE tt USING fts5(a);
           26  +  SELECT name FROM sqlite_master ORDER BY 1;
           27  +} {
           28  +  tt tt_config tt_content tt_data tt_docsize tt_idx
           29  +}
           30  +db_save_and_close
           31  +
           32  +foreach {tn schema sql} {
           33  +  1 {
           34  +    CREATE TRIGGER tr1 AFTER INSERT ON tt_config BEGIN
           35  +      SELECT * FROM tt;
           36  +    END;
           37  +  } {
           38  +    INSERT INTO tt(tt, rank) VALUES('usermerge', 4);
           39  +  }
           40  +
           41  +  2 {
           42  +    CREATE TRIGGER tr1 AFTER INSERT ON tt_docsize BEGIN
           43  +      SELECT * FROM tt;
           44  +    END;
           45  +  } {
           46  +    INSERT INTO tt(a) VALUES('one two three');
           47  +  }
           48  +
           49  +  3 {
           50  +    CREATE TRIGGER tr1 AFTER INSERT ON tt_content BEGIN
           51  +      SELECT * FROM tt;
           52  +    END;
           53  +  } {
           54  +    INSERT INTO tt(a) VALUES('one two three');
           55  +  }
           56  +
           57  +  4 {
           58  +    CREATE TRIGGER tr1 AFTER INSERT ON tt_data BEGIN
           59  +      SELECT * FROM tt;
           60  +    END;
           61  +  } {
           62  +    INSERT INTO tt(a) VALUES('one two three');
           63  +  }
           64  +
           65  +  5 {
           66  +    CREATE TRIGGER tr1 AFTER INSERT ON tt_idx BEGIN
           67  +      SELECT * FROM tt;
           68  +    END;
           69  +  } {
           70  +    INSERT INTO tt(a) VALUES('one two three');
           71  +  }
           72  +} {
           73  +  db_restore_and_reopen
           74  +  do_execsql_test 1.1.$tn.1 $schema
           75  +  do_catchsql_test 1.1.$tn.2 $sql {1 {SQL logic error}}
           76  +  db close
           77  +}
           78  +
           79  +
           80  +finish_test

Changes to ext/rtree/rtree.c.

  3419   3419       /* Read and write the xxx_parent table */
  3420   3420       "SELECT parentnode FROM '%q'.'%q_parent' WHERE nodeno = ?1",
  3421   3421       "INSERT OR REPLACE INTO '%q'.'%q_parent' VALUES(?1, ?2)",
  3422   3422       "DELETE FROM '%q'.'%q_parent' WHERE nodeno = ?1"
  3423   3423     };
  3424   3424     sqlite3_stmt **appStmt[N_STATEMENT];
  3425   3425     int i;
         3426  +  const int f = SQLITE_PREPARE_PERSISTENT|SQLITE_PREPARE_NO_VTAB;
  3426   3427   
  3427   3428     pRtree->db = db;
  3428   3429   
  3429   3430     if( isCreate ){
  3430   3431       char *zCreate;
  3431   3432       sqlite3_str *p = sqlite3_str_new(db);
  3432   3433       int ii;
................................................................................
  3475   3476          /* An UPSERT is very slightly slower than REPLACE, but it is needed
  3476   3477          ** if there are auxiliary columns */
  3477   3478          zFormat = "INSERT INTO\"%w\".\"%w_rowid\"(rowid,nodeno)VALUES(?1,?2)"
  3478   3479                     "ON CONFLICT(rowid)DO UPDATE SET nodeno=excluded.nodeno";
  3479   3480       }
  3480   3481       zSql = sqlite3_mprintf(zFormat, zDb, zPrefix);
  3481   3482       if( zSql ){
  3482         -      rc = sqlite3_prepare_v3(db, zSql, -1, SQLITE_PREPARE_PERSISTENT,
  3483         -                              appStmt[i], 0); 
         3483  +      rc = sqlite3_prepare_v3(db, zSql, -1, f, appStmt[i], 0); 
  3484   3484       }else{
  3485   3485         rc = SQLITE_NOMEM;
  3486   3486       }
  3487   3487       sqlite3_free(zSql);
  3488   3488     }
  3489   3489     if( pRtree->nAux ){
  3490   3490       pRtree->zReadAuxSql = sqlite3_mprintf(
................................................................................
  3506   3506           }
  3507   3507         }
  3508   3508         sqlite3_str_appendf(p, " WHERE rowid=?1");
  3509   3509         zSql = sqlite3_str_finish(p);
  3510   3510         if( zSql==0 ){
  3511   3511           rc = SQLITE_NOMEM;
  3512   3512         }else{
  3513         -        rc = sqlite3_prepare_v3(db, zSql, -1, SQLITE_PREPARE_PERSISTENT,
  3514         -                                &pRtree->pWriteAux, 0); 
         3513  +        rc = sqlite3_prepare_v3(db, zSql, -1, f, &pRtree->pWriteAux, 0); 
  3515   3514           sqlite3_free(zSql);
  3516   3515         }
  3517   3516       }
  3518   3517     }
  3519   3518   
  3520   3519     return rc;
  3521   3520   }

Added ext/rtree/rtreecirc.test.

            1  +# 2018 Dec 22
            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.  The
           12  +# focus of this script is testing the FTS5 module.
           13  +#
           14  +
           15  +if {![info exists testdir]} {
           16  +  set testdir [file join [file dirname [info script]] .. .. test]
           17  +}
           18  +source [file join [file dirname [info script]] rtree_util.tcl]
           19  +source $testdir/tester.tcl
           20  +set testprefix rtreecirc
           21  +
           22  +ifcapable !rtree {
           23  +  finish_test
           24  +  return
           25  +}
           26  +
           27  +do_execsql_test 1.0 {
           28  +  CREATE VIRTUAL TABLE rt USING rtree(id, x1, x2, y1, y2);
           29  +  SELECT name FROM sqlite_master ORDER BY 1;
           30  +} {
           31  +  rt rt_node rt_parent rt_rowid
           32  +}
           33  +db_save_and_close
           34  +
           35  +foreach {tn schema sql} {
           36  +  1 {
           37  +    CREATE TRIGGER tr1 AFTER INSERT ON rt_node BEGIN
           38  +      SELECT * FROM rt;
           39  +    END;
           40  +  } {
           41  +    INSERT INTO rt VALUES(1, 2, 3, 4, 5);
           42  +  }
           43  +  2 {
           44  +    CREATE TRIGGER tr1 AFTER INSERT ON rt_parent BEGIN
           45  +      SELECT * FROM rt;
           46  +    END;
           47  +  } {
           48  +    INSERT INTO rt VALUES(1, 2, 3, 4, 5);
           49  +  }
           50  +  3 {
           51  +    CREATE TRIGGER tr1 AFTER INSERT ON rt_rowid BEGIN
           52  +      SELECT * FROM rt;
           53  +    END;
           54  +  } {
           55  +    INSERT INTO rt VALUES(1, 2, 3, 4, 5);
           56  +  }
           57  +} {
           58  +  db_restore_and_reopen
           59  +  do_execsql_test  1.1.$tn.1 $schema
           60  +  do_catchsql_test 1.1.$tn.2 $sql {1 {no such table: main.rt}}
           61  +  db close
           62  +}
           63  +
           64  +
           65  +finish_test
           66  +

Changes to src/build.c.

   350    350      && SQLITE_OK!=sqlite3ReadSchema(pParse)
   351    351     ){
   352    352       return 0;
   353    353     }
   354    354   
   355    355     p = sqlite3FindTable(db, zName, zDbase);
   356    356     if( p==0 ){
   357         -    const char *zMsg = flags & LOCATE_VIEW ? "no such view" : "no such table";
   358    357   #ifndef SQLITE_OMIT_VIRTUALTABLE
   359    358       /* If zName is the not the name of a table in the schema created using
   360    359       ** CREATE, then check to see if it is the name of an virtual table that
   361    360       ** can be an eponymous virtual table. */
   362         -    Module *pMod = (Module*)sqlite3HashFind(&db->aModule, zName);
   363         -    if( pMod==0 && sqlite3_strnicmp(zName, "pragma_", 7)==0 ){
   364         -      pMod = sqlite3PragmaVtabRegister(db, zName);
   365         -    }
   366         -    if( pMod && sqlite3VtabEponymousTableInit(pParse, pMod) ){
   367         -      return pMod->pEpoTab;
          361  +    if( pParse->disableVtab==0 ){
          362  +      Module *pMod = (Module*)sqlite3HashFind(&db->aModule, zName);
          363  +      if( pMod==0 && sqlite3_strnicmp(zName, "pragma_", 7)==0 ){
          364  +        pMod = sqlite3PragmaVtabRegister(db, zName);
          365  +      }
          366  +      if( pMod && sqlite3VtabEponymousTableInit(pParse, pMod) ){
          367  +        return pMod->pEpoTab;
          368  +      }
   368    369       }
   369    370   #endif
   370         -    if( (flags & LOCATE_NOERR)==0 ){
   371         -      if( zDbase ){
   372         -        sqlite3ErrorMsg(pParse, "%s: %s.%s", zMsg, zDbase, zName);
   373         -      }else{
   374         -        sqlite3ErrorMsg(pParse, "%s: %s", zMsg, zName);
   375         -      }
   376         -      pParse->checkSchema = 1;
          371  +    if( flags & LOCATE_NOERR ) return 0;
          372  +    pParse->checkSchema = 1;
          373  +  }else if( IsVirtual(p) && pParse->disableVtab ){
          374  +    p = 0;
          375  +  }
          376  +
          377  +  if( p==0 ){
          378  +    const char *zMsg = flags & LOCATE_VIEW ? "no such view" : "no such table";
          379  +    if( zDbase ){
          380  +      sqlite3ErrorMsg(pParse, "%s: %s.%s", zMsg, zDbase, zName);
          381  +    }else{
          382  +      sqlite3ErrorMsg(pParse, "%s: %s", zMsg, zName);
   377    383       }
   378    384     }
   379    385   
   380    386     return p;
   381    387   }
   382    388   
   383    389   /*

Changes to src/prepare.c.

   541    541     /* For a long-term use prepared statement avoid the use of
   542    542     ** lookaside memory.
   543    543     */
   544    544     if( prepFlags & SQLITE_PREPARE_PERSISTENT ){
   545    545       sParse.disableLookaside++;
   546    546       db->lookaside.bDisable++;
   547    547     }
          548  +  sParse.disableVtab = (prepFlags & SQLITE_PREPARE_NO_VTAB)!=0;
   548    549   
   549    550     /* Check to verify that it is possible to get a read lock on all
   550    551     ** database schemas.  The inability to get a read lock indicates that
   551    552     ** some other database connection is holding a write-lock, which in
   552    553     ** turn means that the other connection has made uncommitted changes
   553    554     ** to the schema.
   554    555     **

Changes to src/sqlite.h.in.

  3632   3632   ** [[SQLITE_PREPARE_NORMALIZE]] <dt>SQLITE_PREPARE_NORMALIZE</dt>
  3633   3633   ** <dd>The SQLITE_PREPARE_NORMALIZE flag is a no-op. This flag used
  3634   3634   ** to be required for any prepared statement that wanted to use the
  3635   3635   ** [sqlite3_normalized_sql()] interface.  However, the
  3636   3636   ** [sqlite3_normalized_sql()] interface is now available to all
  3637   3637   ** prepared statements, regardless of whether or not they use this
  3638   3638   ** flag.
         3639  +**
         3640  +** [[SQLITE_PREPARE_NO_VTAB]] <dt>SQLITE_PREPARE_NO_VTAB</dt>
         3641  +** <dd>The SQLITE_PREPARE_NO_VTAB flag causes the SQL compiler
         3642  +** to return an error (error code SQLITE_ERROR) if the statement uses
         3643  +** any virtual tables.
  3639   3644   ** </dl>
  3640   3645   */
  3641   3646   #define SQLITE_PREPARE_PERSISTENT              0x01
  3642   3647   #define SQLITE_PREPARE_NORMALIZE               0x02
         3648  +#define SQLITE_PREPARE_NO_VTAB                 0x04
  3643   3649   
  3644   3650   /*
  3645   3651   ** CAPI3REF: Compiling An SQL Statement
  3646   3652   ** KEYWORDS: {SQL statement compiler}
  3647   3653   ** METHOD: sqlite3
  3648   3654   ** CONSTRUCTOR: sqlite3_stmt
  3649   3655   **

Changes to src/sqliteInt.h.

  3055   3055     u8 nested;           /* Number of nested calls to the parser/code generator */
  3056   3056     u8 nTempReg;         /* Number of temporary registers in aTempReg[] */
  3057   3057     u8 isMultiWrite;     /* True if statement may modify/insert multiple rows */
  3058   3058     u8 mayAbort;         /* True if statement may throw an ABORT exception */
  3059   3059     u8 hasCompound;      /* Need to invoke convertCompoundSelectToSubquery() */
  3060   3060     u8 okConstFactor;    /* OK to factor out constants */
  3061   3061     u8 disableLookaside; /* Number of times lookaside has been disabled */
         3062  +  u8 disableVtab;      /* Disable all virtual tables for this parse */
  3062   3063     int nRangeReg;       /* Size of the temporary register block */
  3063   3064     int iRangeReg;       /* First register in temporary register block */
  3064   3065     int nErr;            /* Number of errors seen */
  3065   3066     int nTab;            /* Number of previously allocated VDBE cursors */
  3066   3067     int nMem;            /* Number of memory cells used so far */
  3067   3068     int nOpAlloc;        /* Number of slots allocated for Vdbe.aOp[] */
  3068   3069     int szOpAlloc;       /* Bytes of memory space allocated for Vdbe.aOp[] */

Changes to src/trigger.c.

   912    912     sNC.pParse = pSubParse;
   913    913     pSubParse->db = db;
   914    914     pSubParse->pTriggerTab = pTab;
   915    915     pSubParse->pToplevel = pTop;
   916    916     pSubParse->zAuthContext = pTrigger->zName;
   917    917     pSubParse->eTriggerOp = pTrigger->op;
   918    918     pSubParse->nQueryLoop = pParse->nQueryLoop;
          919  +  pSubParse->disableVtab = pParse->disableVtab;
   919    920   
   920    921     v = sqlite3GetVdbe(pSubParse);
   921    922     if( v ){
   922    923       VdbeComment((v, "Start: %s.%s (%s %s%s%s ON %s)", 
   923    924         pTrigger->zName, onErrorText(orconf),
   924    925         (pTrigger->tr_tm==TRIGGER_BEFORE ? "BEFORE" : "AFTER"),
   925    926           (pTrigger->op==TK_UPDATE ? "UPDATE" : ""),

Changes to test/fts3aa.test.

   246    246   do_execsql_test 9.1 {
   247    247     CREATE VIRTUAL TABLE t9 USING fts4(a, "", '---');
   248    248   }
   249    249   do_execsql_test 9.2 {
   250    250     CREATE VIRTUAL TABLE t10 USING fts3(<, b, c);
   251    251   }
   252    252   
          253  +do_execsql_test 10.0 {
          254  +  CREATE VIRTUAL TABLE z1 USING fts3;
          255  +  INSERT INTO z1 VALUES('one two three'),('four one five'),('six two five');
          256  +  CREATE TRIGGER z1r1 AFTER DELETE ON z1_content BEGIN
          257  +    DELETE FROM z1;
          258  +  END;
          259  +}
          260  +do_catchsql_test 10.1 {
          261  +  DELETE FROM z1;
          262  +} {1 {SQL logic error}}
          263  +
   253    264   expand_all_sql db
   254    265   finish_test