SQLite

View Ticket
Login
2017-01-25
19:04 Fixed ticket [e6e962d6]: sqlite3_blob_reopen() misbehavior on ALTER-ed table plus 5 other changes (artifact: 3850384c user: drh)
14:38
Ensure that sqlite3_blob_reopen() correctly handles short rows. Fix for ticket [e6e962d6b0f06f46e]. (check-in: 8cd1a445 user: drh tags: trunk)
04:41
Ensure that sqlite3_blob_reopen() correctly handles short rows. Proposed fix for ticket [e6e962d6b0f06f46e]. Further testing needed. (Closed-Leaf check-in: 57d8dad3 user: drh tags: blob_reopen-fix)
04:28 New ticket [e6e962d6] sqlite3_blob_reopen() misbehavior on ALTER-ed table. (artifact: a6d71ce7 user: drh)

Ticket Hash: e6e962d6b0f06f46e4ec3c605d014bee4c0f3d77
Title: sqlite3_blob_reopen() misbehavior on ALTER-ed table
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2017-01-25 19:04:30
Version Found In: 3.16.2
User Comments:
drh added on 2017-01-25 04:28:49:

If ALTER TABLE ADD COLUMN is run to add a column to a table, and then later the sqlite3_blob_open() interface is run successfully on that new column, and then sqlite3_blob_reopen() is run to move the sqlite3_blob object to a different row where the added column has never been initialized, then the sqlite3_blob_reopen() routine does not detect that the column it is attempting to open contains a NULL. This can lead to further problems downstream. A C program to demonstrate the issue is as follows:

#include <stdio.h>
#include "sqlite3.h"

int main(int argc, char **argv){
  sqlite3 *db;
  sqlite3_blob *pBlob = 0;
  int rc;

  sqlite3_open(":memory:", &db);
  sqlite3_exec(db,
    "CREATE TABLE t1(a INTEGER PRIMARY KEY);"
    "INSERT INTO t1(a) VALUES(1),(2),(5),(7);"
    "ALTER TABLE t1 ADD COLUMN b;"
    "ALTER TABLE t1 ADD COLUMN c;"
    "ALTER TABLE t1 ADD COLUMN d;"
    "UPDATE t1 SET d=zeroblob(15) WHERE a=1;",
  0, 0, 0);
  rc = sqlite3_blob_open(db, 0, "t1", "d", 1, 1, &pBlob);
  printf("sqlite3_blob_open() returns %d (%s)\n", rc, sqlite3_errmsg(db));
  rc = sqlite3_blob_reopen(pBlob, 2);
  printf("sqlite3_blob_reopen() returns %d (%s)\n", rc, sqlite3_errmsg(db));
  sqlite3_blob_close(pBlob);
  sqlite3_close(db);
  return 0;
}

In the program above, the sqlite3_blob_reopen() should fail since it is attempting to open a NULL value. But it returns SQLITE_OK.

This problem appears to have always existed in the sqlite3_blob_reopen() interface since it was first added in SQLite 3.7.4 (2010-12-07).