The problem occurs only with non-default compilation settings, and depends on 
an incorrect assumption by the programmer, so it is unlikely that it would 
cause a problem under normal circumstances.  It is related to the following 

DROP TABLE TableName — removes related rows from sqlite_stat tables
DELETE FROM TableName — does not remove related rows from sqlite_stat tables
VACUUM — does not modify sqlite_stat tables

Given compilation setting SQLITE_ENABLE_STAT4 (which is not the default), 
there’s a possible data security problem relating to sqlite_stat3 and 
sqlite_stat4 tables.  These tables include columns named "sample" which contain 
copies of data from tables.

If a TABLE is DROPped from the database, then rows related to it are removed 
from the sqlite_stat tables.  This means there is no security problem.  (I’ve 
just realised I didn’t check that the same was done when DROPping individual 

However there is a convenient command, which sqlite has optimizations for, 
which has a similar effect.  This is the use of the DELETE FROM command with no 
WHERE clause.  This removes all data from the table, but it leaves any "sample" 
values in sqlite_stat tables.  A programmer who did the following

DELETE FROM HomePhoneNumbers;

might reasonably think that this would remove all copies of the data from the 
database file.  Especially if they did the VACUUM with this specific objective.

If this is considered a security matter then a modification to the optimized 
DELETE without WHERE clause command, or to VACUUM, could remove the problem.  
I’m sure the dev team can pick an appropriate way to do it.  Or perhaps the 
documentation should remind people that DELETE without WHERE is just another 
DELETE command, and does not necessarily remove all copies of data from the 
database file.

sqlite-users mailing list

Reply via email to