title: Android从非加密数据库迁移到加密数据库
date: 2019-12-26 13:26:53
categories:
- Android
tags:
- SQLITE
如果你之前使用的是非加密数据库，想迁移到加密数据库并保留原来的数据，你需要使用 SQL 函数 sqlcipher_export() 进行迁移。
方案一：WCDB迁移
WCDB 对 sqlcipher_export() 函数做了扩展，原本只接受一个参数为导出到哪个 ATTACHED DB， 现在可以接受第二个参数指定从哪个 DB 导出。因此可以反过来实现导入：
ATTACH 'old_database' AS old;
SELECT sqlcipher_export('main', 'old'); -- 从 'old' 导入到 'main'
DETACH old;
详情请见 sample-encryptdb[https://github.com/Tencent/wcdb/tree/master/android/samples/sample-encryptdb] 示例，它示范了如何使用 SQLiteOpenHelper 实现数据从非加密往加密迁移和 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" //加密必要
关键代码如下：
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
注意事项:
1.WCDB默认加密后的db文件的pagesize为4096字节,为了与官方SQLCipher创建的数据库兼容库中，必须使用页面大小指定SQLiteCipherSpec1024字节。否则Android代码打开数据库时将提示：
net.sqlcipher.database.SQLiteException: file is encrypted or is not a database
2.在进行任何操作之前需要先使用pragma key=...来解密数据库，否则可能会报错“Error: file is encrypted or is not a database”，这里网上也有很多人跟我一样遇到。
3.wcdb使用了sqlcipher来加密的，在加解密的时候必须使用一致的版本，比如我们使用sqlcipher3.x加密的，那么在解密的时候也必须使用3.x版本，否则就会解密失败。
原因：https://github.com/Tencent/wcdb/search?q=64000&unscoped_q=64000
参考
1.wcdb使用笔记
https://www.jianshu.com/p/8fab9eba909d
2.SQlite数据库的加密与解密
https://www.jianshu.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;
https://www.jianshu.com/p/9579f9cef85b
测试代码
https://github.com/CentForever/TestDBFlow.git
网友评论