Distributed computing researcher, Murat Demirbas, has written a blog post about what he sees as the past, present, and future of SQLite.
This paper, which appeared in VLDB'22 a couple weeks ago, delves into analytical data processing on SQLite, identifying key bottlenecks and implementing suitable solutions. As a result of the optimizations implemented, SQLite is now up to 4.2X faster on the Star Schema Benchmark (SSB). This is a sweet little paper (befitting SQLite's fame). It is technically easy to read yet very fulfilling.
The paper also has an important theme. Throughout the paper, we see time and again how SQLite benefits from its informative profiling utilities and aggressive testing to identify and implement optimizations quickly. Performance and correctness monitoring is a prime factor in development velocity. The ease of profiling SQLite's execution engine enabled the team to pinpoint which virtual instructions were responsible for the bottlenecks, and also to watchout for performance regression issues. Their extensive test suite (consisting of fuzz, boundary value, regression, I/O, out-of mem testing) allowed them to quickly integrate the optimizations into a release build without worrying of breaking other components of the library.
SQLite is a widely used, single-node, online transaction processing (OLTP) database useful in many situations where SQL is relevant yet Postgresql or MariaDB are too heavy.
Previously:
(2019) SELECT Code_execution FROM * USING SQLite: Eggheads Lift the Lid on DB Security Hi-jinks
(2019) Remote Code Execution Vulnerability Impacts SQLite
Related Stories
https://www.securityweek.com/remote-code-execution-vulnerability-impacts-sqlite
A use-after-free vulnerability in SQLite could be exploited by an attacker to remotely execute code on a vulnerable machine, Cisco Talos security researchers have discovered.
Tracked as CVE-2019-5018 and featuring a CVSS score of 8.1, the vulnerability resides in the window function functionality of Sqlite3 3.26.0 and 3.27.0.
To trigger the flaw, an attacker would need to send a specially crafted SQL command to the victim, which could allow them to execute code remotely.
The popular SQLite library, a client-side database management system, is widely used in mobile devices, browsers, hardware devices, and user applications, Talos notes.
SQLite implements the Window Functions feature of SQL, allowing queries over a subset, or "window," of rows, and the newly revealed vulnerability was found in the "window" function.
The security researchers discovered that, after the parsing of a SELECT statement that contains a window function, in certain conditions, the expression-list held by the SELECT object is rewritten and the master window object is used during the process.
Submitted via IRC for AnonymousCoward
SELECT code_execution FROM * USING SQLite: Eggheads lift the lid on DB security hi-jinks
At the DEF CON hacking conference in Las Vegas on Saturday, infosec gurus from Check Point are scheduled to describe a technique for exploiting SQLite, a database used in applications across every major desktop and mobile operating system, to gain arbitrary code execution.
In a technical summary provided to The Register ahead of their presentation, Check Point's Omer Gull sets out how he and his colleague Omri Herscovici developed techniques referred to as Query Hijacking and Query Oriented Programming, in order to execute malicious code on a system. Query Oriented Programming is similar in a way to return oriented programming in that it relies on assembling malicious code from blocks of CPU instructions in a program's RAM. The difference is that QOP is done with SQL queries.
[...] It must be stressed, though, that to pull off Check Point's techniques to hack a given application via SQLite, you need file-system access permissions to alter that app's SQLite database file, and that isn't always possible. If you can change a program's database file, you can probably get, or already have achieved, code execution on the system by some other means anyway.
Nonetheless, it's a fascinating look into modern methods of code exploitation, and a neat set of discoveries.
(Score: 5, Interesting) by krishnoid on Sunday November 13 2022, @04:47PM (1 child)
Per the link, they test the code to within a living semicolon of its life. They have a proprietary fuzzer and what they call an aviation-grade [sqlite.org] test suite. It's also why they don't use an object-oriented language [sqlite.org], but they're considering Rust. And I appreciate the title of their security [sqlite.org] documentation page.
(Score: 3, Interesting) by darkfeline on Monday November 14 2022, @04:19AM
The devs are also deeply religious, and I don't think these two facts are unrelated.
Join the SDF Public Access UNIX System today!
(Score: 2) by Opportunist on Sunday November 13 2022, @10:02PM (3 children)
You need a database for something that doesn't require a lot of speed, a lot of throughput, maybe only a user or two but ease of use and no hassle? Yes, you found your tool.
I can't even count how many of my projects, many of which in the area of IoT, made use of SQLite. Fast and easy to use (if you're an old SQL guy like me), VERY lightweight (seriously, the lib has a few kb, and you can strip it down to whatever you need easily), VERY lean when it comes to mem hunger and even with a CPU clocked in the Megahertz range it's fast enough for pretty much anything you could want.
I certainly wouldn't want to host a project for a couple thousand accesses per second on it, but for small stuff it's a godsend.
(Score: 2) by JoeMerchant on Sunday November 13 2022, @11:07PM
Ditto. I generally avoid databases anyway, but sometimes the application just screams for one. When it does (in my line of work) it's almost always screaming for SQLite. If it ever needs to "get heavier" in the future, the migration from SQLite into something more multi-user heavy transaction volume friendly like Postgres, Maria, etc. is really easy, particularly if you've put in a layer like: https://doc.qt.io/qt-6/database.html [doc.qt.io]
Україна досі не є частиною Росії Слава Україні🌻 https://news.stanford.edu/2023/02/17/will-russia-ukraine-war-end
(Score: 2) by looorg on Monday November 14 2022, @01:50PM (1 child)
In that regard shouldn't you ask why you should have SQLite instead of say just using a text/csv file? Isn't the only benefit to using the SQLite then that you might want to one day scale up to a real database and that might be easier if you have already moved or have a query function in SQL instead of manipulating a text file in some other fashion. But if you don't plan to ever scale, don't need to search or use SQL functions and command then just using a normal file might make sense? Or perhaps when does one make sense over the other? After all both in that regard are single/few users and not a lot of I/O or editing of the file where the data is stored anyhow? I can't imagine, or am unsure of if, SQLite has better I/O speed then just writing to a normal file. Perhaps there is some ease-of-use point or aspect to SQLite over it but beyond that I'm not really sure why you would want to pick one over the other unless you are as a matter of fact building a one user database with actual database functions.
(Score: 2) by hendrikboom on Monday November 14 2022, @10:34PM
In the long run, there is software that scales, and software that gets discarded or rewritten.