SQLite is not a toy database • Hacker News Board

Re: SQLite is not a toy database

#361

Post by indigo945 » Wed, Mar 31, 2021, 8:38 AM UTC

SQLite wrote: post #99

indigo945 wrote: post #47

Earlier quoted context omitted.

While this makes things easy, you should not do this for any application file format where you except your users to share files. This is because opening a SQLite database file makes the SQLite library execute any arbitrary code that may be stored in that file. [0] Therefore, SQLite is really only suitable for local-only file formats, such as configuration files, and not for files that users will e-mail to each other.…

This is misinformation. SQLite does not execute arbitrary code found in the data file. There was a bug, long since fixed, that could be used by an attacker to cause arbitrary code execution upon opening the database file. The referenced video talks about it. It was a very clever attack. But the bug that enabled the attack was fixed even before the talk shown in the video was given. Let me say that again: SQLite does…

Are custom FTS3 tokenizers disabled by default on current SQLite versions? The documentation does not mention it (and also doesn't warn of the security concern around enabling them). Otherwise, I do not see how the attack vector discussed in the video could be "fixed". The security guideline recommends disabling triggers and views, which is also a logical remedy, but seems brittle, nevermind also impractical for a wide range of applications.

The documentation on the fts3_tokenizer function merely states that

    Prior to SQLite version 3.11.0 (2016-02-15), the
     arguments to fts3_tokenzer() could be 
     literal strings or BLOBs. They did not have to be bound
     parameters. But that could lead to security 
     problems in the event of an SQL injection. Hence, the
      legacy behavior is now disabled by default.

However, it does not discuss any mitigations for the case where SQL injections are not needed, because the attacker controls the database file.

Re: SQLite is not a toy database

#362

Post by SQLite » Wed, Mar 31, 2021, 11:21 AM UTC

indigo945 wrote: post #361

SQLite wrote: post #99

Earlier quoted context omitted.

This is misinformation. SQLite does not execute arbitrary code found in the data file. There was a bug, long since fixed, that could be used by an attacker to cause arbitrary code execution upon opening the database file. The referenced video talks about it. It was a very clever attack. But the bug that enabled the attack was fixed even before the talk shown in the video was given. Let me say that again: SQLite does…

Are custom FTS3 tokenizers disabled by default on current SQLite versions? The documentation does not mention it (and also doesn't warn of the security concern around enabling them). Otherwise, I do not see how the attack vector discussed in the video could be "fixed". The security guideline recommends disabling triggers and views, which is also a logical remedy, but seems brittle, nevermind also impractical for a wi…

See https://www.sqlite.org/fts3.html#custom_application_defined_...

Since 2016, arguments to the fts3_tokenizer() function must be variables (ex: ? or :var or @var or $var) to which values are supplied by the application at run-time using sqlite3_bind_pointer(). There is no way to do this from pure SQL script. Nor is there any way to do this from within a view or trigger. There is no way to invoke fts3_tokenizer() from a maliciously corrupted schema or database.

Re: SQLite is not a toy database

#363

Post by antonvs » Sat, Apr 03, 2021, 6:16 PM UTC

__jem wrote: post #344

antonvs wrote: post #331

Earlier quoted context omitted.

I think this org chart issue has been overstated. If you have two services that are completely orthogonal, then combining them into a single application for deployment and operation purposes can be limiting. Developers of all people should understand the benefits of decoupling. There are a lot of downsides that come with jamming a whole lot of unrelated functionality into the same deployment unit. It's similar to the…

I think the deployment benefits have been even more overstated. This is an absolutely enormous trade-off between logical modularity and run time operational complexity. If your devs aren't good enough to enforce modular design in a single application, what makes you think they're good enough to handle complex distributed systems?

> If your devs aren't good enough to enforce modular design in a single application, what makes you think they're good enough to handle complex distributed systems?

That's a simplistic take on the reasons that monoliths tend towards breaking modularity.

What makes me think managing microservices is perfectly viable is that the last three companies I've been at have been able to do it successfully, with a typical mix of good and less good developers, including cheap offshore devs.

As for handling "complex distributed systems," in many cases all that's really needed is something like a managed container platform. Services like Fargate or Cloud Run, or managed Kubernetes, can do a good job of this. Developers can deploy and publish new services with minimal effort, and most of the operational complexity is managed by the platform.

You do ideally want someone paying attention to overall architecture to avoid obvious pitfalls, such as effectively doing distributed joins via REST calls, and so on. This isn't that hard to understand, though, and teams that don't do the necessary architecture upfront tend to figure it out once they run into those problems themselves.

Re: SQLite is not a toy database

#364

Post by jyrkesh » Mon, Apr 05, 2021, 9:11 PM UTC

bionoid wrote: post #327

jyrkesh wrote: post #217

Earlier quoted context omitted.

I learned this the hard way when I stuck some app containers down on a few RPis and mapped the folder of stateful stuff (including a SQLite database) to an NFS share on my NAS. It's....not great.

The FAQ suggests that NFS is problematic for concurrent access: > But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. https://www.sqlite.org/faq.html#q5

Oh yeah, I read all that after I started seeing problems, I know I'm in the wrong here. It works just good enough that I haven't addressed it yet (I just kick over the process when things get weird, and the db usually recovers just fine).