Encryption in Microsoft.Data.Sqlite

One of the frequently asked questions about Microsoft.Data.Sqlite is: How do I encrypt a database? I think that one of the main reasons for this is because System.Data.SQLite comes with an unsupported, Windows-only encryption codec that can be used by specifying Password (or HexPassword) in the connection string. The official releases of SQLite, however, don’t come with encryption.

SEE, SQLCipher, SQLiteCrypt & wxSQLite3 are just some of the solutions I’ve found that can encrypt SQLite database files. They all seem to follow the same general pattern, so this post applies to all of them.

Bring Your Own Library

The first step is to add a version of the native sqlite3 library to you application that has encryption. Microsoft.Data.Sqlite depends on SQLitePCL.raw which makes it very easy to use SQLCipher.

Install-Package Microsoft.Data.Sqlite.Core
Install-Package SQLitePCLRaw.bundle_sqlcipher

SQLitePCL.raw also enables you to bring your own build of SQLite, but we won’t cover that in this post.

Specify the Key

To enable encryption, Specify the key immediately after opening the connection. Do this by issuing a PRAGMA key statement. It may be specified as either a string or BLOB literal. SQLite, unfortunately, doesn’t support parameters in PRAGMA statements. Use the quote() function instead to prevent SQL injection.

connection.Open();

var command = connection.CreateCommand();
command.CommandText = "SELECT quote($password);";
command.Parameters.AddWithValue("$password", password);
var quotedPassword = (string)command.ExecuteScalar();

command.CommandText = "PRAGMA key = " + quotedPassword;
command.Parameters.Clear();
command.ExecuteNonQuery();

// Interact with the database here

Updated in 3.0

Note, if you’re using Microsoft.Data.Sqlite version 3.0 or newer, the above commands are unnessary. The Password connection string keyword can be used instead.

// Add or update the Password connection string keyword
connection.ConnectionString =
    new SqliteConnectionStringBuilder(connection.ConnectionString)
        { Password = password }
        .ToString();

connection.Open();

Rekey the Database

If you want to change the encryption key of a database, issue a PRAGMA rekey statement. To decrypt the database, specify NULL.

var command = connection.CreateCommand();
command.CommandText = "PRAGMA rekey = " + newPassword;
command.ExecuteNonQuery();