/ Check-in [23e200da]
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:In defensive mode, do not allow shadow tables to be renamed using ALTER TABLE.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 23e200da5cfbde0798e67cd9e016e4a1cd73b67981e1af841493fcd123d8f547
User & Date: dan 2018-12-18 20:31:14
Context
2018-12-20
15:04
Fix a segfault caused by using the RAISE function incorrectly (library now returns an error instead of crashing). check-in: ddf06db7 user: dan tags: trunk
2018-12-18
20:31
In defensive mode, do not allow shadow tables to be renamed using ALTER TABLE. check-in: 23e200da user: dan tags: trunk
2018-12-17
22:19
Move variable declaration to address compilation issue (C89). check-in: d64f248d user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/alter.c.

    24     24   ** Parameter zName is the name of a table that is about to be altered
    25     25   ** (either with ALTER TABLE ... RENAME TO or ALTER TABLE ... ADD COLUMN).
    26     26   ** If the table is a system table, this function leaves an error message
    27     27   ** in pParse->zErr (system tables may not be altered) and returns non-zero.
    28     28   **
    29     29   ** Or, if zName is not a system table, zero is returned.
    30     30   */
    31         -static int isSystemTable(Parse *pParse, const char *zName){
    32         -  if( 0==sqlite3StrNICmp(zName, "sqlite_", 7) ){
    33         -    sqlite3ErrorMsg(pParse, "table %s may not be altered", zName);
           31  +static int isAlterableTable(Parse *pParse, Table *pTab){
           32  +  if( 0==sqlite3StrNICmp(pTab->zName, "sqlite_", 7) 
           33  +#ifndef SQLITE_OMIT_VIRTUALTABLE
           34  +   || ( (pTab->tabFlags & TF_Shadow) 
           35  +     && (pParse->db->flags & SQLITE_Defensive)
           36  +     && pParse->db->nVdbeExec==0
           37  +   )
           38  +#endif
           39  +  ){
           40  +    sqlite3ErrorMsg(pParse, "table %s may not be altered", pTab->zName);
    34     41       return 1;
    35     42     }
    36     43     return 0;
    37     44   }
    38     45   
    39     46   /*
    40     47   ** Generate code to verify that the schemas of database zDb and, if
................................................................................
   122    129           "there is already another table or index with this name: %s", zName);
   123    130       goto exit_rename_table;
   124    131     }
   125    132   
   126    133     /* Make sure it is not a system table being altered, or a reserved name
   127    134     ** that the table is being renamed to.
   128    135     */
   129         -  if( SQLITE_OK!=isSystemTable(pParse, pTab->zName) ){
          136  +  if( SQLITE_OK!=isAlterableTable(pParse, pTab) ){
   130    137       goto exit_rename_table;
   131    138     }
   132    139     if( SQLITE_OK!=sqlite3CheckObjectName(pParse, zName) ){ goto
   133    140       exit_rename_table;
   134    141     }
   135    142   
   136    143   #ifndef SQLITE_OMIT_VIEW
................................................................................
   420    427   #endif
   421    428   
   422    429     /* Make sure this is not an attempt to ALTER a view. */
   423    430     if( pTab->pSelect ){
   424    431       sqlite3ErrorMsg(pParse, "Cannot add a column to a view");
   425    432       goto exit_begin_add_column;
   426    433     }
   427         -  if( SQLITE_OK!=isSystemTable(pParse, pTab->zName) ){
          434  +  if( SQLITE_OK!=isAlterableTable(pParse, pTab) ){
   428    435       goto exit_begin_add_column;
   429    436     }
   430    437   
   431    438     assert( pTab->addColOffset>0 );
   432    439     iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
   433    440   
   434    441     /* Put a copy of the Table struct in Parse.pNewTable for the
................................................................................
   522    529     int bQuote;                     /* True to quote the new name */
   523    530   
   524    531     /* Locate the table to be altered */
   525    532     pTab = sqlite3LocateTableItem(pParse, 0, &pSrc->a[0]);
   526    533     if( !pTab ) goto exit_rename_column;
   527    534   
   528    535     /* Cannot alter a system table */
   529         -  if( SQLITE_OK!=isSystemTable(pParse, pTab->zName) ) goto exit_rename_column;
          536  +  if( SQLITE_OK!=isAlterableTable(pParse, pTab) ) goto exit_rename_column;
   530    537     if( SQLITE_OK!=isRealTable(pParse, pTab) ) goto exit_rename_column;
   531    538   
   532    539     /* Which schema holds the table to be altered */  
   533    540     iSchema = sqlite3SchemaToIndex(db, pTab->pSchema);
   534    541     assert( iSchema>=0 );
   535    542     zDb = db->aDb[iSchema].zDbSName;
   536    543   

Changes to test/altertab.test.

   501    501     SELECT * FROM x;
   502    502   } {x x x}
   503    503   
   504    504   do_execsql_test 15.5 {
   505    505     SELECT sql FROM sqlite_master WHERE name = 'y';
   506    506   } {{CREATE VIEW y AS SELECT f2 AS f1 FROM x}}
   507    507   
          508  +#-------------------------------------------------------------------------
          509  +# Test that it is not possible to rename a shadow table in DEFENSIVE mode.
          510  +#
          511  +ifcapable fts3 {
          512  +  proc vtab_command {method args} {
          513  +    switch -- $method {
          514  +      xConnect {
          515  +        if {[info exists ::vtab_connect_sql]} {
          516  +          execsql $::vtab_connect_sql
          517  +        }
          518  +        return "CREATE TABLE t1(a, b, c)"
          519  +      }
          520  +
          521  +      xBestIndex {
          522  +        set clist [lindex $args 0]
          523  +        if {[llength $clist]!=1} { error "unexpected constraint list" }
          524  +        catch { array unset C }
          525  +        array set C [lindex $clist 0]
          526  +        if {$C(usable)} {
          527  +          return "omit 0 cost 0 rows 1 idxnum 555 idxstr eq!"
          528  +        } else {
          529  +          return "cost 1000000 rows 0 idxnum 0 idxstr scan..."
          530  +        }
          531  +      }
          532  +    }
          533  +
          534  +    return {}
          535  +  }
          536  +
          537  +  register_tcl_module db
          538  +
          539  +  sqlite3_db_config db DEFENSIVE 1
          540  +
          541  +  do_execsql_test 16.0 {
          542  +    CREATE VIRTUAL TABLE y1 USING fts3;
          543  +  }
          544  +
          545  +  do_catchsql_test 16.1 {
          546  +    INSERT INTO y1_segments VALUES(1, X'1234567890');
          547  +  } {1 {table y1_segments may not be modified}}
          548  +
          549  +  do_catchsql_test 16.2 {
          550  +    ALTER TABLE y1_segments RENAME TO abc;
          551  +  } {1 {table y1_segments may not be altered}}
          552  +
          553  +  do_execsql_test 16.3 {
          554  +    ALTER TABLE y1 RENAME TO z1;
          555  +  }
          556  +
          557  +  do_execsql_test 16.4 {
          558  +    SELECT * FROM z1_segments;
          559  +  }
          560  +}
   508    561   
   509    562   finish_test