How to Corrupt an SQLite Database File
sqlite.org
sqlite.org
Though: Multiple copies of SQLite linked into the same application. Weird and rare scenario, but why not keep the global list of open sqlite files in a global shared memory segment?
I personally like how POSIX works, and how well documented its operation and failure modes. I love systems which don't prevent foot guns, and go bang spectacularly when I foot gun myself.
It's also ironic how we don't get to like systems which do limits us in severe ways, and then get angry to a system which doesn't limit us the same way. Fun times.
Because POSIX doesn't provide features that are necessary for reliability. Workarounds, if they exist, are highly platform-specific. That I'd say is damnation of the standard.
For example, how do you, within POSIX, ensure that a write to the storage media is "durable"? People argue about what combination of various "sync" calls and open flags you need to achieve this. (Disregard the case of hardware cheating -- nothing can be done about that.)
How do you make an "owning" lock on a file: a kind of that you and only you (the owner) can remove? Why is a lock applied per for file descriptor and not the file object? What is the workaround here?
In general, POSIX + threads = damnation because there's too much per-process global state, which was kind of OK "before MT". It's clear that the standard was written before multithreading was even foreseen to be the norm, and it contains many SNAFUs wrt threads, not just files. (Signals and fork are the first to come to mind.)
Oh, yeah, don't mention Linux. It has so many extensions and additions to POSIX to alleviate POSIX messups that it's not even funny.
Edit: I just reread the spec, and it is on POSIX: They recognized that "sync" was not actually writing, and documented the real usage. Unix 5 has this to say about sync:
> Sync causes all information in core memory that should be on disk to be written out..This includes modifi~ super blocks, modified i-nodes, and delayed block I/0. > It should be used by programs which examine a file system, for example check, d.£ etc. It is mandatory before a boot.
But by System 6, Lion's book is already referring to the peculiarities of delayed writes.
https://www.postgresql.org/message-id/CAMsr+YHh+5Oq4xziwwoEf...
If you have a super-serious hardcore owner lock on a file that only the opening process can release, what happens when a buggy or hostile program locks a file and never unlocks it, even after exiting?
Observe that there is no way SQLite could fix this bug, the SQLite developers instead have to shove off the responsibility to everyone one using the SQLite library. Add a few more layers of libraries where this requirement isn't documented as clearly, and this is basically guaranteed to go wrong somewhere.
I love systems which don't prevent foot guns, and go bang spectacularly when I foot gun myself.
That's because all my code is tested for both leaks and all scenarios. None of my code ever have gone bang in production. To be honest, no service I have written ever restarted outside system reboots or configuration changes.
Having systems with no guardrails doesn't equate to having bad code automatically.
That‘s a bold claim. Reeks of hubris though.
Healthy scepticism would convince me a lot more that we can trust your products.
You may also consider if maintaining that test suite for „all cases” is a good investment of your time.
How can I make sure that I have no leaks?
1. I design software by hand. I design construction and destruction chains beforehand.
2. I implement the modules one by one, create some test suites, plug to valgrind, make sure that it has no leaks.
3. Chain the modules together, re-run the tests.
4. For every component and chain which passes the test, "Seal" the unit. Any change requires whole set of tests again.
For this set of components, since components doesn't change, test suites are also "sealed".
After every build, I have a CI/CD pipeline which runs a series of tests including, unit, module and end to end scenarios. Test the result with ground truth up to 32 significant digits. If something doesn't hold, flag the build and fail. I also keep timing values for certain states, and they shouldn't deviate much. Remember, we need speed.
We should have invalid inputs. However these inputs shouldn't reach to the processing state and just be marked invalid and thrown out. Apply the above pipeline. Implement, test and seal.
Since the stack is stable, and this is an "old school" C++ code, I don't need to migrate libraries and other stuff around much. So, the test suite I've written doesn't need maintenance unless the seal is broken.
However, with the feature I'm implementing, I need to break a couple of these seals, but no biggie. Extend a function, write a couple more unit tests, run the test suite and hammer the code, seal it again. Usual tests will go on, of course.
End to end valgrind tests are done periodically, with not every build. It takes around ~12 hours to complete that with full tracing and reporting.
I'd rather be methodical and give the code I've written a torturous shake down, instead of saying this looks good and move on. I trust myself with the code I write, but not so blindly to go over the top and say that "I'm the one". Instead I do my best, but I believe that I'm the worst coder around here, so I test to break my code. Not to validate.
I still doubt you cover "all cases". Even for a simple problem (e.g. given three integers interpreted as side lengths, decide if they result in a equilateral, isosceles, or scalene triangle), the number of cases will be daunting (in our case: 65, see Robert V. Binder, "Testing Object-Oriented Systems. Models, Patterns and Tools").
Despite the effort you chose to invest into your framework, I still doubt you achieve anything close to this depth within your system and the underlying stack.
On the other hand, I don't accept calling something damned because it's old, or has quirks or both. This is the same API (and set of standards) I work with, and I had my fair share of problems with it too.
However, I accept that no API/Standard is perfect, and work my life around it. Also we have a huge ecosystem built around it, and while it's not perfect, it's working so far.
And yes, I refuse to give my freedom to make errors, crash and burn spectacularly in the name of ease of use and abstractions. Because I need that performance, and want to be able to reach to hardware without all these layers and safety nets.
We have abstracted safety nets above POSIX level, and anyone can use that if they want.
Even manufacturers baffled how we can fry our servers. We're eating memory controllers in one generation, and on board NICs were being cooked on others for example.
XFS and EXT4 can handle a lot of abuse, incl. power loss without any problems during a heavy write. Regardless of the services we run, we didn't loss any data during a power outage, and for us, power outage means "power outage during full load".
Making sure that your scientific computation can continue from the point you left it is a big business. Nobody wants to lose three weeks or a month just because a server gave out its magic smoke.
"kill -9" is almost the same thing in most cases.
I rarely get to work on projects where we have the budget to destruction test things that hard, but I am absolutely in love with the concept.
Should be read "None of my code ever have gone bang in production, so far, that I am aware of"
But, related to the Posix thing about reusing fd's, I just ran into this a couple of days ago and it took me 6 hours to figure out.
I made a change in HashBackup to interrupt saving a big file if the backup time limit had been reached. Tested it a while, seemed like it worked fine. I was using a 15-second timeout. For whatever reason I tried it with a 5s timeout. This went bang, but in a completely different area of the program responsible for copying backup files to a destination, where it raised an exception "hey, this file was X bytes but I only copied Y". Go look at the source file, it's fine. Look at the destination file, and indeed, it is only Y bytes. Go look at the copy code, it looks fine but obviously isn't, so I start putting all kinds of debug stuff there to check file sizes, do double reads at EOF, ... nothing helps. Then I add an lseek to report the current file position and indeed it is at X, even though only Y bytes have been copied. So I realize that something else has moved this file pointer on me.
To help track that down, I override Python's os.open and os.close and display the pathname and fd so I have a history of who is using what fd's. After going through that with a microscope, I see that the file being backed up is open on fd 6, and when the timeout occurs, it gets closed. Then that fd gets used by the copy function for the source file.
BUT, there is an asynchronous read process used during the backup. It has exception handling if an fd is closed by higher levels and the read thread gets an EBADF error, then resets itself. But there is a race condition: if the fd gets reopened fast enough, the read thread will never see the EBADF error and thinks it is still reading from the file to backup, which it continues to do. Now there are 2 processes reading from the same file and mayhem ensues.
Of course it's ultimately my fault, but we do have at least 32 bits for a file descriptor number. It would be a lot nicer if Posix kept incrementing it instead of using the low numbers and then wrapped around, like Unix PIDs do. And yeah, I realize this would screw up select, dup, etc because of the way they are designed.
Nice! I'd recommend trying strace to track system calls in the future though. Monkey patching open and close will only catch code using those functions and not any c libraries or very smart people using ctypes.
Strace captures every syscall and dumps the parameters and returns.
Best example being dealing with a situation where a client's code could only have one connection to a particular external streaming API they were using and the developer in charge of connecting to it was a tad territorial and wouldn't provide me a way to access the data that actually worked for the task I'd been assigned.
Solution: strace his process with a -s argument that was larger than any read his process ever did, then backprocess it into the original bytes on the wire, then handle them myself.
Yes, this was a horrible hack, but it allowed me to prove the concept of the thing the client wanted building without causing massive political drama that would've been more trouble for everybody involved than it was worth.
Ideal for that sort of situation would (for me) likely be trapping the open and close functions and having them emit information to stderr while -also- having strace log to the same stderr so I could see how the two compare - with the caveat that whether it's viable to do that is highly variable depending on context.
But as a last comment on strace, I present an old entry from my quotefile:
<@mst> actually, I think my first thing to try would be to strace the code
<@mst> and try and match up the new value of $! with a failed syscall
<@mst> but I mean if strace was a person I'd totally be asking them out
to dinner so maybe I'm biasedhttps://peps.python.org/pep-0578/
It can be seen as somewhere in between home made monkey patching and strace. Strace is great, because it shows absolutely everything. But showing everything can also be too much, a pre filtered view is often what you really want.
If you want a similar tracing mechanism that spans your whole system I think you could do it with eBPF.
You are conflating surprising systems and flexible systems.
Something that is flexible doesn't have to be surprising. Nor does something surprising have to be flexible.
The Principle of Least Surprise says that surprising behavior in software is bad. Systems should strive not to do things that catch users off guard absent a good reason.
Instead, for example Java has given me much more surprises, and at catastrophic levels. I was using Choco Solver [0] back in the day, and I created two instances of it, attached to different classes. Which is perfectly normal, right?
Somehow they've cross linked between these two instances, affected the results they have computed, and created persistent memory leaks which needed system reboots to claim back. Java should be immune to that, but no.
Preventing that needed to run only one instance of Choco, which limited my performance greatly. Luckily, the system had a queue/consumer structure, so running only one didn't need extensive changes.
I can't comment on Choco, but no garbage collector is immune against programmer's mistakes. For example, it's easy to create leaks with observable pattern (short-lived observers not unsubscribing from a long-lived observable). IOW: know when to use weak references.
Do I need to say anything more?
No. Let me re-iterate what I mean.
Systems without guardrails are explicit, transparent and flexible. It's easy to understand how they behave. It might be a little harder to get things right, but when you get it right, there's nothing to second guess.
Systems without guardrails are low in overhead. It's possible to get raw performance.
Yes, I love these systems. I might spend a little more time and need a little more concentration when I develop stuff on these systems, but when it runs, it runs for good.
Trying to make systems foolproof by limiting them is not good. If that's good, we should all love iOS, right?
For a more specifically computer-systems example, perhaps we should consider security? - oh, wait...
Most security problems arise from memory management problems from my experience. This has nothing to do with POSIX. The problem is universal there, and we may delightfully argue that a stricter programming language like Rust can alleviate most problems, however tools like Valgrind can also find a lot of problems during a simple testing run.
If we return to aviation, flight safety and memory management, there are some standards AFAIK which ponder about determinism and memory management on these systems (and smaller "single use" systems). These systems neither know POSIX, nor has the capacity to do so (they spend all their power to catch and tag the object they're tasked with).
Again, the security, reliability and other footgun related stuff ends at the program we're designing and running, and not on POSIX itself.
This just does not hold up at all, in practice. It is not at all uncommon for footguns in software go undetected in testing, and lie in wait, like so many landmines, for someone to set them off - or for some blackhat to find them.
> Most security problems arise from memory management problems from my experience.
That does nothing to somehow compensate for, or otherwise render harmless, those that are not.
More generally, by describing various ways you can seek to avoid footguns, you are simply providing evidence that they are a problem, not a feature.
> when you get it right, there's nothing to second guess
Given the topic is "how to corrupt your db file," does that mean they didn't get it right yet? Or that the underlying platform prevents them from getting it right?
As a person who've developed sizeable projects with both, you hit the nail on the head (and yes, Java can do amazing things in the foot gun department, believe me). However, my favorite language duo is C/C++ at the end of the day.
> Given the topic is "how to corrupt your db file," does that mean they didn't get it right yet?
This means you (as the developer) has a misunderstanding between you and the system. The article contains a very large swath of problems from hard to understand implementations (e.g. locks), to hardware which doesn't listen you to bugs in SQLite itself.
So, in the article there are a lot of cross-platform bad practices which one's warned about. Shrinking this to "POSIX is bad!" is just wrong. This is what I'm trying to point out.
Similar problems occur if you do in-place upgrades of software using dynamic libraries.
For what happens when you do that on Linux: the file entry gets deleted, but disk space doesn't get deallocated until the last program using the file has exited. If you replace the file, the program will still be referencing the old file (now unreferencable by any name/path, except possibly through /proc/../fd).
I don't know how you could avoid this, and it has me wondering if you understand the issue. This is basically the file descriptor version of a use-after-free bug. I'm not one of those "rust fixes everything" people, so I don't answer this about just anything, but I think this issue would completely disappear in a memory safe language and especially one where the wrapper for file descriptors had good prevention of race conditions closing a file, so it hardly seems fair to blame the filesystem API.
I mean, in some cases you could blame the API for high frequency of application bugs, but using a closed file seems like a bug category that is unreasonable to do so for. The issue of use-after-free is larger than the filesystem API.
I understand the issue. Someone else has already mentioned how you could avoid this: allocate fds randomly instead of sequentially. This would significantly reduce probability of accidental reuse. If you widen fds to 64 bits then you're pretty much good.
Yes, it'd break syscalls that rely on sequential allocation like select. It just shows how POSIX has painted itself into a historical corner, alongside with many interactions with threads being a kludge.
Indeed it does not, but it's not "random" either, i.e., they do get reused, probably rather quickly, so it does not solve the problem.
Mark Russinovich or Raymond Chen (I think) had an article about why it's a bad idea to forcibly close files that are in use and they gave exactly that as a reason: the closed handle gets valid again, but for a different object, and, voila - corruption!
you're talking about the kernel interface that eventually maps onto a filesystem
This is an ownership issue is my point. It's not exactly memory safety but the same concept applied to an integer rather than a pointer.
… though if you’ve got a procfs around, /proc/self/fd/* is ripe for the writing.
I strongly recommend finding a different approach ... but it totally worked.
Usually none of these issues are dealt with in the shared memory sdk you’ll be using, so you gotta model it with mental experiments just like you would a lock free implementation in a threaded process.
Simple no, doable yea, error prone you betcha.
I honestly think this one of the greates advantages of POSIX systems over Windows. If I as a (super)user want to delete a file I should always be able to do that and applications will just have to deal with it.
What exactly do you mean by “providing no means to prevent it”?
You can replace the open files on Windows (most oth time, see my other comment here), but the decision to reboot is not a technical one (and back in the days many invested in no reboot upgrades) but to have less headache on a corner cases.
no. restarting some daemons one by one when it's convenient is wildly different from taking down the entire system
They would all use the same reserved "name". It's possible to atomically create or open a SHM segment with a given "name". Then have a header at the start of the segment with critical metadata like signature, version, etc. Barf unconditionally if the metadata is found to be invalid (i.e., some other process deliberately used the name to sabotage sqlite [1]), or, in addition, have an option to not use SHM.
[1] The situation is not different from some process sabotaging another by deleting or corrupting well-known files.
As a practical matter, you really have to work hard to get an application to use two different copies of SQLite at the same time - all the while avoiding symbol collisions on link. You can do it, but it takes some work. And then on top of that your application has to decide to open two or more connections to the same database file, using different copies of SQLite in each case.
The more common failure mode I've seen is a library bundling and inlining an old version of a database API in a way that means it overrides the shared object I was expecting my code to link to, and alarums and excursions resulting thereby.
(I don't imagine SQLite would -break- in that situation, but I doubt my application code would be any less unhappy than in the cases of that problem I've encountered in the wild)
It's easy: create DLL/.so A with statically linked sqlite. create DLL/.so B with statically linked sqlite. Make a program using both DLLs.
I used two different C# libraries to access the same database. And under the hood, they both used a different SQLite library, with different native SQLite builds (of the same version though).
Took me some time to figure out…
....ah, but if they were tripping over each other in memory I can see that going sideways.
What exactly happened?
But it’s also possible, that SQLite was built by different compilers/toolchains and that did the trick.
What happened: my database files were always corrupted and needed repair. The application did only a few writes, and sometimes the changes just disappeared.
Edit: the SQLite website says: „But, if multiple copies of SQLite are linked into the same application, then there will be multiple instances of this global list.“
SQLite is great, but I would not use it in a multi-machine, multi-processing environment.
Developers do need to understand that it's SQLite, not a poor man's Postgres. This require correct engineering, and the SQLite docs are in a class of their own in patiently explaining in simple terms how to do this.
If you embrace what SQLite is, you can do a remarkable number of things with it, safely and reliably.
> SQLite docs are in a class of their own in patiently explaining in simple terms how to do this.
Yes! I love reading the SQLite (and Fossil!) docs. They're so beautifully written!
Yeah great, you shouldn't. SQLite is meant to be replacement of custom files to store data by apps (mobile apps, desktop apps, server apps). This isn't meant to scale to millions of users, but just store data in a structured manner.
You could still allow processes to opt into non-POSIX behavior.
I should mention that I was able to repeat the corrupting problem reliably. The problem disappeared when I moved to a working directory that was not monitored by OneDrive.
I was setting up a very simple db from a few CSV files, for use in a data science exercise for a course. There was nothing interesting happening in my code at all-- maybe a simple table join sql query, at most.
Is it possible to turn off transactions alltogether in SQLite? So they function like a MyIsam table in MySql or a Aria table in MariaDB?
I have crunched many billions of queries over the last years in MySql and then MariaDB. I like MariaDB because it offers tables that are way faster due to no transaction overhead.
I know this goes against the popular opinion to use transactional tables for everything. But depending on the task at hand, the performance gain can be very well worth turning transactions off.
I consider trying SQLite. But if you cannot switch off transactions, performance will probably not be up to par.
PRAGMA synchronous=OFF;
PRAGMA journal_mode=OFF;
I don't there is a way of turning off transactions completely, you can either do it explicitly or implicitly.
However, if you do it explicitly (wrapping BEGIN/COMMIT manually), you can batch all your INSERT/SELECT statements inside one transaction instead of one transaction per query. This should make things a bit faster as the lock would only have to be acquired/released once instead of once per operation.
At least according to my experiments with MyISAM tables vs InnoDB. I never got InnoDB tables to perform as well as MyISAM tables.
How often do you experience "errors" that corrupt your database files? How much performance downgrade would you be willing to take to avoid those?
>How often do you experience "errors" that corrupt your database files?
I don't know what you mean by this sentence. ACID has nothing to do with that. It goes way beyond that. For example, confirming that updates have been written to disk has nothing to do with database corruption. You can lose writes/data without database corruption.
[1] https://www.mail-archive.com/sqlite-users@mailinglists.sqlit...
But it does not give you the performance you get if there are no transactions in the first place.
Is this also true of SSDs?
- I tested four NVMe SSDs from four vendors – half lose FLUSH’d data on power loss (twitter.com/xenadu02)
Unfortunately for me, they also had a thing where if a write-back battery passed a certain number of hours in operation the array would no longer trust it and defaulted to then not using it at all - which is entirely reasonable right up until the point where your systems team won't buy a replacement.
I once offended the lead sysadmin at that job on a phone call so much that he had to pass me off to his junior because I'd been telling him for months that the battery needed replacing and basically my opinion was "yeah, have fun waiting an hour while that system fscks" interspersed with helpless laughter.
(in my defence, I wasn't paid to be on call and he called me at 2am during a good friend's 30th birthday party, so my capacity for diplomacy was even more limited than normal)
That made me nervous!
I don't know enough about how Docker implements volumes under the hood to know how likely (or not) it is to break filesystem behavior that SQLite depends on for its transactional guarantees.
Perhaps someone here does?
What you should worry about though is the underlying volume driver and its storage type. Volumes can be simple bind mounts to your local disk, being more or less identical to no container. But volumes can also be attached straight to a network disk, where same dangers apply as if you would have mounted that network disk on host.
Volumes are made exactly for holding databases. And also bind mounts should work without any issue in most configurations.
But if there is some kind of network/virtualization between the physical disk and the container, you should investigate it in more detail.
Source: https://youtu.be/Jib2AmRb_rk
I'd link the exact timestamp, but then you might watch less than the entire talk, which would be unfortunate.
But the grammar would suggest they do say it by pronouncing each letter of the acronym separately.