SQLite

About the product

SQLite is a lightweight embedded relational database. The source code of the library has been transferred to the public domain. In 2005, the project won the Google-O'Reilly Open Source Awards.

SQLite does not use the client-server paradigm, that is, the SQLite engine is not a separately working process with which the program interacts, but provides a library with which the program is built and the engine becomes an integral part of the program. Thus, SQLite library function calls (APIs) are used as the exchange protocol. This approach reduces overhead, response time, and simplifies the program. SQLite stores the entire database (including definitions, tables, indexes, and data) in a single standard file on the computer running the program. Ease of implementation is achieved due to the fact that before starting a write transaction, the entire file storing the database is blocked; ACID functions are achieved, among other things, by creating a log file.

Multiple processes or threads can read data from the same database simultaneously without any problems. You can write to the database only if no other requests are currently being serviced; otherwise, the write attempt fails and an error code is returned to the program. Another scenario is the automatic repetition of write attempts within a given time interval.

The package also includes a functional client part in the form of the sqlite3 executable file, which demonstrates the implementation of the functions of the main library. The client part works from the command line, allows you to access the database file based on typical OS functions.

Thanks to the engine architecture, it is possible to use SQLite both on embedded systems and on dedicated machines with gigabyte data arrays.

2022: SQLite 3.40

On November 21, 2022, it became known that the release of SQLite 3.40, a lightweight DBMS designed as a plug-in library, was published. SQLite code is distributed as public domain, that is, it can be used without restrictions and free of charge for any purpose. Financial support for SQLite developers is provided by a specially created consortium, which includes companies such as Adobe, Oracle, Mozilla, Bentley and Bloomberg.

Illustration: slide-share.ru

Major changes:

• An experimental ability to compile SQLite into intermediate WebAssembly code has been implemented, capable of launching into web browser and suitable for organizing work DB with web applications in the language. JavaScript Web developers are provided with a high-level object-oriented interface for working data with sql.js or Node.js, binding over a low-level C API and API based on the Web Worker mechanism, which allows you to create asynchronous handlers executed in separate threads. Data that web applications store in the WASM version of SQLite can be stored on the client side using OPFS (Origin-Private FileSystem) or the window.localStore API.
• Added recovery extension to recover data from corrupted database files. The command line interface uses the.recover command to restore.
• Query scheduler performance has been improved. Removed restrictions when using indexes with tables with more than 63 columns (previously, indexing was not used for operations with columns whose sequence number exceeds 63). Improved indexing of values used in expressions. Failed to load large rows and blobs from disk when processing NOT NULL and IS NULL statements. Views for which a full scan runs only once are not materialized.
• In the codebase, instead of the type "char *," a separate type of sqlite3_filename is used to represent file names.
• Added internal sqlite3_value_encoding function ().
• Added SQLITE_DBCONFIG_DEFENSIVE mode to prevent schema version change. data storage
• Additional checks have been added to the PRAGMA integrity_check implementation. For example, tables without a STRICT characteristic should not contain numeric values in columns of type TEXT and string values in columns of type NUMERIC. A check of the correct order of the rows in the tables with the characteristic "WITHOUT ROWID" has also been added.
• The "VACUUM INTO" expression takes into account the "PRAGMA synchronous" settings.
• An assembly SQLITE_MAX_ALLOCATION_SIZE option has been added that allows you to limit the size of blocks when allocating memory.
• The pseudo-random number generation algorithm built into SQLite is translated from using the RC4 stream cipher to Chacha20.
• Indexes with the same name are allowed in different data schemas.
• Performance optimizations have been made that have reduced the CPU load by about 1% with a typical SQLite^[1].

2019: Ability to hack an iPhone through vulnerabilities in SQLite

On August 16, 2019, it became known that Check Point specialists demonstrated how you can hack an iPhone through the database kernel that uses iOS - SQLite. In this case, hackers will be able to obtain administrator rights over the device.

SQLite is a common database. data They are available in any, operating system personal computer and on. mobile phone SQLite users -,, Windows 10 iOS, MacOS,,, and. Chrome Safari Firefox Contacts Android on your iPhone, some saved on yours - passwords all this laptop information is very likely it is stored in the SQLite database.

Check Point specialists found several vulnerabilities and invented a way to exploit them. Simply put, it has now become possible to gain control over everything that accesses SQLite databases.

Since SQLite is one of the most widespread components, software such vulnerabilities can be used infinitely many times.

Check Point researchers demonstrated these vulnerabilities in two ways. In the first case, engineers intercepted an attacker who infected a test device with a popular one known harmful ON as a "password thief." When a malicious program takes a stored password from an infected computer and sends it to its operator, we gain control over the operator himself.

The second demonstration was on the iPhone, on the iOS operating system. Specialists managed to bypass Apple's trusted secure boot mechanism and gain administrator rights on the latest iPhone.

SQLite is almost built into almost any platform, so we can say that experts have barely scratched the tip of the iceberg, if we talk about the potential for exploiting vulnerabilities.

Check Point hopes this study will push the global cybersecurity community to work further on these vulnerabilities.

2016: SQLite 3.14.0

On August 9, 2016, SQLite 3.14.0 became available.

Version Logo, (2016)

Added features and functions^[2]

• Ability to create virtual tables without row identifiers (WITHOUT ROWID mode);
• The query scheduler has made changes that allow you to use optimizations of the "OR" expression in virtual tables if there are several LIKE, GLOB, REGEXP and MATCH statements;
• Added virtual CSV table to facilitate import of CSV files;
• Added carray () function;
• Added downloadable extension vfsstat.c with VFS layer to collect statistics on I/O parameters;
• The algorithm for executing queries containing "ORDER BY" and "LIMIT" expressions has been improved;
• Added VFS "win32-none," which is similar to "win32" except that it does not use a file lock;
• A new dbhash command line utility has been added to calculate the SHA1 hash for the data schema and database content;
• Added SQL function json_quote ().

2015

SQLite 3.10.0

On January 6, 2015, SQLite 3.10.0 was released, designed as a plug-in library^[3].

Major changes

• It is possible to use LIKE, GLOB and REGEXP operators with virtual tables;
• The option "--transaction" has been added to the sqldiff utility;
• New interfaces sqlite3_db_cacheflush () and sqlite3_strlike () are implemented;
• When opening a symbolic link to databases that provide logging, files are now created in reference to the real file name, and not the name of the symbolic link;
• When using I/O using memory-mapped I/O, the display is now in read-only mode, which does not make it possible to accidentally change the database in the event of a buffer overflow in the application or problems with pointers;
• New SQL functions json_group_array () and json_group_object () have been added to the JSON format extension;
• Added assembly option SQLITE_LIKE_DOESNT_MATCH_BLOBS;
• Performance optimization was introduced, which accelerated work with the database by 2-3%;
• New commands "changes ON 'OFF" and "vfsinfo" have been added to the command line interface.

SQLite 3.8.11

July 30, 2015 it became known about the publication of the release of SQLite 3.8.11^[4].

Following it, a corrective release 3.8.11.1 was released for a lightweight database management system designed as a plug-in library.

SQLite CPU Load Test, 2015

In the new release:

• An experimental RBU (Resumable Bulk Update) extension has been added, designed to organize quick incremental updates of large data sets;
• An experimental extension of the FTS5 has been added with a new implementation of the full-text search system;
• Support for the ON CONFLICT expression has been added to the spellfix1 extension;
• The IS statement implements the ability to use indexes;
• Improved query scheduler performance in terms of automatic indexing of subqueries specified in the FROM block;
• Added the command "PRAGMA cell_size_check" to detect corruption of the database file at an early stage;
• In the full-text FTS3 engine, the matchinfo () function has a new mapping flag "b";
• Added fuzzcheck program for high-quality fuzz-testing of the database. The program is automatically called when the "make test" is executed;
• The efficiency of the page cache is increased and the pre-allocation of memory for the cache is implemented. In tests, the change allowed to increase performance by 5% with typical applications. DBMS Various microoptimizations were introduced, which allowed to perform 22.3% more work within the same CPU cycles. In total, in terms of performance, release 3.8.11 is twice as fast as 3.8.0 and three times as fast as 3.3.9 (when testing cachegrind and speedtest1.c in Ubuntu 14.04 x64 when building in gcc 4.8.2 with the -Os flag).

2012

SQLite 3.7.0

Changes:

• For correct operation on systems without ftruncate (), the database size is stored in the header.
• The GROUP BY and ORDER BY derektivs will not be noop when pointing to each other.
• sqlite3_db_status function (), return code SQLITE_DBSTATUS_CACHE_USED
• Faster processing of database requests, enabled logging support.
• Significantly reduced time for processing a request to build indexes in the database due to the use of background construction.

SQLite 3.7.10

We switched to using the default 4 versions of the data schema format, which also implies the inclusion by default of a newer format of decreasing indexes and a violation of compatibility with the SQLite database version below 3.3.0 (released before 2006).

Some other changes:

• The sqlite3_pcache_methods structure is declared obsolete, as well as the SQLITE_CONFIG_PCACHE and SQLITE_CONFIG_GETPCACHE configuration options, instead of which you should use sqlite3_pcache_methods2, SQLITE_CONFIG_PCACHE2 and SQLITE_CONFIG_GETPCACHE2;

• The VFS interface has added support for data loss protection in the event of an unexpected power outage, which is enabled through the option at the SQLITE_POWERSAFE_OVERWRITE build stage and through the "psow = BOOLEAN" parameter in the URI. Additionally, the flaw was corrected, which could theoretically lead to damage to the base during an emergency power outage;

• New interfaces have been added: sqlite3_db_release_memory (), sqlite3_db_filename (), sqlite3_stmt_busy (), sqlite3_uri_boolean () and sqlite3_uri_int64 ();

• The ability to indicate negative values ​ ​ in PRAGMA cache_size, which will indicate the need to reduce the cache size by -1024 * N bytes relative to the memory page size;

• The default memory allocation system has been improved;

• The query scheduler is improved in the direction of involving indexes when requesting ranges of values ​ ​ for rowid, processing "UNION ALL" and in some other situations;

• Improved implementation of locks for "unix-dotfile" VFS (mkdir ()/rmdir () is now used instead of open ()/unlink ());

• Various improvements in unix and windows VFS implementation.

Programming languages

• C, C++, Java, C#, VB.NET, Python, Perl, PHP, Tcl (Tcl tools are included with SQLite), Ruby, Haskell, Scheme, Smalltalk, Lua and Parser.

Notes