Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

What If OpenDocument Used SQLite?

sqlite.org

Post reply on HN
Print view View original

71–80 of 109 posts

Re: What If OpenDocument Used SQLite?

#71

Post by maweki »

jdboyd wrote: post #60
floating-io wrote: post #3

An interesting skim, but it would have been more meaningful if it had tackled text documents or spreadsheets to show what additional functionality would be enabled with those beyond "versioning". Maybe it's just me, but I see the presentation functionality as one of the less used aspects of the OpenOffice family.

What he listed as the first improvement, "Replace ZIP with SQLite" would certainly apply to the other ODF formats. He advocates breaking the XML into smaller pieces in SQLite. I suppose making each slide a new XML record could make sense. Moving over to spreadsheets, I don't know how ODF does it now, but making each sheet a separate XML could make sense. Thinking about Write documents, I wonder what a good smaller un…

Splitting the presentation into multiple fragments makes it more difficult to generate/alter a presentation using xslt.

Re: What If OpenDocument Used SQLite?

#72

Post by ncruces »

chris_wot wrote: post #38
agwa wrote: post #28

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…

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

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.

Re: What If OpenDocument Used SQLite?

#73

Post by ncruces »

munch117 wrote: post #68
wzdd wrote: post #56

Earlier quoted context omitted.

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 descri…

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…

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

Re: What If OpenDocument Used SQLite?

#74

Post by gwd »

Anki's storage format is SQLite (or was a few years ago). That made it really lovely when I wanted to import the contents (including the view logs) of Anki deck I'd been using for a decade into a custom system I was designing. Just pop up the `sqlite3` REPL, poke around and see what it looks like, then write standard SQL queries to get the data out.

Re: What If OpenDocument Used SQLite?

#75

Post by afiori »

cpach wrote: post #57
afiori wrote: post #45

Earlier quoted context omitted.

In that case the application would keep a temporary file and copy over when saving

Maybe, but how would the application know if /data/foo.bar is a local file or mounted via NFS/SMB/etc?

it would always use such a temporary file and update the "real" file only on explicit saves with fast mv or cp operations

Re: What If OpenDocument Used SQLite?

#77

Post by lifthrasiir »

chungy wrote: post #55
lifthrasiir wrote: post #34

SQLite can't be reliably used in networked file systems because it heavily relies on locking to be correctly implemented. I recently had to add a check for such file systems in my application [1] because I noticed a related corruption firsthand. Simpler file formats do not demand such requirements. SQLite is certainly good, but not for this use. [1] https://github.com/lifthrasiir/angel/commit/50a15e703ef2c1af...

That's pretty broad and over-generalized. Networking file systems without good lock support is almost always a bad setup by an administrator. Both NFS and CIFS can work with network-wide locks just fine. SQLite advises against using a networking file system to avoid potential issues, but you can successfully do it.

As noted in my other comment, those "potential" issues are real and do happen from time to time. Unless SQLite gives some set of configurations to avoid such issues, I can't agree that it's over-generalized.

Re: What If OpenDocument Used SQLite?

#79

Post by Retr0id »

johncolanduoni wrote: post #51
Retr0id wrote: post #29

Earlier quoted context omitted.

You can split your data up across multiple blobs

Also you almost certainly want to do this anyway so you can stream the blobs into/out of the network/filesystem, well before you have GBs in a single blob.

Singular sqlite blobs are streamable too! But for streaming in you need to know the size in advance.

Re: What If OpenDocument Used SQLite?

#80

Post by nloomans »

Interesting read! I find the idea to use SQL queries to get only the relevant data quite convincing. I do wonder how this would work in practice though. Any changes the user makes would have to be inserted with SQL to allow for the new data to be included in SQL queries, but users also expect to be able to make changes and then not save them (or save them into a different file).

Should one make a massive transaction that is only committed when saving? It is possible to commit such a transaction to a different file when using Save As?

Or maybe for editing one would need to copy the file to a separate temporary location, constantly commit to that file, and when saving move the temporary file over the original file (this way we aren't losing the resilience against corruption SQLite offers).

Or is there a better way to do this? I don't like storing pending changes into the original file since it kinda goes against how users expect files to work (and could cause them to accidentally leak data).

Post reply on HN