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

81–90 of 109 posts

Re: What If OpenDocument Used SQLite?

#81

Post by munch117 »

ncruces wrote: post #73
munch117 wrote: post #68

Earlier quoted context omitted.

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

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.

Re: What If OpenDocument Used SQLite?

#84

Post by agwa »

ncruces wrote: post #72
chris_wot wrote: post #38

Earlier quoted context omitted.

"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 su…

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

Re: What If OpenDocument Used SQLite?

#85

Post by Mikhail_Edoshin »

duskwuff wrote: post #13
librasteve wrote: post #2

wouldn’t an XML database be easier?

You can't* index into XML. You have to read through the whole document until you get to the part you want. *: without adding an index of your own, at which point it isn't really XML anymore, it's some kind of homebrew XML-based archive format.

This applies to any secondary index. The data themselves can only be ordered by a single criteria. It may be a meaningful one, but I guess in most cases it is merely the internal ID, which means you will have to scan the whole table too.

XML was meant for documents so in most cases the sequence of elements is given. But technically if I compose XML myself I can lay it out the way I want and thus can have it sorted too. This means it will be directly searchable without an index: read a bit at the middle, find an element name, see where we are, choose head or tail, repeat.

Re: What If OpenDocument Used SQLite?

#86

Post by joseda-hg »

nloomans wrote: post #80

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…

You could insert any modifications and just mark whatever row the current saved one is

This would also work as a really crude undo tree

I don't really know if it actually goes against users expectations, Office kinda "saves" stuff for you and stores them as temporary versions anyway, to be presented in case you forgot to save

Re: What If OpenDocument Used SQLite?

#87

Post by tombert »

wakawaka28 wrote: post #44
tombert wrote: post #25

I remember I played with some software called "The Illumination Software Creator" [1], and I remember the saved project files were just SQLite databases. I actually thought it was kind of cool, because I was able to play with it easily with some SQLite explorer tool (I forget which one) and I could easily look at how the save files actually worked. I haven't really used SQLite for anything serious [2], but always fou…

What is it that makes you think Lunduke is pseudo-intellectual? He certainly doesn't try to pose as a scholar. If you are like most of his haters, you just refuse to believe that smart people can be conservatives.

I used to think he was reasonably smart but after a certain point I realized that his knowledge of basically anything he talked about was extremely surface level, and doesn’t appear to know much after that.

I disliked him before he went super conservative, but now his YouTube channel boils down to “OMG GUYS LOOK AT HOW WOKE EVERYTHING IS WOKE WOKE WOKE WOKE WOKE PEOPLE ARE HATERS ON ME BECAUSE I SAID SOMETHING THEY DONT LIKE WOKE WOKE!”

It’s typical low effort grifter stuff.

Re: What If OpenDocument Used SQLite?

#88

Post by sigwinch »

sakesun wrote: post #16

If I remember correctly Mendix project file format is simply a sqlite db. I thought the designer was lazy but it turns out it's a reasonable decision. Recently, DuckDB team raise similar question on DataLake catalog format. Why not just use SQL database for that ? It's simpler and more efficient as well.

With regard to DuckDB catalogs, I think a database is preferred for that. In particular, the tutorials assume PostGres.

Re: What If OpenDocument Used SQLite?

#89

Post by ectospheno »

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…

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.

Re: What If OpenDocument Used SQLite?

#90

Post by jockm »

chmaynard wrote: post #9

Dr. Hipp occasionally gets on a soapbox and extolls the virtue of sqlite databases for use as an application file format. He also preaches about the superiority of Fossil over Git. His arguments generally make sense. I tolerate his sermons because he is one of the truly great software developers of our time, and a personal hero of mine.

The problem is that better is not an abstract measure. It is better at what, for what purpose, in what context? I like fossil in the abstract, but it isn’t integrated well into any of my tools; there is only one hosting service I know of; and they took away the wysiwyg option from the build in wiki (a preference of mine). So it isn’t better for me

Your better will be measured against different criteria, etc.

Post reply on HN