Unlikely data security problem in sqlite_stat tables

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

Unlikely data security problem in sqlite_stat tables

Simon Slavin-3
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 commands:

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

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
[hidden email]