Background

As part of making a C# project Linux compatible I had to change System.Data.SQLite dependency to Microsoft.Data.Sqlite to support encrypted databases in Linux. Since SQLite doesn't support encrypting database files by default Microsoft.Data.Sqlite is also not supporting it, instead it supports using modified versions of SQLite like SQLCipher, SEE or SQLiteCrypt to support encryption.(For more information read here.)

After migrating the project to Microsoft.Data.Sqlite there was one thing left to do, converting System.Data.SQLite encrypted databases in the project to SQLCipher encrypted databases. For this, I made an open-source console application linked here that will get the path to the database and its password and convert it to an SQLCipher encrypted database.

How it works

The console application consists of two main parts, first decrypting the System.Data.SQLite encrypted database, second encrypting it with the SQLCipher encryption. The best way to decrypt the System.Data.SQLite database is to use the rekey pragma as it is compatible with the latest version of System.Data.SQLite. For doing so you need to connect to the database and PRAGMA rekey the database with empty string as the password, like below.

private static void DecryptSystem(SQLiteConnectionStringBuilder connectionStringBuilder, string password)
{
    var connection = new SQLiteConnection(connectionStringBuilder.ConnectionString);
    connection.Open();

    using var command = connection.CreateCommand();
    command.CommandText = $"PRAGMA key = {password};";
    command.ExecuteNonQuery();

    using var command2 = connection.CreateCommand();
    command2.CommandText = "PRAGMA rekey = '';";
    command2.ExecuteNonQuery();
    connection.Close();
}

Encrypting the database to SQLCipher is a little bit more complicated as it is not built into Microsoft.Data.Sqlite. What it means is that you need to create a new SQLCipher encrypted database file and move the data into it. Luckily, most of the work can be done with a query that has three sections.

  • First section attaches an empty SQLCipher encrypted database to the connection you have to your decrypted database with the alias of encrypted.
ATTACH DATABASE '{databaseTemp}' AS encrypted KEY '{password}';
  • Then sqlcipher_export is being used to move and encrypt the data into the empty database.
SELECT sqlcipher_export('encrypted');
  • And at the end the database with the encrypted data will be detached from the connection.
DETACH DATABASE encrypted;

Now that the encrypted database is created, we will replace the old, decrypted database with the new SQLCipher encrypted database. Here is the full code for encrypting the database.

private static void EncryptToMicrosoft(SqliteConnectionStringBuilder connectionStringBuilder, string password)
{
    var databasePath = connectionStringBuilder["Data Source"]?.ToString() ?? "";
    var databaseName = Path.GetFileNameWithoutExtension(databasePath);
    var connection = new SqliteConnection(connectionStringBuilder.ConnectionString);
    var databaseTemp = $"{databaseName}_Temp.db";
    File.Delete(databaseTemp);
    var query =
        @$"ATTACH DATABASE '{databaseTemp}' AS encrypted KEY '{password}'; SELECT sqlcipher_export('encrypted'); DETACH DATABASE encrypted;";

    connection.Open();
    using var cmd = new SqliteCommand(query, connection);
    cmd.ExecuteNonQuery();
    connection.Close();

    if (File.Exists(databasePath))
    {
        File.Delete(databasePath);
    }

    File.Move(databaseTemp, databasePath);
}

The repository also supports providing a new password for the SQLCipher encrypted database if you do not wish to have the same password on the new database. Try out the latest release of the tool and let me know in the comments if it was useful. Have in mind that the tool is built for Windows, as the System.Data.SQLite encryption does not work on Linux and was the whole reason for migrating to Microsoft.Data.Sqlite.