Encryption and Password Protection in SQLite

1. What is Encryption
2. How to Set a Password on SQLite
3. How to Encrypt a Database on SQLite

When dealing with large databases with confidential information, it is pretty natural to want to hide it well. If it remains on one system, a password usually will suffice.

However, it is best to encrypt your database if it is sent over unsecured networks.

What is Encryption

Encryption, in a nutshell, is the process of converting your data into strings of nonreadable data that can’t be deciphered by simply reading it. It requires a key to encrypt and decrypt it.

Decryption turns unreadable data back into its original form, while encryption is the opposite, and the readable data is transformed into an unreadable form.

Data is encrypted based on an algorithm. Their complexity can vary, and some are more reliable than others.

Therefore, the type of encryption you use should be reliable enough to prevent decryption without a key.

How to Set a Password on SQLite

To protect your database in SQLite, use the following code before performing any other operations to set a password.

SQLiteConnection conn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
conn.SetPassword("any_password");
conn.open();

You can access your database (in SQLite version 3) next time using the following:

conn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;Password=any_password;");
conn.Open();

You can also reset your password with this:

conn.ChangePassword("new_password");

If you add the clause String.Empty in place of the password, you can remove the password.

How to Encrypt a Database on SQLite

Multiple methods can be adopted to encrypt your data. Almost all of them use some extension or software that is completely secure and can encrypt your data successfully.

Here are a few reliable options:

SQLite Encryption Extension (SEE)

This is a standard encryption service specifically for SQLite. It allows you to read from and write to both encrypted and unencrypted files in SQLite.

Moreover, you can even use the ATTACH command to work with two or more files simultaneously using SEE.

Encrypting the file is fairly simple in this extension. You may do so using the code below.

int sqlite3_key
(
   sqlite3 *db,
   const void *e_key,
   int key_size
);

Here, the first line represents the database used, the second refers to the key used for decryption, and the last row represents the number of bytes used to store the key.

You can also change the key of your database using the following code:

int sqlite3_rekey
(
   sqlite *db,
   const void *e_key, int key_size
);

Here, the first row represents the database, and the second includes the new key and the total size of the key.

You can also use sqlite3_rekey() to decrypt a previously encrypted database by specifying a NULL key.

This is how SEE encrypts the data and metadata of the entire database. However, it is essential to note that this is not currently available for free.

wxSQLite

This encryption service is open to all. It may be considered a C++ wrapper around the public domain SQLite database.

What’s different about this is that wxSQLite doesn’t attempt to hide the underlying database. Instead, it supports all features and encrypts it all individually in UTF-8 coding with automatic conversion between wxStrings and UTF-8 strings.

For other ANSI builds, the current locale conversion object (wxConvCurrent) is used for conversion. Still, it is important to note that issues might arise if external administration tools are used to modify the database.

This key-based encryption allows you to select your preferred cipher scheme at runtime.

SQLCipher

SQLCipher is an open-source extension for SQLite. It provides secure encryption services based on your requirements.

For example, they provide different packages such as “Community”, “Commercial”, and “Enterprise”. Each has been designed to provide features you will need without paying for any excess features.

SQLCipher has excellent performance and a small footprint, so it’s great for protecting embedded application databases and is well-suited for mobile development. Its features include the following:

1. There is only a 5-15% overhead encryption. This means that it provides speedy encryption.
2. The entire database is encrypted.
3. Security practices include CBC mode and key derivation.
4. It offers application-level cryptography.
5. The peer-reviewed OpenSSL crypto library provides the algorithms.
6. It supports multiple platforms.

SQLiteCrypt

This service uses AES 256 encryption support, which is the same AES as SQLite, without compromising too much speed. In fact, to the user, it will seem as fast as normal.

Here are some of the features it provides.

1. It is incredibly fast.
2. You only need to replace SQLite runtime and add 2 PRAGMA to use it.
3. It decrypts one block at a time, so hackers can’t get anything from the memory dump either.
4. Zero configuration allows encryption with a simple process.

The pragma statements it uses are mentioned below.

PRAGMA key = 'the passphrase'
PRAGMA lic = 'the license key'
PRAGMA rekey = 'new passphrase'

The first is to create an encrypted database, the second is to identify a legal copy of SQLiteCrypt software, and the last is to re-write the database with a new passphrase.

Sqleet

Sqleet is a transparent encryption service based on advanced algorithms for optimal security. It offers robust side-channel resistance and high software performance.

The compilation is simple due to the absence of dependencies to ease cross-compilation and cross-platform development.

You can compile an SQLite3 shell with sqleet encryption support as follows:

1. Unix - % gcc sqleet.c shell.c -o sqleet -lpthread -ldl
2. Windows - % gcc sqleet.c shell.c -o sqleet

System.Data.SQLite

This is a .NET library that offers encryption. After downloading the relevant package, extract SQLite.Interop.dll and rename it to sqlite3.dll.

This encrypts with plaintext passwords or encryption keys.

Function Creation Routines

You may also use SQLite’s function creation routines.

$db_obj->sqliteCreateFunction('Encrypt', 'function_name', 2);
$db_obj->sqliteCreateFunction('Decrypt', 'function_name', 2);

Now, you will be able to easily encrypt your databases and add passwords.