SQLite Encryption

Hi,

I'm using encryption (as implemented by Robert Simpson in System.Data.SQLite) in a database which I need to access with Lightspeed. However, to access the database, I need to use the SQLiteConnection.SetPassword function, because the password is an array of bytes. Setting the password in plain-text on the connection string is also easier to hack.

Is there anyway to access this database with Lightspeed, without using the connection string approach?

Thanks.

paulo

We don't provide direct access to the connection object, so there isn't a direct way to do this.

However, you could work around it by using a custom connection strategy.  Basically the idea would be to implement the default connection strategy, but with an additional SetPassword call.  Your custom strategy would look something like this:

  public class EncryptedConnectionStrategy : ConnectionStrategy
  {
    private IDbConnection _connection;
    private readonly byte[] _password;

    public EncryptedConnectionStrategy(LightSpeedContext context, byte[] password)
      : base(context)
    {
      _password = password;
    }

    protected override IDbConnection Connection
    {
      get
      {
        if (_connection == null)
        {
          _connection = Context.DataProviderObjectFactory.CreateConnection();
          _connection.ConnectionString = Context.ConnectionString;

          SQLiteConnection sqliteConnection = _connection as SQLiteConnection;
          if (sqliteConnection != null)
            sqliteConnection.SetPassword(_password);

          _connection.Open();
        }

        return _connection;
      }
    }

    protected override void Dispose(bool disposing)
    {
      if (disposing)
      {
        if (_connection != null)
        {
          _connection.Dispose();
          _connection = null;
        }
      }

      base.Dispose(disposing);
    }
  }

You would then set this as your connection strategy on each unit of work you created:

using (MyUnitOfWork uow = context.CreateUnitOfWork()) {
  uow.ConnectionStrategy = new EncryptedConnectionStrategy(context, password);
  // ...
}

To encapsulate this, see the UnitOfWorkFactory and UnitOfWorkFactory<T> classes and the LightSpeedContext.UnitOfWorkFactory property.  We can provide advice on this if required.

Thanks for the tip Ivan!

I'll let you know if I have any problem.

paulo