0

I've just learned how to use SQLCipher here and I want to be able to save it to an SQLite file and I stumbled upon this approach where the file is saved via FileOutputStream. However, the FileOutputStream approach only worked when the database file isn't encrypted. Is there a workaround that you can recommend?

If not, my ultimate objective is actually the following:

  1. Create an encrypted database using Room
  2. Create an encrypted backup for this database
  3. Give the user the ability send this file either via Google Drive, email, or some other means so that I can also view the database's contents if they need me to look over it
  4. Make this process as seamless and as invisible, but also as elegant as possible

Thanks a lot!

Here's what I tried to do and it is pretty much a copy-paste of those two links I referred above:

For the database:

public abstract class MyDatabase extends RoomDatabase {
    public static MyDatabase myDatabase;
    
    // based on https://github.com/sqlcipher/android-database-sqlcipher
    public static synchronized MyDatabase getInstance(Context context) {
        if (MyDatabase == null) {
            final byte[] passphrase = SQLiteDatabase.getBytes("userEnteredPassphrase".toCharArray());
            final SupportFactory factory = new SupportFactory(passphrase);
            MyDatabase = Room.databaseBuilder(context.getApplicationContext(),
                    MyDatabase.class,
                    "my_database")
                    .addCallback(roomCallback)
                    .openHelperFactory(factory) // WHEN I REMOVE THIS LINE, THE ENTIRE DATABASE GETS SAVED AS A FILE INCLUDING THE TABLES, BUT WHEN THIS LINE IS ACTIVE, THE FILE IS REDUCED TO ABOUT 4kb WITH NOTHING IN IT
                    .build();
        }
        return MyDatabase;
    }
}

How I tried to save the database using FileOutputStream:

public void saveDatabaseToFileOutputStream() {
    File dbfile = this.getDatabasePath("my_database");
    File sdir = new File(Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS),"DBsaves");
    String sfpath = sdir.getPath() + File.separator + "DBsave" + String.valueOf(System.currentTimeMillis());
    
    if (!sdir.exists()) {
        sdir.mkdirs();
    }
    
    File savefile = new File(sfpath);
    
    try {
        savefile.createNewFile();
        
        int buffersize = 8 * 1024;
        byte[] buffer = new byte[buffersize];
        int bytes_read = buffersize;
        
        OutputStream savedb = new FileOutputStream(sfpath);
        InputStream indb = new FileInputStream(dbfile);
        
        while ((bytes_read = indb.read(buffer,0,buffersize)) > 0) {
            savedb.write(buffer,0,bytes_read);
        }

        savedb.flush();
        indb.close();
        savedb.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

The difference in saved file when the database is ciphered and not ciphered

dg713
  • 3
  • 4
  • "However, the FileOutputStream approach only worked when the database file isn't encrypted" -- you might want to provide a [mcve] showing what you tried and explaining in detail what problems you encountered. – CommonsWare Feb 05 '21 at 22:08
  • @CommonsWare hi, I added an example of pretty much what I did – dg713 Feb 06 '21 at 00:02
  • That is not a particularly good file-copy algorithm, and newer versions of Android will make it difficult for you to write to that location. You also need to make sure that your database is closed at the point of making the copy. But, there is nothing here that will cause specific problems for SQLCipher for Android -- any problems that you have I would expect to see also with ordinary SQLite databases. – CommonsWare Feb 06 '21 at 00:12
  • Do you have recommendations or references I could study where I can learn to do the following? (1.) Create an encrypted database using Room / SQLCipher (2.) Create an encrypted backup for this database (3.) Give the user the ability send this file either via Google Drive, email, or some other means so that I can also view the database's contents if they need me to look over it (4.) Make this process as seamless and as invisible, but also as elegant as possible – dg713 Feb 06 '21 at 00:14
  • "so that I can also view the database's contents if they need me to look over it" -- if you want that, skip the encryption. – CommonsWare Feb 06 '21 at 00:18
  • @CommonsWare, Ok, assuming I skip the encryption, how can I make the user send the database to me without them having it as a visible file? Otherwise, if I choose to keep the encryption, how can I make a backup of the database inside the user's phone so that they can recover data in case the database gets wiped for some reason? – dg713 Feb 06 '21 at 00:20
  • "how can I make the user send the database to me without them having it as a visible file?" -- with the database closed, you can use `FileProvider` to serve the file, giving you a `Uri` that you can use with `ACTION_SEND`. "how can I make a backup of the database inside the user's phone so that they can recover data in case the database gets wiped for some reason?" -- your code pretty much does that, though you should consider backing it up to some other location (e.g., `getExternalFilesDir()`, `ACTION_CREATE_DOCUMENT`). – CommonsWare Feb 06 '21 at 00:33
  • Thanks! I'll be trying this out once I get some sleep, I've already lost quite a few hours trying to figure out and researching different approaches! – dg713 Feb 06 '21 at 00:40
  • @CommonsWare hey sorry if I get back too you soon. Are you saying that by simply closing the database, I will be able to create ciphered backup of the database using the algorithm I just wrote? – dg713 Feb 06 '21 at 00:53
  • You need to close the database regardless of whether it is encrypted or not. Do not have an open connection to the database while also trying to copy the database file. Bear in mind that SQLite (and SQLCipher for Android) offer write-ahead logging (WAL), where content is not written to the main database file unless that database is closed (or you execute `CHECKPOINT` statements, IIRC). Room, in particular, turns on WAL IIRC. – CommonsWare Feb 06 '21 at 12:05
  • @CommonsWare, I'm about to try this and now I just wondered, if I already have a database that was created, and on a latest version, I will just start implementing encryption on the device, how will that affect the database or the data that is already stored? Also, how do I make sure that the database is re-opened once I am finished with creating the backup database file? – dg713 Feb 07 '21 at 14:34
  • "how will that affect the database or the data that is already stored?" -- you would need to arrange to encrypt the existing database. "how do I make sure that the database is re-opened once I am finished with creating the backup database file?" -- the details of that will depend a lot on how you are using the database, where the passphrase is coming from, and so on. – CommonsWare Feb 07 '21 at 14:41
  • @CommonsWare Thanks a lot! You've been really helpful! – dg713 Feb 11 '21 at 15:13

0 Answers0