11 ms·
Select Code_execution from * Using SQLite; (2019)
- jiofih 6y agoThis is surprising. SQLite is known for having 100% code coverage, fuzzy testing, and a 644:1 tests to code ratio. If even that cannot stop this kind of attack then we really need to rethink computers from the ground up... https://sqlite.org/testing.html https://sqlite.org/testing.html
- jrockway 6y agoPeople have rethought computers from the ground up. The C++ version was slightly faster in a benchmark, though, so their thought was ignored.
- otabdeveloper4 6y agoYeah, I'm sure switching to a more fashionable programming language will automatically fix all bugs and security issues. (sarcasm)
- mrmonkeyman 6y agoMemory leaks are not bugs, they are a fundamental design error.
- pdimitar 6y agoEvery little bit helps IMO. Rust seems like it eliminates class of bugs (and so do Haskell and OCaml), the JVM looks like it could prevent another subset of them, and I am sure there are other examples. Not going to bash anyone for using C -- sqlite's team has their priorities and values clearly stated (performance and discipline) -- but it's probably time for all of us to admit that not all recent tech innovation is about fads. Some of it is actually genuinely useful and helps with age-old problems (though sadly, none of it addresses all of them).
- anyfoo 6y agoAll of them? No. But will a modern language, where many more properties are mathematically proven through the type system, massively improve the situation? Yes. If you don’t believe me, do the converse and write your next big project completely in assembly (which is effectively untyped), and see what happens.
- saagarjha 6y agoRemoved undefined behavior from your code with this one weird trick!
- dathinab 6y agoThrough only if has high usability. You currently can bend C++ into ways to have such a type system. The usability this introduced through additional complexity is just very very bad. So no one does it because bad usability means bad productivity and getting a project done with bugs is often better then getting it not done at all (sure there are exceptions!).
- anyfoo 6y agoThat's a good point, and I think you've hit one of the main issues why we're still stuck with old, unsafe languages. There is the saying that once you got a Haskell program to compile, it usually just works, and I personally found it to be true a lot. But getting it through the compiler can indeed be much more of a journey than in, say, C. And I realize that the work you have to put in to just even being able to run something can be discouraging, maybe going against a natural (or maybe just common) affinity towards tight, iterative "change-compile-try" approaches. Still, in cases where it really matters, i.e. system software or common, but often mission-critical software like sqlite, maybe it's worth taking the jump?
- pdimitar 6y agoI would jump with joy if sqlite3 was rewritten in Haskell, OCaml or Rust. They can bring so much to the table in terms of safety and less development surprises.
- jrockway 6y agoI am not really talking about the present day, though I agree with the other comments that a lot of modern features help. Think back to the era of Lisp machines. Memory safety, macros, OO... all in the 80s! We ignored the lessons and paid the price. People, to this day, still look at you weird when you say "I think you could write a game in a garbage collected language" and don't bat an eye when you say "C is dangerous, but I was very careful." I think what killed us is that computers are expensive (and performance directly translates to the number of computers you need to buy) and security holes are free. Of course you're going to optimize for speed; optimizing for safety can save you at most zero dollars. That's just engineering.
- dathinab 6y agoOften times it's more about compatibility with existing libraries or programmers experience then about speed. I often feel that many very experienced C++ devs have fallen into one of tree fallacies: The sunken (learning) cost fallacy (I include sentimental attachment here). The speed fallacy. And the being totally hypnotised/fascinated by C++-complexity fallacy (I include the you can turn C++ into whatever you like fallacy in this one).
- GlitchMr 6y ago"Program testing can be used to show the presence of bugs, but never to show their absence!" - Edsger W. Dijkstra
- jonnypotty 6y agoSomeone talking sense here
- umvi 6y agoI'm not convinced. If lockpicking and other ancient hobbies are anything to go on, it doesn't matter how many redesigns you go through; people will just create new specialized/innovative tools to break/attack the most popular ones.
- Quekid5 6y agoLocks are physical devices and so are susceptible to all manner of wonderful and surprising physical attacks. Programming languages can artificially limit the attack surface. (Up to hardware exploits which can subvert the processor itself.)
- c22 6y agoI don't understand this reasoning at all. Locks attempt to artificially limit the (physical) attack surface as well. This could, in fact, be a reasonable definition of a lock. So people come up with wonderful and surprising configurations of matter that cause these artificial limitations to fail providing direct access to the lock's root mechanism (via unanticipated route). This is analogous to virtual attacks on information systems which are hardened against familiar routes of ingress and vulnerable to those which are wonderful and surprising.
- anyfoo 6y agoYou can apply formal methods to code that you cannot in physical reality. If I show you the equation “x == y”, you won’t be able to pick 7 and 8 to make the equation true. If I give you a mechanical device that works with beads to answer the same question, you might make it work. Of course computers are eventually physical reality, too, and things like rowhammer show that you don’t even need traditional “bugs” to escape your mathematical utopia. But all the bugs talked about here are entirely the fault of the software.
- vntok 6y agoWell one could try to change the meaning of "==" to make the equation true. Think about comparator overloading in supported languages where the x and/or y variables are actually objects, for example.
- txutxu 6y agoIndeed. But my first thought is: how many programs am I using right now on my laptop, that don't have such test coverage? and... how much (probably) undiscovered holes will they have? That without count, that we're living in the times of the hardware related bugs.
- Quekid5 6y ago100% code coverage explores an absolutely miniscule portion of the state space of any given program. This is why formal methods are a thing.
- dathinab 6y agoYes especially for something fairly complex like an sql engine with views and full text search. Through then it also seems that many of the newer bugs sl seem to be around more advanced features which sometimes can be disabled on compiler time. (newer=after they integrated fuzzing into their tested) I wonder if it makes sense for Oses to ship two versions of sqlite one for the small local "trusted" database case and one for the sqlite as file format case which had many features disabled, only allowing the core subset of sqlite. Through if you provide some "evergreen" software you can anyway just include your own version of sqlite. Potentially statically linked in.
- juskrey 6y agoFormal methods have formal assumptions.
- Quekid5 6y agoYes? A huge portion of what a database does is amenable to formal methods. You can even come up with a reasonable rudimentary formal model of a physical machine/CPU and check against that. Sure, the model of the machine may be wrong in subtle ways, but when you discover that, you fix it and recheck your model of a database against the updated model of a physical machine. That said... don't use a language with trivially allows for out-of-bounds indexing, etc. (SQLite was started when there were really few other good options, so that's understandable.)
- cztomsik 6y agocode coverage only says what's been called not if it behaves correctly (I always thought it's obvious)
- oh_sigh 6y agoBut Postgres is more complicated than sqlite, and only 10% of the issues were discovered there.
- SQLite 6y agoMany 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 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?"
- anarazel 6y ago> (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. Yea, that made it harder in the past for other test tooling too. IIRC Greg Stark fuzzed our regex library and had to fight against the more complicated interaction due to client / server to make that work. Ended up finding quite a few things... > (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. FWIW, we (pg devs) are pretty hesitant to break backward compat. Sure, each release has a few things, but it's usually pretty corner case-y stuff. Check e.g. the list for the upcoming v13: https://www.postgresql.org/docs/13/release-13.html https://www.postgresql.org/docs/13/release-13.html There's a lot of significant design errors we're continuing to support just because it'd be too painful to break compat.
- centimeter 6y agoEveryone who has been evangelizing strongly typed safe-by-construction languages (or other lightweight formal methods) has been saying this for like 20 years. If you have a memory corruption bug in your application in 2020 it is 110% your fault for making that possible.
- rurban 6y agoIt's not surprising. A hack is still a hack, even you apply industry best practices in testing. There are two major security entry points, and none of them were closed since. This is the paper describing both. https://research.checkpoint.com/2019/select-code_execution-from-using-sqlite/ https://research.checkpoint.com/2019/select-code_execution-f...
- badrabbit 6y agoMaybe having a way to architect complexity in a way that preserves the author's intent for input data restriction is one way? Can code be built such that a sql query input could be declared such that it can only be processed in a control flow that ends up interacting with data on disk and return output? I don't mean things like control flow guard that precompute control flow, but an input oriented control flow whitelist enforced by the processor. Programming languages would declare input and explicitly declare what functions can execute as a result of or against that input. So, ideally, each input declaration would have a precomputed control flow graph, the processor will need to register memory as "input" (like NX/DEP) and the program will need to load the control flow graph with an interrupt that causes the processor to track operation resulting from control flow that interacted with the input in memory that is not accessible to the OS. The technology to do this and make it practical exists today but it did't at the advent of modern computing.
- deleted 6y ago[deleted]
- ysleepy 6y agoCombined with the correctness fuzzing results by SQLancer [1] where SQLite had 179 issues revealed (vs. 11 in PostgreSQL), I think the 100% coverage is a lot less meaningful than I had thought. Robustness is not necessarily correctness and if the unit tests are just mirror images of the code, their results may help against regressions but little for correctness. [1]: https://www.manuelrigger.at/dbms-bugs/ https://www.manuelrigger.at/dbms-bugs/
- simlevesque 6y agoThey don't just have full coverage. They have 3 completely different test suites.
- jbverschoor 6y agoCoverage doesn't mean correct behavior. It's a vanity metric, which will prevent programming errors, such as not checking for null, division by zero. I've seen so many instances of useless tests which don't actually test properly, but simply walk the lines. Of course, for sqlite I assume the tests are very decent.
- dathinab 6y agoMany code coverage tools tell you just if a line/"statement" or similar had been executed during any test. But that bit might have many possible variants in which you can execute it. E.g. if you have two if/else blocks in a row there are potentially 4 path of execution (if,if; if,else;else,if;else,else). Only two of them need to be hit to get what corresponds to 100% test coverage in most tools. If you consider that you likely call functions in that if and else statements which can have further branching and similar you might understand why firstly 100% coverage doesn't say to much and secondly why a "real" 100% Coverage if all possible execution paths is normally infeasible. (Through by using a more powerful type system combined with proofs around the type system and your usage of it you likely can massively prune the number of paths which need testing reachable, but at this point you probably should go full in with the proofs instead of using tests ;=)
- 6y ago
- yodon 6y agoI feel like I just watched the original first presentation of SQL injection - this is that big a deal. (I can't find the link, but the guy who gave that presentation thought it bombed because people started getting up and walking out five minutes into his talk - it turned out they were trying to get to the pay phones first so they could call back to their offices and alert them that the world had just been lit on fire).
- dleslie 6y agoIIRC it was Jeff Forristal who gave that talk, but I too am unable to find the video.
- hanche 6y agoIf I understand this correctly, you need a malicious sqlite database file, which I don’t think you could create with sqlite itself. Which means, in order to exploit this, you first have to get this malicious database onto their computer. So while this seems serious enough, it may not be time to panic quite yet.
- progval 6y agoI'm guessing this submission is a reaction to another post on the frontpage: "SQLite as an Application File Format" https://news.ycombinator.com/item?id=23508923 https://news.ycombinator.com/item?id=23508923 In this case it's relevant, as untrusted SQLite files would be downloaded and loaded in this context.
- hanche 6y agoI see. I had thought of that more as a format for applications to store their persistent data than for data interchange. When used for the latter, the danger seems clear enough. (I also just noticed that the original post is half a year old.)
- recursivecaveat 6y agoIf you're using it for persistent storage, its probably inevitable that a user sends in their copy as part of a bug report and a developer cracks it open.
- hanche 6y agoGood point.
- dathinab 6y agoTrue but even as a interchange file format sqlite is interesting. Through you probably want some hardened against this types of attacks form of sqlite. Like one which had just a subset of features and sqlsupport.
- 6y ago
- bob1029 6y agoI don't see the practical attack vector. For every use case I have for SQLite, none of them involve allowing someone to execute arbitrary SQL against one of my databases. We also don't do any loading of databases from untrusted parties. Seems like a hypothetical scenario was invented here that could just as well be applied to any database engine with similar outcomes.
- zorked 6y agoThere are a few applications that use SQL databases as their save file format.
- ysleepy 6y agoThe exploit works with any query performed on the sqlite db file. The expected tables are replaced by views that execute the arbitrary sql.
- SQLite 6y agoThe 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 https://sqlite.org/forum/forumpost/8beceed68e
- Drip33 6y agoWas the write a shell php file portion of the video/exploit patched? Sites like https://sqliteonline.com/ https://sqliteonline.com/ and https://inloop.github.io/sqlite-viewer/ https://inloop.github.io/sqlite-viewer/ could be perpetually vulnerable if not?
- dsabanin 6y ago
- saagarjha 6y agoNot sure why there are comments on this about SQLite's "correctness" or "testing": these are "works as intended" oversights where you run a query and SQLite does as you asked, except if you give an attacker too much control over the query it gives an unpredicted amount of control over what it lets you do. It's similar to command injection: bash could be 100% fuzzed and covered, but if you pass a string into it unescaped it's still going to let an attacker run code…
- devwastaken 6y agoYeah this appears to apply more to things like websql. But not in situations where sqlite is used behind a web service that uses prepared statements and best practices and such. If I'm wrong I'd like to know though.
- schoen 6y ago(2019)
- ufo 6y agoIn the context of the recent discussion[1] about using an SQLite database as an application file format, is there a way to mitigate these problems? For example, a way to verify that the SQLite database has the exact schema that you expect and doesn't have weird views configured to hijack your normal queries? [1] https://news.ycombinator.com/item?id=23508923 https://news.ycombinator.com/item?id=23508923
- anskp 6y agoIs this the supporting submission for the two daily Rust indoctrination threads?
- ngneer 6y agoA lot of comments on this thread ponder the difference between logical and physical, between software and hardware bugs. There is absolutely no difference. As a kind reminder, computation is a physical process, period. Any proofs of correctness apply only within the confines of the model in which they are proven. To the folks fond of formal verification, methinks Kripke agrees. To the folks presenting hardware bugs as a counterexample, it is not. If you include hardware bugs in your security analysis, and you should, you are merely taking a wider view of the system.
- petters 6y agoSeems like the fts3_tokenizer was disabled in a 2016 release: https://www.sqlite.org/releaselog/3_11_0.html https://www.sqlite.org/releaselog/3_11_0.html (due to security concerns) Did PHP7 still use an older version at the time of this talk (late 2019)? I agree with the speaker that the interface of fts3_tokenizer is mind blowing.
- dilandau 6y ago>have to install a pre-corrupted database. So basically a nothingburger. These guys seem like they're just trying to get attention and make a name. I'd say if someone has access to install a pre-corrupted file, code execution is already a given.
- ratorx 6y agoSQLite as an Application File Format (2014) https://news.ycombinator.com/item?id=23508923 https://news.ycombinator.com/item?id=23508923
- thunderbong 6y ago>> We created a rogue SQLite database that exploits the software used to open it. So, here, they are replacing the whole database and then stealing the passwords? I would guess that if a malicious actor has that kind of access to the system, all is lost.
- XMPPwocky 6y agoSQLite notably is often used as a file format. For example, for browser cookie storage. As you can see in the next sentence after your quote, > Exploring only a few of the possibilities this presents we’ll pwn password stealer backends while they parse credentials files and achieve iOS persistency by replacing its Contacts database… (The major browsers store passwords in SQLite databases, so password stealers upload those to a backend. iOS makes it hard to run code persistently (if you have a shell once, can you keep it after a reboot), so the Contacts database is a target for persistence.)