Defects and improvement of SQLite encryption Design

 

SQLite is
A very small cross-platform embedded database does not provide encryption functions, but the designer has obviously considered the encryption solution. We can find two reserved encryption connections in the source code.
Port: sqlite3_key and sqlite3_rekey. You can implement these two interfaces to achieve encryption.

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 password.
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..." contains 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 important databases,
This parameter is expressed in 16th and 17 bytes. If encryption is not performed, 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 cannot obtain the correct page.
Size and other information, so why is there no error in using the encryption method?

Because at this time, the database 16th and 17 bytes are generally garbled. When the pagesize reading is not an integer multiple of 512 or greater than a certain value, opendatase will
By default, the page size is treated as 1024, and this method is generally normal (the premise is that many databases
Pagesize is indeed 1024
); But this approach is not safe, 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,
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 encryption Implementation of sqlite3_key to decrypt and read the database when setting the key.
To read the decrypted pagesize, and then set the correct pagesize;

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, which does not affect the original
SQLite implementation, 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 various table
Information. If the pagesize is incorrect, SQLite will not be able to parse the tables, which directly leads to data opening failure. Even if it is lucky to open successfully, data retrieval will also fail;

Does the latest SQLite version solve this problem?

Currently, SQLite update frequency is very high. The latest version is 3.6.11. It hasn't been verified whether it has been solved yet. However, some new settings are found in the lockbtree code.
Pagesize statement, but the page is not re-read after the reset, so the role of these statements is not clear.