Simple SQlite DB Encryption

It so happened that I really like to use SQLite DBMS.

When programming in assembler, I sometimes need a complete DBMS. My programs rarely exceed a few hundred kilobytes. It is clear that using a DBMS of several hundred megabytes with it is at least ridiculous, but ultimately very inconvenient - the hardware requirements and the complexity of installation and configuration immediately increase, and as a result, the reliability of the entire system decreases.

SQLite is a completely different matter. Firstly, it is small - only a few hundred kilobytes, a great addition to compact assembler programs. Secondly, it is an ultra-reliable data storage system. She does not need any special settings and settings. Well, as for performance - not the last.

For example, I used SQLite in my AsmBB forum engine about which I already wrote on Habré. (By the way, after that he did not fall ).

Since that time, the project has been slowly but surely developing. New features have appeared, increased security and performance.

And then one day I thought about how to increase the already good security of the project. And immediately I thought that it would be nice to do the encryption of the forum database. Indeed, even if the database is leaked, no one will get access to the personal data of users.

A quick search on the Internet showed that there are several SQLite extensions for database encryption. Unfortunately, the official SEE extension is not free and generally sold for money.

But, of course, a holy place is never empty and I immediately stumbled upon the SQLeet extension. And in it I liked literally everything.

SQLeet uses the ChaCha20 algorithm to encrypt the database. The encryption key is computed through PBKDF2-HMAC-SHA256 using a 16-byte salt and 12345 hash iterations. For authentication, Poly1305 is used.

Both SQLeet and SQLite are distributed under the public domain. This is convenient because it does not increase licensing chaos in the project.

Still SQLeet is very compact. All the code only takes about one and a half thousand lines in C and has no external dependencies.

The project is actively supported and the author promptly answers questions and fixes bugs, if any.

SQLeet is distributed in the same way as SQLite - in the form of a single C source file that can be simply compiled in the same way that SQLite is compiled.

In addition, since the extension does not change the SQLite code in any way, updating the main DBMS can be done very simply - by replacing the sqlite3.c



file and re-creating the combined source.

Since I use not quite standard compilation in AsmBB (SQLite in AsmBB is compiled via MUSL libc ), and I am not a C programmer, for me simplicity of compilation is very important.

For example, here’s the bash code that I use to download the latest version of SQLeet and create and build the source:

 wget -q -O - https://github.com/resilar/sqleet/archive/master.tar.gz | tar -xz cd ./sqleet-master script/amalgamate.sh < ./sqleet.c > ../sqlite3.c cd .. rm -rf ./sqleet-master/
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

The result is a sqlite3.c



file that can be inserted where the original SQLite file was inserted before and used in the same way.

Using the extension is no different than using SQLite. The only difference is that if the database is encrypted, then immediately after opening it is necessary to call the sqlite3_key () function, in which to specify the encryption password. Well, or even better, just execute SQL pragma key='%%'



. (This is better because the SQLite API does not change and

you can always replace SQLeet with SQLite and vice versa).

Initial database encryption, as well as password replacement, occurs through the sqlite3_rekey()



function or pragma with the pragma rekey='%NEW_PASSWORD%'



.

And here I had such a question. Where does the password come from? After all, if the password is stored on the server in some file, then the potential hacker will be able to read it.

So I decided to do it differently. The fact is that AsmBB is a long-lived FastCGI application. Once launched on a server, it runs for months and even years without the need for a reboot.

And if so, then the administrator can simply enter the password through the web interface immediately after starting AsmBB. Thus, the password exists only in RAM and only during the execution of the POST request during application launch. (Of course, one should not forget to zero out all the memory in which the password existed during the execution of the POST request.)

From the set password, SQLeet generates an encryption key via PBKDF2-HMAC-SHA256, and this key is also stored only in RAM.

Of course, such a decision is imperfect. The encryption key can probably be found in RAM memory, during the execution of AsmBB, if the attacker has administrator rights.

But even so, the system is still much safer than without encryption. For example, now database backups can be stored everywhere and sent over open channels without fear of data leakage.

By the way, there is a rake that you can step on using SQLeet (or other SQLite cryptographic extensions). And I, of course, stepped on them.

The problem is the size of the database page. In versions of SQLite earlier than 3.12.0 (March 2016), the default page size was 1024 bytes. In v3.12.0, 4096 bytes made it. This size, the database user can change for performance reasons, and the page size is written in the database itself.

But if the database is encrypted, then the page size cannot be read, and for decryption this size is needed, because each block is encrypted separately.

Therefore, if you encrypt a database with a non-standard page size (for SQLeet, the standard is 4096 bytes), then you will not be able to decrypt it, even if you set the correct password.

This is fixed simply - before setting the password via sqlite3_key()



or pragma key='%%'



, you need to set the correct page size via pragma page_size=%%



.

Another possible problem is that after encryption, the OS will no longer be able to recognize that the file is an SQLite database. This (as far as I read) sometimes leads to some problems, in particular in iOS. There is a solution to this problem, just do not encrypt the first 32 bytes of the file, but I did not go into details.

And finally, about performance. SQLeet is very fast. After encryption, I did not notice any slowdown in the system against the background of normal fluctuations in VPS performance. Precision measurements may show some kind of slowdown, but it will probably be within less than 10% of the speed of an unencrypted database.

Of course, there are other free SQLite extensions for encryption. For example, SQLcipher . It did not suit me because it has a different distribution license (BSD), the code is much larger and there are external dependencies.

But, on the other hand, SQLcipher is much older and therefore (possibly) more stable. Someone may come in handy.