SQLite
View Ticket
Not logged in
Ticket UUID: 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: (text/x-fossil-wiki)
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:

<blockquote><verbatim>
#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;
}
</verbatim></blockquote>

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).