Add password to Sqlite file in Xamarin forms

Question

I have a Xamarin form application that creates a Sqlite database.

Microsoft.EntityFrameworkCore.Sqlite is used to create the database. I want to add a password to the file. I searched the internet, but unfortunately I can't find any obvious way. On StackOverflow, there are some questions that are similar to my question, however, the platform is not Xamarin Forms.

Here are the questions:

This is my code for creating the database:

public class DoctorDatabaseContext : DbContext
{
        private readonly string DatabasePath;

        public virtual DbSet<OperationsNames> OperationsNames { get; set; }
        public virtual DbSet<CommonChiefComplaint> CommonChiefComplaint { get; set; }
        public virtual DbSet<CommonDiagnosis> CommonDiagnosis { get; set; }
        public virtual DbSet<CommonLabTests> CommonLabTests { get; set; }

        public DoctorDatabaseContext(string DatabasePath)
        {
            FixedDatabasePath.Path = this.DatabasePath = DatabasePath;

            Database.EnsureCreated();    
        }    

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite($"Filename={DatabasePath}");    
        }
    }

bricelam · Accepted Answer · 2018-03-05T17:47:08.653

8

I wrote a post on Encryption in Microsoft.Data.Sqlite. You can leverage it in EF Core by passing in an open connection to UseSqlite.

Instead of Microsoft.EntityFrameworkCore.Sqlite, use these packages:

Microsoft.EntityFrameworkCore.Sqlite.Core
SQLitePCLRaw.bundle_sqlcipher

protected override void OnConfiguring(DbContextOptionsBuilder options)
{
    // TODO: Dispose with DbContext
    _connection = new SqliteConnection(_connectionString);
    _connection.Open();

    // TODO: Avoid SQL injection (see post)
    var command = _connection.CreateCommand();
    command.CommandText = "PRAGMA key = '" + _password + "'";
    command.ExecuteNonQuery();

    options.UseSqlite(_connection);
}

edited Mar 05 '18 at 17:47

answered Mar 02 '18 at 18:30

bricelam

28,825
9
92
117

Thanks for the answer, but there are two points that I don't understand. First, you are using variable `_connection` and in the next line you use `connection`, is that two different variables? The second point is that I can't see where the database file will saved. – Ahmed Shamel Mar 02 '18 at 19:31
2

@AhmedShamel For your first question i am pretty sure that its a spelling error. For the second question you can use the second constructor of [SqliteConnection](https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlite.sqliteconnection.-ctor?view=msdata-sqlite-2.0.0#Microsoft_Data_Sqlite_SqliteConnection__ctor_System_String_) – Nick Polyderopoulos Mar 04 '18 at 23:54
2

Sorry, this certainly wasn't a complete code listing, just something to get you started. Variables that start with `_` above should be fields on the class. – bricelam Mar 05 '18 at 17:48
@bricelam Again, thanks for the answer. It solve my problem. But I don't know how the package `SQLitePCLRaw.bundle_sqlcipher` works? I just install it and don't use it in my code. – Ahmed Shamel Mar 05 '18 at 22:58
2

By default (`Microsoft.EntityFrameworkCore.Sqlite`) bundle_green (a vanilla SQLite build) is used. `Microsoft..Sqlite.Core` lets you bring your own bundle. bundle_sqlcipher is a build of SQLite called SQLCipher which supports encryption. It all works by [pure, battery-powered magic](https://github.com/aspnet/Microsoft.Data.Sqlite/blob/2.0.0/src/Microsoft.Data.Sqlite.Core/Utilities/BundleInitializer.cs). – bricelam Mar 05 '18 at 23:23
@bricelam Thanks again. I now have one problem. After applying your solution, the database type become `SQLCipher ` and I need the database type to be `System.Data.SQLite` because I have a Windows application that produce `System.Data.SQLite` database and I want the two applications to produce the same database. is that possible? – Ahmed Shamel Mar 07 '18 at 16:32
1

I don't think so. System.Data.SQLite encryption is a very different implementation than SQLCipher. The algorithms used by System.Data.SQLite are Windows-only (and unsupported). – bricelam Mar 07 '18 at 21:29
@bricelam is this still valid for EF? I have tried with above but my ExecuteNonQuery returns -1 and I am not seeing any difference – Arslan Pervaiz Oct 10 '22 at 19:51
It should still work. We also added the [`Password`](https://learn.microsoft.com/dotnet/standard/data/sqlite/connection-strings#password) connection string keyword that you can use instead. – bricelam Oct 12 '22 at 17:28

score 0 · Answer 2 · answered Feb 28 '18 at 06:18

There is no way to add password protection for sqlite db out of the box, there is a whole explanation here regarding the reason in more details.

however, there is a nice solution shown on this thread using SQL Chiper to encrypt you DB per page, if you are willing to invest the money.

If you want to encrypt and decrypt your entire db file on demand use PCLCrypto.

But as you noticed, the only way for you to achieve this kind of protection is via encryption of your db file, you won't be able to set password.

score 0 · Answer 3 · answered Mar 06 '18 at 01:17

Not exactly an answer to your question but a suggestion. You can use a library called Akavache to store data.

There are four storage locations: - BlobCache.LocalMachine - Device storage - BlobCache.UserAccount - User settings. Some systems backup this data to the cloud. - BlobCache.Secure - For saving sensitive data - like credentials.(encrypted) - BlobCache.InMemory - A database, kept in memory. The data is stored for the lifetime of the app.

Hope that helps

Add password to Sqlite file in Xamarin forms

3 Answers3