sqliteentity-framework-coresqlcipher

SQLCipher .NET with Entity Core - set or remove password


I'm using SQLCipher (community edition) running with .NET and Entity Core. Everything works perfectly including changing a password on existing database.

For example, if DB is protected with password '1' it is no issue to change (rekey) it to '2' or anything else. However, there is an issue when I want to protect unprotected database or remove protection from a database that has password.

At the moment the code that changes password for a DB looks like:

public bool Rekey(string oldKey, string newKey)
    {
        SqliteConnection conn = GetConnection(oldKey);

        try
        {
            conn.Open();
            using (var command = conn.CreateCommand())
            {
                command.CommandText = $"PRAGMA rekey = '{newKey}';";
                int rowsChanged = command.ExecuteNonQuery();
            }
            conn.Close();
        }
        catch
        {
            return false;
        }
        return true;
    }

private SqliteConnection GetConnection(string key)
    {
        SqliteConnection conn;
        if (_dbaccessBytes == null)
        {
            conn = new SqliteConnection
            {
                ConnectionString = new SqliteConnectionStringBuilder()
                {
                    DataSource = _databasePath,
                    Mode = SqliteOpenMode.ReadWriteCreate
                }.ToString()
            };
        }
        else
        {
            conn = new SqliteConnection
            {
                ConnectionString = new SqliteConnectionStringBuilder()
                {
                    DataSource = _databasePath,
                    Mode = SqliteOpenMode.ReadWriteCreate,
                    Password = key
                }.ToString()
            };
        }

        return conn;
    }

So when we don't have password protection and send oldKey as NULL and newKey as some value - it would just run, but will not set password. No errors. I tried to look up solution, but no luck yet. May be I'm missing some understanding and setting and removing key should be done not like 'PRAGMA rekey', but in some other method?

Thanks in advance.


Solution

  • 'PRAGMA rekey' is used to change existing password for the database. To set password for unencrypted database or remove it from a password protected one should use sqlcipher_export() function. This is described in SQLCipher documentation here.

    And maybe someone would find code sample useful. For decrypting db you should just establish connection like:

    SqliteConnection conn = GetConnection();
    conn.Open();
    using var command = conn.CreateCommand();
    command.CommandText = $"ATTACH DATABASE '{newDBPath}' AS plaintext KEY '';";
    command.ExecuteNonQuery();
    command.CommandText = @"SELECT sqlcipher_export('plaintext');";
    command.ExecuteNonQuery();
    command.CommandText = @"DETACH DATABASE plaintext;";
    command.ExecuteNonQuery();
    

    Consequently, for encrypting you should do the similarly:

    SqliteConnection conn = GetConnection();
    conn.Open();
    using var command = conn.CreateCommand();
    command.CommandText = $"ATTACH DATABASE '{newDBPath}' AS encrypted KEY '{newKey}';";
    command.ExecuteNonQuery();
    command.CommandText = @"SELECT sqlcipher_export('encrypted');";
    command.ExecuteNonQuery();
    command.CommandText = @"DETACH DATABASE encrypted;";
    command.ExecuteNonQuery();