SQLite

There was a bug in an older version of SQLite that CheckPoint was able to exploit. That bug has been fixed for a long time. It was fixed even before the referenced talk was given. They had to use an older version of SQLite in that talk so that the attack would work. The CheckPoint attack was a clever new idea, to be sure, and so more recent versions of SQLite have added new defenses to this kind of mischief, such that even if new bugs in SQLite are found, there will be defense in depth and attacks similar to the CheckPoint attack will still be unlikely. And yet for some reason, because we had that one bug, long ago, people keep saying that SQLite is "unsafe" years after the bug was fixed. Why is that?

See https://www.sqlite.org/security.html for tips on safely opening SQLite database files that have been tampered with by a hostile agent.

That bug was fixed long ago, even before the referenced video was produced.

The described attack is clever. It exploits the fact that an attacker might alter the schema so that it invokes an SQL function with side-effects when an app simply tries to read from a table. And depending on those side-effects, an exploit might be possible. In the video, there was a bug in a built-in SQL function that caused exploitable side-effects. But that bug was fixed long before the video was even produced. The examples in the video were from an older version of SQLite. They did not work for the latest SQLite release on the day that lecture was given.

Since the checkpoint.com attack was described (in the video and elsewhere) new defense-in-depth features have been added to SQLite to make similar exploits increasingly unlikely.

(1) Built-in SQL functions that have side effects cannot be used in the schema. Side-effect functions can only be invoked directly by the application.

(2) When applications register their own custom SQL functions, they can now mark those functions as "direct-only", meaning that they are prohibited in the schema.

(3) Run-time and compile-time options are available to prohibit the use of SQL functions in the schema that are not explicitly declared to be safe for use in the schema - that is, functions without side effects. This is for use in legacy applications that might have been created before the per-function flag that prohibited use within the schema was available. It is also an extra layer of defense for complex applications that might add hundreds or thousands of side-effect SQL functions - to ensure that the "direct-only" flag is not accidentally omitted from one of them.

(4) Run-time options are available to disable triggers and views in applications that do not need them. This is not necessary to avoid an exploit, but it does provide an additional layer of defense.

See https://sqlite.org/security.html and especially paragraph 1.2 item 8 for additional information.

All versions of SQLite from 3.0.0 (2004-06-18) through 3.34.0 (the latest) use the same database file format. So if two applications use different versions of SQLite, it shouldn't matter. The database file will be the same.

Now, if one application uses a more recent version of SQLite and also makes use of some new feature (say, for example, generated columns which were added in release 3.31.0) then that might result in a database file that is unreadable by older versions since the older version won't be able to interpret the generated column syntax. But if you assume that all different versions of SQLite that you use have support for all of the features used in the database file (a reasonable assumption in most cases) then the database files are completely portable across versions. Different versions of SQLite can read/write the same database file concurrently. And newer versions of SQLite are always able to read/write databases created by older versions of SQLite, without exception or precondition.

So perhaps the "sqlite format conflict" example was not the best, in as much as it will always work as long as the package manager installs the most recent version of SQLite.

All of the above is also true for the API.

GCC 5.4.0 is what came installed by default on my Ubuntu 16.04 desktop. I might change to a different compiler except that would be a lot of work, since I would then need to rerun hundreds of historical benchmarks, and it is not clear what would we learn by using a different compiler or compiler version.

We use many different compilers and compiler versions for correctness testing - various versions of GCC, LLVM, and MSVC with various optimization settings. That's important because different compilers generate different code and we want to ensure that they all generate correct code. (SQLite has found bugs in historical versions of all of those compilers!) But for benchmarking, as long as the same compiler and version is used consistently, does it really matter which compiler is used?

Additional compiler comparison data (from 2017): https://www.sqlite.org/footprint.html

The problem that prompted me to make this extension to SQLite was a web-page request, so it needs to be processed with low latency. I initially tried loading the graph into memory and processing it that way. But there are approx 100K nodes in the graph, only a few dozen of which are relevant to the answer. It took a lot of time to pull in 100K nodes from disk. Running the query entirely in SQL is not only much less code to write, debug, and maintain, it is also much faster.

I wrote https://wapp.tcl.tk/ for occasions such as this. SQLite is built-in, of course.

One key feature Wapp is that identical code can be run as

+ CGI from Apache

+ SCGI from Nginx

+ As its own stand-alone webserver

So, for development work on your desktop you run it as a standalone webserver. It even pops up its root page in your webbrowser automatically when you launch it. Then, once you get things working there, you "scp" the one small source-code file that implements your application up into a CGI-enabled directory of you server, and is just works.

SQLite described (with links to details) here: https://www.sqlite.org/arch.html

Pikchr author here

It is difficult to find the right balance between a simple language that requires "toil" and a less toilsome language that is also more complex and requires more effort to learn and master. I'm very open to new ideas of how to make the Pikchr language less toilsome without a corresponding increase in complexity. Expect improvements to occur. Your suggestions are welcomed

I envision Pikchr operating in the same nitch as Markdown. (Indeed, Pikchr was originally designed to augment Markdown-based documentation.) An artificial language like PIC/Pikchr is always going to have a steeper learning curve than a GUI diagram drawing tool, just as Markdown documents are always going to be a barrier to entry for pointy-clicky people who prefer MS-Word or Google-Docs. And yet, Markdown persists and even flourishes. Why is that? If point-and-click really is so much better, why are there so many Markdown technical documents out there?

A key advantage of Markdown, which helps it thrive in a point-and-click world, is that it is very simple to learn and requires no proprietary tools. Pikchr is not as simple, though it is simpler (I think) than any other artificial language for graphics that I have encountered, and I've tried to make the Pikchr code as accessible and as easy to integrate as I know how. I'm on a mission to make Pikchr simpler still. Your feedback on how to do that is appreciated.

The TH3 test harness for SQLite (https://www.sqlite.org/th3.html) supports a virtual filesystem in which we can create test database files that appear to be very large but that don't actually contain much data or use much space.

We also have a simple utility program in the SQLite source tree (https://www.sqlite.org/src/file/tool/enlargedb.c) that lets you create a massive database file using a sparse file (https://en.wikipedia.org/wiki/Sparse_file) on systems that support that kind of thing.

Author of SQLite and Fossil here - I have not forgotten monotone! Indeed, Fossil itself is inspired by monotone documentation (though not the implementation). This is credited at https://fossil-scm.org/fossil/doc/trunk/www/history.md

Many reasons for this:

(1) Because SQLite is just a function call whereas PostgreSQL is a round-trip message to a separate server process, MRigger was able to run many more test cases per second on SQLite.

(2) We fixed bugs faster in SQLite, allowing MRigger to continue testing SQLite sooner.

(3) SQLite has much stronger backwards compatibility guarantees than PostgreSQL. We have to continue to support design errors made decades ago, whereas PostgreSQL gets to walk away from their poor design choices with each major release. For this reason, SQLite is rather more complicated than you might imagine.

(4) Many of the bugs found by MRigger had to do with the innovative (and controversial) decision by SQLite to use flexible typing rather than strict, rigid typing. SQLite allows you to put text into an INT column, for example. PostgreSQL has a more traditional design that simply does not allow that kind of thing, and hence many of the bugs found by MRigger are simply not applicable to PostgreSQL.

(5) The PostgreSQL developers are very clever people and write some of the best software around. When we were developing the cross-DBMS "sqllogictest" test suite for SQLite (https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki) we were able to crash every DBMS we tried it on, except for PostgreSQL. To this day, when somebody has questions about whether or not the behavior of SQLite is correct, our reflexive reply is "What Does PostgreSQL Do?"

The attack is clever and original. AFAIK, nothing like it has ever been seen before.

Since this attack came to light, SQLite has added features so that an application can ensure that views and triggers do not have side-effects (outside of the database file itself). And if there are no side-effects then the attack is basically harmless. Sure, the attacker can still exfiltrate or corrupt data, but the attacker had to have write access to the database file in order to carry out the attack in the first place, so exfiltrating or corrupting data is not an issue - they could already do that. See a quick summary at https://sqlite.org/forum/forumpost/8beceed68e

This concern was just raised on the SQLite Forum (probably after showing up here). See my reply at https://sqlite.org/forum/forumpost/8beceed68e for additional insights into the problem and recent SQLite enhancements to address it.

Storing content in SQLite is actually faster than writing the equivalent content directly to disk, in many situations. See https://www.sqlite.org/fasterthanfs.html for discussion, caveats, and links to source code where you can verify these claims for yourself.

What happened:

1. OSSFuzz reports a bug against SQLite.

2. SQLite dev tries to fix the reported problem but is unable to repro the bug on his desktop

3. SQLite dev replicates the OSSFuzz build environment which uses clang-11.0.0 and is then able to repro the reported bug

4. SQLite dev finds that the bug isn't in SQLite at all, but rather in clang-11.0.0

5. SQLite dev patches SQLite to work around the clang bug, posts a brief note about this on the SQLite forum, and goes to bed.

6. The post on the SQLite forum is picked up by HN while the SQLite dev is asleep. LLVM devs read HN, isolate and fix the clang problem, all before the SQLite dev wakes up.

It was late at night. I didn't realize the clang-11.0.0 was unreleased until I saw this HN thread the next morning. The only reason I was using clang-11.0.0 is because the OSSFuzz bug report against SQLite used clang-11.0.0 and I couldn't get the (alleged) SQLite problem to repro using any other compiler.

That was my business plan: Do the intense testing required for avionics, then sell the test cases to aviation manufacturers. That plan didn't work out - I've never sold the tests to any aviation manufacturer; not one. But the TH3 test harness has had side benefits that I did not anticipate, not the least of which is that it allows us to maintain a complex code base that is run on billions of devices with just a few developers.

On-line docs describing the design and internal operation of SQLite include: https://sqlite.org/arch.html https://sqlite.org/optoverview.html https://sqlite.org/opcode.html https://sqlite.org/queryplanner.html https://sqlite.org/atomiccommit.html

Inspired by this post, I started yet another: https://sqlite.org/draft/howitworks.html

The issue with NFS and SQLite is that posix advisory locks do not work, or do not work well, on many NFS installations. As long as advisory locks work over NFS, or as long as there is only one client trying to access the database at a time, so that locks are not really needed, SQLite works fine over NFS. NFS is a lot slower, but that's just the nature of NFS and SQLite can't do anything about that.

You can ask SQLite to use dot-file locking instead of posix advisory locking. Or you can ask it to simply ignore locking all together. In both cases, SQLite will work on even a broken NFS system, though with the corresponding concurrency limitations.

If you will kindly send your problem query to the SQLite developers, we will do what we can to improve the query planner to make it run faster.

I did not. This is the first I've heard of it.

Just to be clear: We do not have any support contracts with the US military, nor any other US government agency, nor any other government entity, either inside or outside the US. Not that we would turn down such work if it were available, it is just that is has never come up.

I don't actually remember making that argument. But I will try to reconstruct my thinking...

(1) Keeping an entire repo in a single file is a better abstraction. There is just one file to move around or rename. There is a single icon on your desktop to drag around or double-click on. There is a single file to attach to an email. There is a single file to measure the size of when judging the size of a repository. And so forth.

Lots of programs bundle multiple entities into a single file for convenience like this. For example, a DOCX file is really a ZIP archive containing lots of individual pieces. Would you rather your document be a single DOCX file, or a directory full of the individual pieces. Which would be more convenient to use, do you suppose? How is a VCS repository different from a DOCX file in this respect?

Another way to look at this: Breaking up a repository into a directory full of separate files exposes internal implementation details to the user.

(2) Perhaps I was making the argument that a relational database is better than a key/value database for holding a repository. (A directory full of files is just a kind of key/value database after all.) There are countless reasons why relational databases work better than key/value databases. One example: With Git, given an individual check-in, it is difficult to discover the descendants of that check-in. It is so difficult, in fact, that none of the common Git tools provide that capability, and Git workflows are engineered (perhaps subconsciously) to avoid the need to ever figure out what comes after a specific check-in. But if Git used a relational database to store content, finding the descendants of a check-in would be a fast and simple query.

(3) I/O to a single relational database is faster than I/O to individual files on disk. See https://www.sqlite.org/fasterthanfs.html for details.

"blockchain" is self-descriptive, easier to pronounce (only two syllables instead of three), and easier to spell correctly. :-)

My experience in developing and maintaining Fossil is that the hashing speed is not a factor, unless you are checking in huge JPEGs or MP3s or something. And even then, the relative performance of the various hash algorithms is not enough to worry about.

> if I understand correctly then Fossil's migration is straightforward because they did not address the same issues Git chose to.

I think more is at play here.

(1) You can set Fossil to ignore all SHA1 artifacts using the "shun-sha1" hash policy.

(2) The excess complication in the Git migration strategy is likely due to the inability of the underlying Git file formats to handle two different hash algorithms in the same repository at the same time.

But, I could be wrong. Post a rebuttal if you have evidence to the contrary.

Just to be clear: Every time you modify a file, the new changes get put in using SHA3. In an older repository, any given commit might have some files identified using SHA1 (assuming they have not changed in 3 years) and others identified using SHA3.

For example, the manifest of the latest SQLite check-in is see at (https://www.sqlite.org/src/artifact/29a969d6b1709b80). You can see that most of the files have longer SHA3 hashes, but some of the files that have not been touched in three years still carry SHA1 hashes.

An attack like what you describe is possible if you could generate an evil.c file that has the exact same SHA1 hash as the older floppy.c file. Then you could substitute the evil.c artifact in place of the floppy.c artifact, get some unsuspecting victim to clone your modified repository, and cause mischief that way. Note, however, that this is a pre-image attack, which is rather more difficult to pull off than the collision attacks against SHA1, and (to my knowledge) has never been publicly demonstrated. Furthermore, the evil.c file with the same SHA1 hash would need to be valid C code that does something evil while still yielding the same hash (good luck with that!) and Fossil (like Git) has also switched over to Hardened SHA1, making the attack even harder still.

As still more defense, Fossil also maintains a MD5 hash against the entire content of the commit. So, in addition to finding evil.c that compiles, does your evil bidding, has the same hardened-SHA1 hash as floppy.c, you also have to make sure that the entire commit has the same MD5 hash after substituting the text of evil.c in place of floppy.c.

So, no, it is not really practical to hack a Fossil repository as you describe.

> I only wish there was a way to open an http-fetched SQLite database from memory so I don't have to write it to disk first.

The sqlite3_deserialize() interface was created for this very purpose. https://www.sqlite.org/c3ref/deserialize.html

"embedded database" does not imply "serverless database". Other embedded RDBMSes run servers; they just run the server as a separate thread rather than a separate process. SQLite is different in that there is no separate thread of control. SQLite runs in the same thread as the application that calls it. There is no separate thread hanging around to clean up or handle background tasks after an SQLite function call returns.

Complete history of the document in question is here: https://www.sqlite.org/docsrc/finfo?name=pages/serverless.in...

It was, indeed, written in 2007, but based on ideas that predate that.