title: Android從非加密數(shù)據(jù)庫遷移到加密數(shù)據(jù)庫
date: 2019-12-26 13:26:53
categories:
- Android
tags:
- SQLITE

如果你之前使用的是非加密數(shù)據(jù)庫总放，想遷移到加密數(shù)據(jù)庫并保留原來的數(shù)據(jù)滤港，你需要使用 SQL 函數(shù) sqlcipher_export() 進(jìn)行遷移。

方案一：WCDB遷移

WCDB 對 sqlcipher_export() 函數(shù)做了擴(kuò)展褪猛，原本只接受一個(gè)參數(shù)為導(dǎo)出到哪個(gè) ATTACHED DB， 現(xiàn)在可以接受第二個(gè)參數(shù)指定從哪個(gè) DB 導(dǎo)出吃环。因此可以反過來實(shí)現(xiàn)導(dǎo)入：

ATTACH 'old_database' AS old;
SELECT sqlcipher_export('main', 'old');   -- 從 'old' 導(dǎo)入到 'main'
DETACH old;

詳情請見 sample-encryptdb[https://github.com/Tencent/wcdb/tree/master/android/samples/sample-encryptdb] 示例镶柱，它示范了如何使用 SQLiteOpenHelper 實(shí)現(xiàn)數(shù)據(jù)從非加密往加密遷移和 Schema 升級。

import android.content.Context;
import android.util.Log;

import com.tencent.wcdb.DatabaseUtils;
import com.tencent.wcdb.database.SQLiteChangeListener;
import com.tencent.wcdb.database.SQLiteDatabase;
import com.tencent.wcdb.database.SQLiteOpenHelper;
import com.tencent.wcdb.repair.RepairKit;

import java.io.File;


public class EncryptedDBHelper extends SQLiteOpenHelper {

    private static final String TAG = "EncryptedDBHelper";

    private static final String DATABASE_NAME = "encrypted.db";
    private static final String OLD_DATABASE_NAME = "plain-text.db";
    private static final int DATABASE_VERSION = 2;

    private Context mContext;
    private String mPassphrase;

    // The test database is taken from SQLCipher test-suit.
    //
    // To be compatible with databases created by the official SQLCipher
    // library, a SQLiteCipherSpec must be specified with page size of
    // 1024 bytes.
    static final SQLiteCipherSpec CIPHER_SPEC = new SQLiteCipherSpec()
            .setPageSize(1024);
    public EncryptedDBHelper(Context context, String passphrase) {

        // Call "encrypted" version of the superclass constructor.
        super(context, DATABASE_NAME, passphrase.getBytes(), CIPHER_SPEC , null, DATABASE_VERSION,
                null);

        // Save context object for later use.
        mContext = context;
        mPassphrase = passphrase;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // Check whether old plain-text database exists, if so, export it
        // to the new, encrypted one.
        File oldDbFile = mContext.getDatabasePath(OLD_DATABASE_NAME);
        if (oldDbFile.exists()) {

            Log.i(TAG, "Migrating plain-text database to encrypted one.");

            // SQLiteOpenHelper begins a transaction before calling onCreate().
            // We have to end the transaction before we can attach a new database.
            db.endTransaction();

            // Attach old database to the newly created, encrypted database.
            String sql = String.format("ATTACH DATABASE %s AS old KEY '';",
                    DatabaseUtils.sqlEscapeString(oldDbFile.getPath()));
            db.execSQL(sql);

            // Export old database.
            db.beginTransaction();
            DatabaseUtils.stringForQuery(db, "SELECT sqlcipher_export('main', 'old');", null);
            db.setTransactionSuccessful();
            db.endTransaction();

            // Get old database version for later upgrading.
            int oldVersion = (int) DatabaseUtils.longForQuery(db, "PRAGMA old.user_version;", null);

            // Detach old database and enter a new transaction.
            db.execSQL("DETACH DATABASE old;");

            // Old database can be deleted now.
            oldDbFile.delete();

            // Before further actions, restore the transaction.
            db.beginTransaction();

            // Check if we need to upgrade the schema.
            if (oldVersion > DATABASE_VERSION) {
                onDowngrade(db, oldVersion, DATABASE_VERSION);
            } else if (oldVersion < DATABASE_VERSION) {
                onUpgrade(db, oldVersion, DATABASE_VERSION);
            }
        } else {
            Log.i(TAG, "Creating new encrypted database.");

            // Do the real initialization if the old database is absent.
            db.execSQL("CREATE TABLE message (content TEXT, "
                    + "sender TEXT);");
        }

        // OPTIONAL: backup master info for corruption recovery.
        RepairKit.MasterInfo.save(db, db.getPath() + "-mbak", mPassphrase.getBytes());
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        Log.i(TAG, String.format("Upgrading database from version %d to version %d.",
                oldVersion, newVersion));

        // Add new column to message table on database upgrade.
        db.execSQL("ALTER TABLE message ADD COLUMN sender TEXT;");

        // OPTIONAL: backup master info for corruption recovery.
        RepairKit.MasterInfo.save(db, db.getPath() + "-mbak", mPassphrase.getBytes());
    }

    @Override
    public void onConfigure(SQLiteDatabase db) {
        db.setAsyncCheckpointEnabled(true);
        db.setChangeListener(new SQLiteChangeListener() {

            private StringBuilder mSB = new StringBuilder();
            private void printIds(String prefix, long[] ids) {
                mSB.append(prefix).append(": ");
                for (long id : ids) {
                    mSB.append(id).append(", ");
                }
                Log.i(TAG, mSB.toString());
                mSB.setLength(0);
            }

            @Override
            public void onChange(SQLiteDatabase db, String dbName, String table,
                    long[] insertIds, long[] updateIds, long[] deleteIds) {
                Log.i(TAG, "onChange called: dbName = " + dbName + ", table = " + table);
                printIds("INSERT", insertIds);
                printIds("UPDATE", updateIds);
                printIds("DELETE", deleteIds);
            }
        }, true);
    }
}

方案二：SQLCipher 遷移

從 SQLCipher Android 遷移
gradle加入

implementation "net.zetetic:android-database-sqlcipher:3.5.9@aar" //加密必要

關(guān)鍵代碼如下：

private static void convertNormalToSQLCipheredDB(Context context,String startingFileName, String endingFileName, String filePassword)
 throws IOException {
  File mStartingFile = context.getDatabasePath(startingFileName);
  if (!mStartingFile.exists()) {
   return;
  }
  File mEndingFile = context.getDatabasePath(endingFileName);
  mEndingFile.delete();
  SQLiteDatabase database = null;
  try {
   database = SQLiteDatabase.openOrCreateDatabase(MainApp.mainDBPath,
   "", null);
   database.rawExecSQL(String.format(
   "ATTACH DATABASE '%s' AS encrypted KEY '%s'",
   mEndingFile.getAbsolutePath(), filePassword));
   database.rawExecSQL("select sqlcipher_export('encrypted')");
   database.rawExecSQL("DETACH DATABASE encrypted");
   database.close();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   if (database.isOpen())
    database.close();
   mStartingFile.delete();
  }
 }

詳情請見https://codeday.me/bug/20191030/1964560.html

注意事項(xiàng):

1.WCDB默認(rèn)加密后的db文件的pagesize為4096字節(jié),為了與官方SQLCipher創(chuàng)建的數(shù)據(jù)庫兼容庫中模叙，必須使用頁面大小指定SQLiteCipherSpec1024字節(jié)。否則Android代碼打開數(shù)據(jù)庫時(shí)將提示：

net.sqlcipher.database.SQLiteException: file is encrypted or is not a database

2.在進(jìn)行任何操作之前需要先使用pragma key=...來解密數(shù)據(jù)庫鞋屈，否則可能會報(bào)錯(cuò)“Error: file is encrypted or is not a database”范咨，這里網(wǎng)上也有很多人跟我一樣遇到。
3.wcdb使用了sqlcipher來加密的厂庇，在加解密的時(shí)候必須使用一致的版本渠啊，比如我們使用sqlcipher3.x加密的，那么在解密的時(shí)候也必須使用3.x版本权旷，否則就會解密失敗替蛉。
原因：https://github.com/Tencent/wcdb/search?q=64000&unscoped_q=64000

參考

1.wcdb使用筆記
http://www.reibang.com/p/8fab9eba909d
2.SQlite數(shù)據(jù)庫的加密與解密
http://www.reibang.com/p/0b2376f3d579
3.解決sqlcipher從3.5.9升級到4.0.1引起的崩潰問題

Caused by: net.sqlcipher.database.SQLiteException: file is not a database: , while compiling: select count(*) from sqlite_master;

http://www.reibang.com/p/9579f9cef85b

測試代碼

https://github.com/CentForever/TestDBFlow.git

測試軟件

https://sqlitestudio.pl/index.rvt?act=download