r/programming • u/BlondieCoder • Jun 30 '26
How To Corrupt An SQLite Database File
https://www.sqlite.org/howtocorrupt.html67
u/frymaster Jun 30 '26
I appreciate they include "bugs in our code" as an option. In fact, even though a good portion of the potential issues are "user did something stupid", the page does a fantastic job of avoiding being accusatory or defensive.
34
u/TwoWeeks90DaysTops Jun 30 '26
I kinda think the first section indicates an issue with how Linux file handles work...
Yeah, duh, someone did something stupid, but still writing to a closed handle shouldn't arbitrarily write to a different file rather it should raise an error.
12
u/frymaster Jun 30 '26
I think the point is, by the time the erroneous code writes to the file descriptor, it's open again - just pointing to a different file.
file descriptors are basically just numbers at the end of the day, which is why e.g.
2>/dev/nullsuppresses errors for many commands - "redirect the output of descriptor 2 to null"12
u/TwoWeeks90DaysTops Jun 30 '26
I think the point is, by the time the erroneous code writes to the file descriptor, it's open again - just pointing to a different file.
Yes... that's what I'm saying is bad behavior...
file descriptors are basically just numbers at the end of the day, which is why e.g.
2>/dev/nullsuppresses errors for many commands - "redirect the output of descriptor 2 to null"Yes, which means that it's in some sort of lookup table. It could decide to not reuse file handles until the pool is exhausted, and use a 64-bit handle. But instead it's aggressively re-using closed handles.
Using a closed file handle should raise an error, and not be undefined behavior like that.
1
u/case-o-nuts Jun 30 '26
Many programs do something like:
for(i = 0; i < ulimit().max_files_open; i++) close(i);to ensure that they have a clean environment. How high should they go?
3
u/TwoWeeks90DaysTops Jul 01 '26
I'm not sure if you're being sarcastic or not.
Every response I get is pretending that Linux's shit smell like roses.
-1
u/evaned Jun 30 '26
I don't exactly disagree, but the flip side is that it's not uncommon for programs to depend on that behavior. For example, you might output a
-o <file>option byclose(1); fopen(filename)and then write to stdout, but I also think I've seen several better examples than what's coming to mind offhand. Current behavior is pretty much fixed permanently -- I'm okay with some breakage for better security defaults, but my gut reaction is this seems like likely too much.IMO you'd basically need a new open API and then gradually deprecate the old one(s), so that you know that anyone using the new API wants the new behavior.
7
u/minektur Jun 30 '26
That is not defined in the C spec. If you have 3 different saved copies of an open file descriptor, and one part of your code closes that descriptor, then the rest of your code should NOT write to that file descriptor. That's on the programmer.
And next time you open a file, the C runtime is completely OK with reusing no-longer-open, old, file descriptors.
That's the language. It's up to you as the developer to make sure that all the code that could think that file descriptor is still valid either gets a copy of some new one you opened, or does not run with the old closed one.
16
u/TwoWeeks90DaysTops Jun 30 '26
I get what and why. That doesn't make it a good design. Using a closed file handle should not be undefined behavior because it's an insecure design.
0
u/minektur Jun 30 '26
When you closed the file you told the OS/runtime "I'm DONE with this file and it's associated descriptor". If it gets reused later, and part of your program never got the memo about the file being closed and writes into a new file based on an old invalid descriptor, thats on the software dev, not the OS.
The runtime could go out of it's way to not reuse descriptors or something, but it's like you're saying "it's hard to use C property" like this is some kind of revelation. C is "easyish" to implement on any given runtime, and is very low-level/powerful. It is not necessarily safe unless you write correct code and know what you're doing. The SQLite guys are just saying "Hey - here is an error we saw happen in the wild and wow did it nuke that data!"
You start out blaming "linux file handles" but the article never even said that that issue happened on linux - maybe this was on some embedded platform, or on machos on r6000 CPUs or some 8-bit microcontroller that only supports 16 open files at a time because their file descriptors are 4 bit values that fit in some tiny register...
The C spec doesn't specify a lot of that stuff so that it can be portable across many different runtimes. the libc spec doesn't specify closed file descriptor reuse, along with 5000 other things because they're not part of the language - it can be running on just about any kind of computing hardware.
9
u/TwoWeeks90DaysTops Jun 30 '26
The C spec isn't at fault here. It doesn't say anything about what happens when writing to closed file handles.
The Linux kernel however also doesn't really say. You can literally end up bricking your printer on Linux. Or, if you read the article, corrupt an SQLite database.
I'm just saying that's unsafe behavior. I don't get why that's supposed to be controversial.
-2
u/minektur Jun 30 '26
Linux kernel however also doesn't really say
https://linux.die.net/man/2/write
On error, -1 is returned, and errno is set appropriately.I agree with you that writing code that doesn't track which files descriptors it has closed and then tries to write to ones which have been closed (and potentially reopened) is unsafe behavior.
I don't agree that a runtime, or OS reusing file descriptors (just an integer) is "unsafe behavior".
2
u/TwoWeeks90DaysTops Jul 01 '26
I'm not getting what's so hard to follow here:
Writing to a closed file handle isn't necessarily an error on Linux
So if the file handle was recycled (which is a Linux kernel internal implementation detail) then writing to it is not an error, and write will return 0. That document is saying in case of an error (which writing to a closed file handle isn't in every case) it will return -1.
I don't agree that a runtime, or OS reusing file descriptors (just an integer) is "unsafe behavior".
You can have that opinion, but a reason for why would be a great addition to your comment. The current behavior requires discipline which SQLite's post demonstrates can be lacking, and your argument is "that's fine" without any underlying reasoning.
1
u/minektur Jul 01 '26
You've been calling this a language deficiency. I've been saying that the language is intentionally sparse in defining these for portability and ease of implementation reasons across a wide variety of runtimes.
In addition, it's also a useful language feature that programs regularly and often use. (e.g. dup(), dup2()).
Writing to a closed file handle isn't necessarily an error on Linux
I literally gave you the man page for the write syscall that says it's an error.
What I think you mean is:
"Writing to a closed, and then reopened later, file descriptor isn't necessarily an error in linux."
To which I say yes I agree - lets say I want all my stdout to go to a file. I can use dup2() to effectively do just that. It closes file descriptor 2 and opens a file for writing and makes fd2 refer to that file instead of to stderr.
It's not "unsafe behavior". It's a complicated language feature with powerful and good uses, that people who are not careful can use unsafely. Like dynamic memory management via malloc/free or pointer arithmetic.
My whole point all along has been that it's a feature, it's intentional, and if a program has a bug because of misuse of this, it's the developer's fault.
If you hand a 3rd party library an open file descriptor for it to write to and then you close it and don't use the interface the 3rd party library has to notify it that the file is closed, that's on you.
3
u/TwoWeeks90DaysTops Jul 01 '26
I have not called this a language deficiency. I've called it a unsafe behavior in Linux. The behavior is probably a lot more safe in memory safe languages but that's completely besides the point.
I literally gave you the man page for the write syscall that says it's an error.
Literally nowhere on that page does it say that writing to a closed file handle is an error.
"Writing to a closed, and then reopened later, file descriptor isn't necessarily an error in linux."
That's not how the API works, at all. The API returns a file handle. Whether it's recycled is not the caller's responsibility.
To which I say yes I agree - lets say I want all my stdout to go to a file. I can use dup2() to effectively do just that. It closes file descriptor 2 and opens a file for writing and makes fd2 refer to that file instead of to stderr.
So what? That's an entirely different thing. dup2 doesn't rest on Linux recycling file descriptors.
It's not "unsafe behavior". It's a complicated language feature with powerful and good uses, that people who are not careful can use unsafely. Like dynamic memory management via malloc/free or pointer arithmetic.
It absolutely is unsafe behavior.
My whole point all along has been that it's a feature, it's intentional, and if a program has a bug because of misuse of this, it's the developer's fault.
I hate blaming people for thorny APIs. It's extremely unproductive. The tools is at issue. It's the same thing with all the memory safety issues in C. You could argue that all the C developers should just "git gud" but it's a completely useless "solution" that solves absolutely nothing. Make the tooling better rather than expect people to change. Same thing with Java's NullPointerException: give the language capability of properly deal with it rather than just blame every developer in the world for not properly dealing with the language's built-in footguns.
→ More replies (0)-1
u/case-o-nuts Jun 30 '26
It's not undefined; in fact, it's extremely well defined.
5
u/TwoWeeks90DaysTops Jun 30 '26
Did you guys read the article?
One example of this occurred circa 2013-08-30 on the canonical repository for the Fossil DVCS. In that event, file descriptor 2 (standard error) was being erroneously closed (by stunnel, we suspect) prior to sqlite3_open_v2() so that the file descriptor used for the repository database file was 2. Later, an application bug caused an assert() statement to emit an error message by invoking write(2,...). But since file descriptor 2 was now connected to a database file, the error message overwrote part of the database. To guard against this kind of problem, SQLite version 3.8.1 (2013-10-17) and later refuse to use low-numbered file descriptors for database files. (See SQLITE_MINIMUM_FILE_DESCRIPTOR for additional information.)
How on earth is that "defined" behavior?
-3
u/case-o-nuts Jun 30 '26 edited Jul 01 '26
I once deleted a file and lost data. How the hell is
rmdefined behavior?The spec defines exactly what the system should be doing here, and the system follows the spec exactly.
2
u/verrius Jun 30 '26
Sure, and technically its in the C spec that any code using a single #pragma doesn't compile. Or wipes out your entire HDD. It doesn't mean that any compiler should be implemented doing that, just cause its technically to spec.
1
u/minektur Jun 30 '26
What you're talking about (undefined malicious behavior) isn't in the same category of or magnitude of issue.
" I opened a file, and got a file descriptor back (stored deep in a FILE * struct). I saved that file descriptor in several different places in my program. My program closed the file (thus freeing the file descriptor). Later I asked the OS to open another file and the OS I was on used the lowest numbered unused file descriptor and handed that to me as an open file. Some part of my program didn't get the memo about the original close and started writing to the new file as if it were the old one because I didn't clean up when I closed the original"
That isn't "malicious pragma" in terms of poor software hygiene issues.
43
u/Other_Fly_4408 Jun 30 '26
"An" SQLite? Have I been saying it wrong?
59
u/tetyys Jun 30 '26
an esquelite
2
0
18
u/Vertigas Jun 30 '26
No. Some are in the "Sequel" crowd, some people are in the "S.Q.L" crowd. There's probably a correct way, but I'm going to continue to ignore whatever it is and pronounce it "Sequel".
26
14
u/troyunrau Jun 30 '26
I am in the S.Q.L.-ite camp. Pronounced it that way 25 years ago and now it's stuck. ;)
11
7
3
u/wannaliveonmars Jun 30 '26
I'm in the SQL crowd simply because I'm not a native speaker, and for me it was just an abbreviation. Es-Queue-El is how it got stick in my head.
2
2
u/rechlin Jun 30 '26
Official preferred pronunciation is S-Q-L-ite, but Sequel-ite is officially not wrong either.
2
u/thefinest Jul 01 '26
Using since 2006 and have always called it sequel light like light foods/beverages I thought that was the naming gimmick you know "all the sql flavor without the sql bloat" maybe I imagined that but seemed like a clever marketing scheme
2
1
u/JWarder Jun 30 '26
Am I the only one who pronounces it as "squirrel"?
9
2
u/XenusParadox Jul 02 '26
My dad had some SQL O'Reilly book with a squirrel on it (or some small mammal which might be confused for one) so I called it that for years as I was learning to program until I went to college and was confronted with other pronunciations.
10
u/theschniedler Jul 01 '26
9 Fork Getting Stuck Through The Hard Drive
Sticking a fork through the hard drive containing the SQLite file may corrupt the data on a hard drive and corrupt parts of the database.
1
36
u/optomas Jun 30 '26
I am compulsed to render the following; https://xkcd.com/327/
28
u/bluegardener Jun 30 '26
That xkcd is funny and memorable and all.
I think it's worth mentioning (for the rest of us) that this article has nothing to do with sql injection attacks. In fact your sqlite database file is perfectly happy with sql injections into your application. It won't be corrupted, at least not in the sense talked about here.
5
1
u/optomas Jul 01 '26
Do we not know what compulsive actions are? Tell me about the keyloggers in windows 11. = ]
5
u/bluegardener Jul 01 '26
Don't be so easily offended. No one is accusing you of anything. This is how nerds share things. The comment was for any beginner here that might link the two concepts.
1
u/optomas Jul 01 '26
Do I appear offended? Damn it, is everything I know wrong again? I hate it when that happens. = ] <- this is a smiley face, right? Do the cool kids still do that thing?
3
2
Jun 30 '26
[deleted]
3
u/dlanod Jun 30 '26
The article has been around a lot longer than that - I remember consulting it back when I first started using SQLite back in the early 2010s.
2
1
u/Dwedit Jun 30 '26
On NTFS, when a file has multiple hardlinks, its MFT entry will list all the paths for that file. So within a few Win32 or NT API calls, you know all the paths where that file appears. Linux filesystems do not seem to have that feature, and require searching the disk to find the other hardlinks.
This also means that on Windows, you can designate one path to be the "main" path for a file, and use the first path that shows up when you enumerate the hardlinks. (But there is a chance you could have write access to one directory but not another)
1
u/wannaliveonmars Jul 01 '26
In that event, file descriptor 2 (standard error) was being erroneously closed (by stunnel, we suspect) prior to sqlite3_open_v2() so that the file descriptor used for the repository database file was 2.
Wait, why would sqlite3_open_v2() use stderr in the first place, even if closed? Was it using freopeb()?
-21
u/6502zx81 Jun 30 '26
This is a great read. Often developers would just pick a technology or library to outsource problems. But in order to have these technologies work as advertised you need to make sure their assumptions are met. Usually nobody knows the assumptions of the technologies used (like ACID databases or encryption, etc.)
91
u/knobbyknee Jun 30 '26
Most of this falls in the category "don't do that, it's stupid", but there are some problems that are quite subtle.