SQLite database encryption defects and Improvements

Sqlite is a very small cross-platform embedded database, which does not provide encryption functions. However, the designer has obviously considered the encryption solution. We can find two reserved encryption ports in the source code: sqlite3_key and sqlite3_rekey can be encrypted through these two interfaces.

There are many articles describing how to encrypt data. For details, refer :《SQLite
A solution for database encryption"

Here I want to talk about the defects and improvement methods of this method (test sqlite v3.5.6 );

Brief description of encryption functions:

Sqlite3_key has three input parameters: one is the sqlite3 pointer, and the other is the password pointer and Data Length;

From the first parameter, we can see that you must first obtain a sqlite3 pointer using sqlite3_open to call sqlite3_key to set the key;

Problem description:

Sqlite3_open aims to open the database. During the openDatabase process, the header information of the database file will be read and parsed;

(When we use a binary viewing tool (such as UltraEdit) to open a database file, we will find "SQLite format 3... "Some readable information, the first 100 bytes are the sqlite data file header information .)

One of the main functions of openDatase reads the header information, including the page size of the important database. This parameter is expressed in 16th and 17 bytes. Without encryption, we observe that this value is generally 0400, indicating that the page size of the database is 1024;

The problem arises: we already know that the encryption setting is after openDatase. If the data has been encrypted, opendatase will inevitably fail to get the correct page size and other information, why is there no error in using the encryption method?

In this case, 16th and 17 bytes of the database are garbled. When pagesize is not an integer multiple of 512, or is greater than a value, opendatase treats page size as 1024 by default, this practice is generally not a problem (the premise is that the pagesize of many databases is indeed 1024); however, this practice is not secure and there are two risks:

Risk 1: If the pagesize of the database is not 1024, but 2048, can the database be opened after encryption?

Risk 2: If the 16 and 17 bytes after database encryption are exactly an integer multiple of 512, it will be mistaken for pagesize, and the database cannot be opened.(This data has been tested and proved to be wrong );

Solution:

This problem is actually because the sqlite Code does not fully consider the issue of pagesize reading after encryption. There are two solutions:

Solution 1: (complete solution) modify the sqlite source code so that the pagesize read by opendatase is invalid. After the database key is set, the pagesize is recalculated when the data is read for the first time;

Solution 2: Modify the sqlite3_key encryption implementation. When setting the key, decrypt and read the database header information, and read the decrypted pagesize, set the correct pagesize back;

After comparative analysis, I chose the second solution. If the source code of sqlite is modified, we will be faced with the maintenance of sqlite upgrade. The problem can be solved only by modifying the encryption implementation, this does not affect the implementation of the original sqlite, but this issue should be raised to the sqlite Development Team;

Other problems:

Why does pagesize fail to read the database?

Pagesize determines how many bytes of data will be read from the file for page parsing. the first page is especially important. It stores information about various tables in the database. If pagesize is incorrect, sqlite won't be able to parse the tables that actually exist, which directly leads to data opening failure. Even if you are lucky enough to open the tables, data retrieval may fail in the future;

Does the latest sqlite version solve this problem?

Currently, sqlite update frequency is very high. The latest version is 3.6.11. It has not been verified whether it has been solved. However, some new pagesize statements are found in the lockBtree code, however, after resetting, the page is not re-read, so the roles of these statements are not clear yet.