【转】SQLite加密设计的缺陷

这几周工作都围绕SQLite数据库，主要是数据库升级工作。在升级数据库中遇到了很多坑，我觉得还是把数据导出来（导出为.sql文件）再导入新的数据库引擎可以干净利落解决问题。如果研究十年前和十年后的数据库有啥区别，特别是加密的SQLite数据库升级有啥问题可真不容易，网上资料非常稀少，这篇是很重要的一篇。这篇讲到了数据库的页大小问题，加密数据库可能无法被正确识别，虽然这是几年前的文章，依旧适用。

原文转自 databasesql.info

SQLite is a very small cross-platform embedded database, which itself does not provide encryption, but the designers obviously also consider the encryption scheme in the source code, we can find two set aside the encrypted interface: sqlite3_key, and sqlite3_rekey by achieve these two interfaces to achieve the purpose of encryption.

How to encrypt Much has been written description, you can refer to: ”

I have to say is a drawback of this method and the improved method (test sqlite version v3.5.6);

A brief description of the encryption function:

the sqlite3_key input parameters, there are three: one is sqlite3 pointer, di, tri, respectively, the pointer and the data length of the password;

From the first argument, we can see, must be obtained sqlite3_open a sqlite3 pointers, in order to set the key tone sqlite3_key;

Problem Description:

The purpose of sqlite3_open open the Database, the openDatabase process to read and parse the db file header information;

(Binary view tool (such as UltraEdit) to open the database file, at the top found “SQLite format 3 …” readable information in front of 100 bytes is the SQLite data file header information.)

a major function of openDatase read header information, wherein there are more important database page size, this parameter is represented with a byte 16,17; unencrypted case, we observed that this value is usually 0400, which means that the database The page size is 1024;

The question arises: We already know that the encryption settings after openDatase, if the data is encrypted, opendatase must not get the correct page size and other information, then why encryption method used without error?

Because the database 16, 17 bytes generally is garbled, the default page size when the 1024 deal with when pagesize read out is not 512 an integer multiple of, or greater than a certain value, opendatase, and this approach is generally not (provided that the database pagesize is indeed 1024); However, this approach is not secure, there are two risks:

Risk: If the database pagesize is not 1024, but 2048, encrypted database can open it?

The risk: If the database is encrypted 16,17 just an integer multiple of 512 bytes, it will be mistaken for is pageSize, the database can not be opened (this data has been test and prove bound to be wrong);

Solution:

This problem, in fact, SQLite code does not take full account of this pagesize read encrypted, there are two workarounds:

Option One: (complete solution) to modify the sqlite source, invalid pagesize the opendatase read, set up a database key, the first read data to re-calculate the pagesize;

Option Two: (for encryption repair program) modify the sqlite3_key encryption implementation, set the key, decryption read database header information, read the PageSize decrypted, then correct pagesize back;

After a comparative analysis, I chose the second solution sqlite sqlite source modify faced SQLite upgrade maintenance, but only modify the encryption part to solve the problem, which does not affect the realization of the original sqlite, but this problem is to development team to mention;

Other problems:

Why pagesize read wrong, the database will lead to fail to open?

The pagesize decide how many bytes the data read from the file page parsing, the first page is particularly important, store it in a database inside a variety of table information, if the pagesize wrong, sqlite can not be resolved in the end those tables, a direct result failed to open; even lucky enough to open the future to take the data will go wrong;

Latest sqlite version to solve this problem?

Sqlite update frequency is high, the latest 3.6.11 not verify has been resolved, but found the lockBtree code found some new re-set pagesize statement, but re-set did not re-read the page, so it the role of these statements is not clear.