How to Encrypt SQLite

2 posts, 0 answers
  1. Erdem
    Erdem avatar
    22 posts
    Member since:
    Sep 2013

    Posted 05 Jun 2015 Link to this post

     

    Hi,

     I'm using "schemahandler.createdatabase" and "createDDLScript" for creating and updating a database.

     I would like to password protect the database and I know it's possible with the SQLite driver that is available for .net

    Like so:

     

    //if the database has already password
    try{
                Conn = @"Data Source=database;Password=Mypass;";
                SQLiteConnection connection= new SQLiteConnection(Conn);
                connection.Open();
                //Some code
                connection.ChangePassword("Mypass");
                connection.Close();
        }
    //if it is the first time sets the password in the database
    catch
        {
                Conn = @"Data Source=database;";
                SQLiteConnection connection= new SQLiteConnection(Conn);
                connection.Open();
                //Some code
                connection.ChangePassword("Mypass");
                connection.Close();
                Conn = @"Data Source=database;Password=Mypass;";
        }

     ---

    What I have tried to do is the following:

    if db is not yet created => allow schemahandler to create it with regular connectionstring, then execute classic ado commands(as stated in above post) to add password to database and modify a static connectionstring (Conn) to a connectionstring with password.

    if db already exists => execute schemaupdates and then update password and connectionstring

    And Make sure that the Model of Data Access uses this Static connectionstring (Conn).

    Result:

    When the DB already exists => adding password seems to work, then I perform some queries with Data Access to get data and this works.

    When the DB does not exists => creating the database works, then modifying the db with new password seems like it works, but right after, I try to insert some data with Data Access but this fails => with the message that "the file is missing or encrypted" => I have checked if the connectionstring is modifed and it is, also a new DBContext instance is created.

    --------------------

    Is there no simpler way?

    I have seen this post:
    http://www.telerik.com/forums/fluent-model-and-database-encryption

    But SQLCipher seems to be 500 dollars now, I really don't want to cough this up just to encrypt my db, especially if the functionality is available in the sqlite library for .net itself.

     

    Thank you for your response and reading my post!

     

    ... And if it's currently not possible, this is a feature request, since nhibernate seems to be able to do this, see below:

     

    private ISessionFactory createSessionFactory()
    {
        return Fluently.Configure()
                .Database(SQLiteConfiguration.Standard.UsingFileWithPassword(filename, password))
                .Mappings(m => m.FluentMappings.AddFromAssemblyOf<DBManager>())
                .ExposeConfiguration(this.buildSchema)
                .BuildSessionFactory();   
    }
     
    private void buildSchema(Configuration config)
    {
            if (filename_not_exists == true)
            {
                new SchemaExport(config).Create(false, true);
            }
    }

     

  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 08 Jun 2015 Link to this post

    Hi Erdem,

    Telerik DataAccess can use password protected databases when the Password is specified in the connection string. The important thing is to create the password protected database upfront, before you use the schema handler; this way, no change in the connection string is required at this stage.

    Using the Telerik.DataAccess Fluent Sample NuGet, I was able to achieve the effect with:

    class Program
        {
            static void Main(string[] args)
            {
                var cs = EnsureEncrpytedDatabase("c:\\database.db3", "MyPass");
     
                EncryptedDatabaseContext.DbConnection = cs;
                using (var ctx = new EncryptedDatabaseContext())
                {
                    ctx.UpdateSchema();
     
                    Console.WriteLine("Count={0}", ctx.Products.Count());
                }
            }
     
            private static string EnsureEncrpytedDatabase(string db, string password)
            {
                var cs = string.Format(@"Data Source={0};Password={1};FailIfMissing=True", db, password);
                try
                {
                    using (var connection = new System.Data.SQLite.SQLiteConnection(cs))
                    {
                        connection.Open();
                        //Some code
                        if (connection.State != System.Data.ConnectionState.Open)
                            throw new System.Data.DataException();
                        connection.BeginTransaction().Commit();
                        connection.Close();
                    }
                }
                //if it is the first time sets the password in the database
                catch
                {
                    cs = string.Format(@"Data Source={0}", db);
                    using (var connection = new System.Data.SQLite.SQLiteConnection(cs))
                    {
                        connection.Open();
                        //Some code
                        connection.ChangePassword(password);
                        connection.Close();
                        cs = string.Format(@"Data Source={0};Password={1};", db, password);
                    }
                }
                return cs;
            }
        }

    In this EnsureEncryptedDatabase method, the connection string is changed depending on the state of the database. I just needed to make the private const DbConnection string member into an internal static string member for this to work.

    Regards,
    Thomas
    Telerik
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  3. DevCraft banner
Back to Top