What If OpenDocument Used SQLite?

If you're going to use SQLite as an application file format, you should:

1. Enable the secure_delete pragma <https://antonz.org/sqlite-secure-delete/> so that when your user deletes something, the data is actually erased. Otherwise, when a user shares one of your application's files with someone else, the recipient could recover information that the sender thought they had deleted.

2. Enable the options described at <https://www.sqlite.org/security.html#untrusted_sqlite_databa...> under "Untrusted SQLite Database Files" to make it safer to open files from untrusted sources. No one wants to get pwned when they open an email attachment.

3. Be aware that when it comes to handling security vulnerabilities, the SQLite developers consider this use case to be niche ("few real-world applications" open SQLite database files from untrusted sources, they say) and they seem to get annoyed that people run fuzzers against SQLite, even though application file formats should definitely be fuzzed. https://www.sqlite.org/cves.html

They fail to mention any of this on their marketing pages about how you should use SQLite as an application file format.

"Most applications can use SQLite without having to worry about bugs in obscure SQL inputs." And then they recommend SQLite as a document interchange format.

Hrm, using sqlite as an application format would be a good use case for Limbo.

>and they seem to get annoyed that people run fuzzers against SQLite, even though application file formats should definitely be fuzzed.

I think that's an unfair reading. Sqlite runs fuzzers itself and quickly addresses bugs found by fuzzers externally. There's an entire section in their documentation about their own fuzzers and thanking third party fuzzers, including credit to individual engineers.

https://www.sqlite.org/testing.html

The tone of the CVE docs are because people freak out about CVEs flagged by automated tools when the CVEs are for issues that have no security impact for typical usage of SQLite, or have prerequisites that would already have resulted in some form of compromise.

Although this is indeed a worrying statement, it seems true to me. Most users of sqlite control the SQL they use. The problem I would expect from using a database document interchange format is that a maliciously crafted database could result in a CVE. The page acknowledges this possibility, even while pointing out (in their CVE list) that it hasn't happened so far, or is rare (it's hard to parse some of their descriptions).

I'm not that concerned with bugs in sqlite. sqlite is high quality software, and the application that uses it is a more likely source of vulnerabilities.

But I do see a problem if you really need to use a sqlite that's compiled with particular non-default options.

Say I design a file format and implement it, and my implementation uses an sqlite library that's compiled with all the right options. Then I evangelize my file format, telling everyone that it's really just an sqlite database and sooo easy to work with.

First thing that happens is that someone writes a neat little utility for working with the files, written in language X, which comes with a handy sqlite3 library. But that library is not compiled with the right options, and boom, you have a vulnerable utility.

Untrusted database file is not the same as untrusted SQL input.

There are parts of the SQL engine that are exposed to malicious file manipulation (the schema is stored as SQL DDL text) but that's not arbitrary SQL input.

If you want to highlight an inconsistency, this is way more worrying:

> “All historical vulnerabilities reported against SQLite require at least one of these preconditions: (…) 2. The attacker can submit a maliciously crafted database file to the application that the application will then open and query. Few real-world applications meet either of these preconditions…”

However, most of the rest of the page is speaking of arbitrary SQL input, not purposely broken database files.

Most of the recommended [1] setting are available on a per connection basis, through PRAGMAs, sqlite3_db_config, sqlite3_limit, etc; some are global settings, like sqlite3_hard_heap_limit64.

A binding can expose those settings. It's not a given a third party utility will use them, but they can.

1: https://www.sqlite.org/security.html

Ah, I missed that 9.a-c were alternatives. And that, in the absence of custom tables or functions, they are merely defense in depth for something that is already secure, barring bugs. I withdraw my concern.

> There are parts of the SQL engine that are exposed to malicious file manipulation (the schema is stored as SQL DDL text) but that's not arbitrary SQL input.

View and triggers can contain arbitrary SQL and can be defined by a malicious database file, though these can be disabled as described on the "Defense Against The Dark Arts" page.

That leaves default column values and indexes on expressions, which can execute a limited subset of SQL. I'd be worried about certain arbitrary SQL input vulnerabilities being reachable this way.

To be fair, PRAGMA trusted_schema=OFF is recommended by the docs, it just isn't default. The docs also recommend the SQLITE_DIRECTONLY flag on all custom SQL functions.

> The tone of the CVE docs are because people freak out about CVEs flagged by automated tools when the CVEs are for issues that have no security impact for typical usage of SQLite, or have prerequisites that would already have resulted in some form of compromise.

The CVE docs:

> The attacker can submit a maliciously crafted database file to the application that the application will then open and query

This is exactly the normal use case GP talks about with application file formats.

On the other hand, exploiting weaknesses in MITRE’s CVE program to create ticket management primitives, creating “shellcode” that composes them to implement a feature request tracking API, using it to manage your open source organization’s feature roadmap, sure would make for a great 2600 article…

That's true, but most usage of sqlite is not as an application file format, and many of those CVEs don't apply even to that use case. The reason people have policies around CVE scanning is because CVEs often represent real vulnerabilities. But there's also a stuff like "this regex has exponential or polynomial runtime on bad inputs", which is a real security issue for some projects and not others, depending on what the input to the regex is. That's also true for SQLite, and I'm guessing that the author of that page has spent a bunch of time explaining to people worried about some CVE that their usage is not vulnerable. The maintainer of cURL has expressed similar frustration.

> but most usage of sqlite is not as an application file format,

This is exactly the OTHER way around. Most usages of SQLite are as an application file format. Firefox stores bookmarks, history, cookies in SQLite files in the profiles folder. Messaging apps (WhatsApp, Signal, etc. use SQLite for chat history). macOS and Windows use SQLite in various subsystems, ex: Spotlight metadata, application cache. Mobile apps use SQLite heavily. And probably ten thousand other cases as a file format if I bother to look up more.

Mobile apps store SQLite dbs in their private data directory that only they can access. In order to exploit a vulnerability you'd have to first break the sandbox. Desktop OSes generally have far weaker protections than that, if you have access to the user's profile directory you can steal all of their credentials or plant executables etc.

When I think application file format I think of something like .txt, pdf, or .doc, where it's expected that you'll receive untrusted input passed around. In that case it makes a lot more sense to restrict which features of SQLite are accessible, and even then I'd worry about using it in widely - there's so much surface area, plus the user confusion of shm and wal files.