/ Check-in [64bec9e6]
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:Fix a fairly obscure problem allowing an "ALTER TABLE RENAME col TO ..." statement to modify the schema in such a way as to break a reference within a trigger program.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 64bec9e6214c6932fab5a3fb8c569ae14cd2d603bd0f8b26104815c3bb9d396a
User & Date: dan 2019-01-18 16:06:18
Context
2019-01-18
17:53
Handle oversize floating point values carefully when converting to integers for the '%' binary operator. check-in: 048add13 user: drh tags: trunk
16:06
Fix a fairly obscure problem allowing an "ALTER TABLE RENAME col TO ..." statement to modify the schema in such a way as to break a reference within a trigger program. check-in: 64bec9e6 user: dan tags: trunk
14:53
Use the full 64-bit integer value in the argument to randomblob(). check-in: 05df5f7a user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/alter.c.

1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
....
1108
1109
1110
1111
1112
1113
1114

1115
1116
1117
1118
1119
1120
1121
....
1139
1140
1141
1142
1143
1144
1145

1146
1147
1148
1149
1150
1151
1152
** message in the Parse object.
*/
static int renameResolveTrigger(Parse *pParse, const char *zDb){
  sqlite3 *db = pParse->db;
  Trigger *pNew = pParse->pNewTrigger;
  TriggerStep *pStep;
  NameContext sNC;
  SrcList sSrc;
  int rc = SQLITE_OK;

  memset(&sNC, 0, sizeof(sNC));
  sNC.pParse = pParse;
  assert( pNew->pTabSchema );
  pParse->pTriggerTab = sqlite3FindTable(db, pNew->table, 
      db->aDb[sqlite3SchemaToIndex(db, pNew->pTabSchema)].zDbSName
................................................................................
      if( pParse->nErr ) rc = pParse->rc;
    }
    if( rc==SQLITE_OK && pStep->zTarget ){
      Table *pTarget = sqlite3LocateTable(pParse, 0, pStep->zTarget, zDb);
      if( pTarget==0 ){
        rc = SQLITE_ERROR;
      }else if( SQLITE_OK==(rc = sqlite3ViewGetColumnNames(pParse, pTarget)) ){

        memset(&sSrc, 0, sizeof(sSrc));
        sSrc.nSrc = 1;
        sSrc.a[0].zName = pStep->zTarget;
        sSrc.a[0].pTab = pTarget;
        sNC.pSrcList = &sSrc;
        if( pStep->pWhere ){
          rc = sqlite3ResolveExprNames(&sNC, pStep->pWhere);
................................................................................
            rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertWhere);
          }
          if( rc==SQLITE_OK ){
            rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere);
          }
          sNC.ncFlags = 0;
        }

      }
    }
  }
  return rc;
}

/*







<







 







>







 







>







1077
1078
1079
1080
1081
1082
1083

1084
1085
1086
1087
1088
1089
1090
....
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
....
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
** message in the Parse object.
*/
static int renameResolveTrigger(Parse *pParse, const char *zDb){
  sqlite3 *db = pParse->db;
  Trigger *pNew = pParse->pNewTrigger;
  TriggerStep *pStep;
  NameContext sNC;

  int rc = SQLITE_OK;

  memset(&sNC, 0, sizeof(sNC));
  sNC.pParse = pParse;
  assert( pNew->pTabSchema );
  pParse->pTriggerTab = sqlite3FindTable(db, pNew->table, 
      db->aDb[sqlite3SchemaToIndex(db, pNew->pTabSchema)].zDbSName
................................................................................
      if( pParse->nErr ) rc = pParse->rc;
    }
    if( rc==SQLITE_OK && pStep->zTarget ){
      Table *pTarget = sqlite3LocateTable(pParse, 0, pStep->zTarget, zDb);
      if( pTarget==0 ){
        rc = SQLITE_ERROR;
      }else if( SQLITE_OK==(rc = sqlite3ViewGetColumnNames(pParse, pTarget)) ){
        SrcList sSrc;
        memset(&sSrc, 0, sizeof(sSrc));
        sSrc.nSrc = 1;
        sSrc.a[0].zName = pStep->zTarget;
        sSrc.a[0].pTab = pTarget;
        sNC.pSrcList = &sSrc;
        if( pStep->pWhere ){
          rc = sqlite3ResolveExprNames(&sNC, pStep->pWhere);
................................................................................
            rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertWhere);
          }
          if( rc==SQLITE_OK ){
            rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere);
          }
          sNC.ncFlags = 0;
        }
        sNC.pSrcList = 0;
      }
    }
  }
  return rc;
}

/*

Changes to test/altertab2.test.

301
302
303
304
305
306
307



























308
309
310
311
} {
  {CREATE TRIGGER r1 AFTER INSERT ON "xyzzy" BEGIN
    INSERT INTO t2
    SELECT a,b,ccc FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,ccc;
  END}
}





























finish_test









>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
} {
  {CREATE TRIGGER r1 AFTER INSERT ON "xyzzy" BEGIN
    INSERT INTO t2
    SELECT a,b,ccc FROM "xyzzy" UNION SELECT d,e,f FROM "xyzzy" ORDER BY b,ccc;
  END}
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 8.0 {
  CREATE TABLE t1(a, b, c); 
  CREATE TABLE t2(a, b, c); 
  CREATE TABLE t3(d, e, f);
  CREATE VIEW v1 AS SELECT * FROM t1;
  CREATE TRIGGER tr AFTER INSERT ON t3 BEGIN
    UPDATE t2 SET a = new.d;
    SELECT a, b, c FROM v1;
  END;
}

do_execsql_test 8.1 {
  INSERT INTO t3 VALUES(1, 2, 3);
}

# The following ALTER TABLE fails as if column "t1.a" is renamed the "a"
# in the "SELECT a, b, c FROM v1" within the trigger can no longer be
# resolved. But at one point there was a bug allowing the ALTER TABLE
# succeed. Which meant the subsequent INSERT statement would fail.
do_catchsql_test 8.2 {
  ALTER TABLE t1 RENAME a TO aaa;
} {1 {error in trigger tr after rename: no such column: a}}
do_execsql_test 8.3 {
  INSERT INTO t3 VALUES(4, 5, 6);
}

finish_test