5 ms·

Based on the mention of free DB pages, I suspect the issue is information leaking because it isn't scrubbed after being deleted, so that it's still in the db fi

Based on the mention of free DB pages, I suspect the issue is information leaking because it isn't scrubbed after being deleted, so that it's still in the db file when it's sent off in a third party, just not "accessible" via SQLite APIs. For example, a redaction procedure which consists of deleting one table that contains all the sensitive data doesn't necessarily _erase the data_; you'd probably want to VACUUM or reconstruct the database from a dump. Moreover, it's a general issue with file formats that can encode the same data in multiple ways that, in addition to the data itself, the data file also incidentally encodes information about the process by which the data was encoded. This metadata is usually ignored and abstracted out by APIs for working with the data, so application developers tend to overlook it as a place where sensitive data can leak. For comparison, you might consider fingerprinting API clients based on the order in which they send HTTP headers or keys in JSON objects, which can often be correlated with language or library versions in environments where the most convenient map structure is an arbitrarily ordered hash. It's not necessarily easy to think of nefarious uses for this sort of information, but SQLite database fragmentation can reveal, albeit in rather rough detail, the order in and frequency with which changes were made. Quite the opposite of "security by obscurity", in fact.

justinclift 8y ago
The SECURE_DELETE pragma sounds like it'd be useful for this: https://sqlite.org/pragma.html#pragma_secure_delete https://sqlite.org/pragma.html#pragma_secure_delete Needs to be manually enabled though, as that doc says it's generally not enabled by default. :/
- Const-me 8y ago
  This indeed helps but only partially. Will still leak data in the way the DB is fragmented. E.g. if you have a text document with 1 row per paragraph, fragmentation will reveal where it was edited, and what kinds or edits were made. You insert a bunch of images into the document, and the physical order of pages will reveal in which order they were inserted, even if you re-arrange them afterwards. It's possible to workaround by careful programming, but still, very easy to screw up and leak data. OTOH, with zipped XMLs people normally use instead, you overwrite the complete file each time, it's not fragmented and hopefully doesn't contain any extra info.
romwell 8y ago
>or reconstruct the database from a dump. Indeed - just write a new file you export data to be shared with someone else. Heck, for many use cases (e.g. config files), the save_to_file() function can always start by deleting the old file completely, creating the DB from scratch, and filling it with data from memory (essentially, treating the DB files as immutable). Hardly a difficulty, no? And proprietary file formats aren't guaranteed to NOT have the problems you described; the only apriori advantage is that their structure is opaque. Hence my "security by obscurity" remark if my understanding is correct.
- Const-me 8y ago
  > just write a new file you export data to be shared with someone else. There's no good way to distinguish. Add separate File/Export command? Users will forget to use it. Add a checkbox on file dialog? Similarly, users will forget to check it. It's very unreliable. The only reliable way is treat every save as "export data to be shared with someone else". > proprietary file formats Very few left, the industry has moved towards open formats. For example, MS Word documents are zipped XMLs, ISO/IEC 29500.

justinclift 8y ago

The SECURE_DELETE pragma sounds like it'd be useful for this: https://sqlite.org/pragma.html#pragma_secure_delete https://sqlite.org/pragma.html#pragma_secure_delete Needs to be manually enabled though, as that doc says it's generally not enabled by default. :/

Const-me 8y ago
This indeed helps but only partially. Will still leak data in the way the DB is fragmented. E.g. if you have a text document with 1 row per paragraph, fragmentation will reveal where it was edited, and what kinds or edits were made. You insert a bunch of images into the document, and the physical order of pages will reveal in which order they were inserted, even if you re-arrange them afterwards. It's possible to workaround by careful programming, but still, very easy to screw up and leak data. OTOH, with zipped XMLs people normally use instead, you overwrite the complete file each time, it's not fragmented and hopefully doesn't contain any extra info.

romwell 8y ago

>or reconstruct the database from a dump. Indeed - just write a new file you export data to be shared with someone else. Heck, for many use cases (e.g. config files), the save_to_file() function can always start by deleting the old file completely, creating the DB from scratch, and filling it with data from memory (essentially, treating the DB files as immutable). Hardly a difficulty, no? And proprietary file formats aren't guaranteed to NOT have the problems you described; the only apriori advantage is that their structure is opaque. Hence my "security by obscurity" remark if my understanding is correct.

Const-me 8y ago
> just write a new file you export data to be shared with someone else. There's no good way to distinguish. Add separate File/Export command? Users will forget to use it. Add a checkbox on file dialog? Similarly, users will forget to check it. It's very unreliable. The only reliable way is treat every save as "export data to be shared with someone else". > proprietary file formats Very few left, the industry has moved towards open formats. For example, MS Word documents are zipped XMLs, ISO/IEC 29500.