在上一讲中 Android 使用Sqlcipher给Sqlite加密,我们学习了如何给数据库加密,但有时候我们想看加密数据库中的内容是否有错误,该如何解密呢。Sqlcipher的jar包没有这个方法,而Sqlcipher官网是有相应的工具,但是是收费的,没有交费只能查看部分数据。那如何解密已加密的数据库,获取里面的全部数据信息呢?
一、以前做过一个Demo,是输入数据库路径与密码,输入sql语句,直接查询,可以查看,但在手机上输入sql语句太麻烦。我们改用另外一种思路:
- 用一个工具类,比如BaseSqlUtil输入数据库与密码去打开数据库。这个类主要方法有获取所有的表名,查询表属性组建创建表语句,查询表中所有的数据
private String dbPath, pas;
private SQLiteDatabase db = null;
public BaseSqlUtil(String dbPath, String pas) {
this.dbPath = dbPath;
this.pas = pas;
}
public SQLiteDatabase getDb() {
if (db == null) {
db = SQLiteDatabase.openOrCreateDatabase(dbPath, pas, null);
}
return db;
}
2.用另一个工具类DecodeUtil去创建一个新的数据库!但数据库的密码为“”,
private String dbPath;
private SQLiteDatabase db = null;
public DecodeUtil(String dbPath) {
this.dbPath = dbPath;
}
public SQLiteDatabase getDb() {
if (db == null) {
//注意密码输入空字符串,否则打不开
db = SQLiteDatabase.openOrCreateDatabase(dbPath, "", null);
}
return db;
}
将BaseSqlUtil查出的数据全部插入到此数据库!这样就完成了解密。
二、代码实现
1.布局:activity_main.xml
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:id="@+id/container"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context="com.huang.sqlcipher.MainActivity"
tools:ignore="MergeRootFrame" >
<EditText
android:id="@+id/base_db_path"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="输入解密的db路径"
android:singleLine="true" />
<EditText
android:id="@+id/base_db_pas"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="输入解密密码"
android:singleLine="true" />
<EditText
android:id="@+id/decode_db_path"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="输入解密db存放路径"
android:singleLine="true" />
<Button android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/decode_btn"
android:text="开始解密"/>
</LinearLayout>
2.BaseSqlUtil:
package com.huang.sqlcipher;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import net.sqlcipher.Cursor;
import net.sqlcipher.database.SQLiteDatabase;
import android.util.Log;
/**
* 基本数据库工具类
* @author Administrator
*
*/
public class BaseSqlUtil {
private String dbPath, pas;
private SQLiteDatabase db = null;
public BaseSqlUtil(String dbPath, String pas) {
this.dbPath = dbPath;
this.pas = pas;
}
public SQLiteDatabase getDb() {
if (db == null) {
db = SQLiteDatabase.openOrCreateDatabase(dbPath, pas, null);
}
return db;
}
/**查询所有的表名**/
public List<String> queryTableName() {
List<String> allTableName = new ArrayList<String>();
String sql = "select name from sqlite_master where type='table' order by name";
Cursor cursor = getDb().rawQuery(sql, null);
while (cursor.moveToNext()) {
String name = cursor.getString(0);
allTableName.add(name);
}
cursor.close();
cursor = null;
return allTableName;
}
/**根据表名查询出属性并组建sql**/
public String getSql(String tableName) {
if (getDb() == null) {
return null;
}
String createSql = "create table if not exists " + tableName + " ( ";
Cursor cursor = getDb().rawQuery(
"pragma table_info( " + tableName + " )", null);
while (cursor.moveToNext()) {
//属性名称
String name = cursor.getString(cursor.getColumnIndex("name"));
//属性类型
String type = cursor.getString(cursor.getColumnIndex("type"));
//最后一个属性注意不加,号
if (cursor.isLast()) {
createSql = createSql + name + " " + type ;
}else{
createSql = createSql + name + " " + type +" , ";
}
}
createSql = createSql + " ) ";
Log.i("TAG", "createSql====" + createSql);
cursor.close();
return createSql;
}
/**根据表名查询表中所有的数据**/
public List<Map<String, String>> query(String tableName) {
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
Cursor c = null;
try {
if (getDb() != null) {
c = getDb().rawQuery("select * from " + tableName, null);
int colCount = c.getColumnCount();
long count = 0;
if (colCount > 0) {
while (c.moveToNext()) {
count++;
Log.w("huang", "count==" + count);
Map<String, String> map = new HashMap<String, String>();
for (int i = 0; i < colCount; i++) {
String colName = c.getColumnName(i);
String value = c.getString(i);
map.put(colName, value);
}
list.add(map);
}
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (c != null && !c.isClosed())
c.close();
}
return list;
}
}
3.DecodeUtil解密工具类:
package com.huang.sqlcipher;
import java.util.List;
import java.util.Map;
import java.util.Set;
import net.sqlcipher.database.SQLiteDatabase;
import android.content.ContentValues;
/**
* 解密工具类
* @author Administrator
*
*/
public class DecodeUtil {
private String dbPath;
private SQLiteDatabase db = null;
public DecodeUtil(String dbPath) {
this.dbPath = dbPath;
}
public SQLiteDatabase getDb() {
if (db == null) {
db = SQLiteDatabase.openOrCreateDatabase(dbPath, "", null);
}
return db;
}
//创建表格
public void exeSql(String sql) {
if (getDb() != null)
getDb().execSQL(sql);
}
//插入数据,利用事务
public void insertData(String tableName, List<Map<String, String>> listData) {
getDb().beginTransaction();
for (Map<String, String> mapData : listData) {
ContentValues contentValues = new ContentValues();
Set<Map.Entry<String, String>> set = mapData.entrySet();
for (Map.Entry<String, String> entry : set) {
contentValues.put(entry.getKey(), entry.getValue());
}
getDb().insert(tableName, null, contentValues);
}
getDb().setTransactionSuccessful();
getDb().endTransaction();
}
}
4.MainActivity实现:
package com.huang.sqlcipher;
import java.io.File;
import java.util.List;
import java.util.Map;
import android.app.Activity;
import android.os.AsyncTask;
import android.os.Bundle;
import android.os.Environment;
import android.text.TextUtils;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.EditText;
import android.widget.Toast;
public class MainActivity extends Activity implements OnClickListener {
private EditText base_db_path, base_db_pas, decode_db_path;
private String testPath = Environment.getExternalStorageDirectory().getAbsolutePath();
private BaseSqlUtil baseSqlUtil;
private DecodeUtil decodeUtil;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
base_db_path = (EditText) findViewById(R.id.base_db_path);
base_db_pas = (EditText) findViewById(R.id.base_db_pas);
decode_db_path = (EditText) findViewById(R.id.decode_db_path);
//这里测试直接写路径
base_db_path.setText(testPath+"/test.db");
base_db_pas.setText("123456");//输入正确密码
//创建解密数据库的存放路径
decode_db_path.setText(testPath+"/decode.db");
findViewById(R.id.decode_btn).setOnClickListener(this);
}
@Override
public void onClick(View v) {
switch (v.getId()) {
case R.id.decode_btn:
String baseDbStr = base_db_path.getText().toString();
String baseDbPas = base_db_pas.getText().toString();
String decodeDb = decode_db_path.getText().toString();
if (TextUtils.isEmpty(baseDbStr)) {
showToast("输入正确的要解密数据库的路径");
return;
}
if (!new File(baseDbStr).exists()) {
showToast("输入的解密文件不存在");
return;
}
if (TextUtils.isEmpty(baseDbPas)) {
showToast("解密密码不能为空");
return;
}
if (TextUtils.isEmpty(decodeDb)) {
showToast("解密存放路径不能为空");
return;
}
baseSqlUtil = new BaseSqlUtil(baseDbStr, baseDbPas);
if (baseSqlUtil.getDb()==null) {
showToast("无法打开数据库");
return;
}
decodeUtil = new DecodeUtil(decodeDb);
//查询出要解密数据库的所有表名
new QueryTask().execute();
break;
}
}
private void showToast(String msg){
Toast.makeText(this, msg, Toast.LENGTH_SHORT).show();
}
class QueryTask extends AsyncTask<Void, Void, List<String>>{
@Override
protected List<String> doInBackground(Void... params) {
return baseSqlUtil.queryTableName();
}
@Override
protected void onPostExecute(List<String> result) {
super.onPostExecute(result);
if (result!=null&&result.size()>0) {
for(String table:result){
//根据表名一个一个解密
new DecodeTask().execute(table);
}
}
}
}
class DecodeTask extends AsyncTask<String, Void, Void>{
@Override
protected Void doInBackground(String... params) {
String table = params[0];
Log.i("TAG", "解密表:"+table);
//查询创建语句
String createSql = baseSqlUtil.getSql(table);
//创建表格
decodeUtil.exeSql(createSql);
//查询数据
List<Map<String,String>> listData = baseSqlUtil.query(table);
//插入数据
if (listData.size()>0) {
decodeUtil.insertData(table, listData);
}
Log.i("TAG", "解密表"+table+"完成");
return null;
}
}
}
5.权限:
<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/>
<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE"/>
当解密完成后,在电脑用SQLiteExpertPers是可以打开的。
三、上面的代码存在一些小的问题:
1.BaseSqlUtil中的query查询数据方法,当表中的数据库太大,比如有10w条数据,内存会溢出而导致程序崩溃。应该根据分页来查数据,首先我们获取表中总共数据的条数:
public long getCount(String table){
Cursor cursor = null;
long count = 0;
try {
if (db != null) {
cursor = getDb().rawQuery("select count(*) as total from "+table, null);
int colCount = cursor.getColumnCount();
if (colCount > 0) {
while (cursor.moveToNext()) {
count = cursor.getLong(0);
Log.w("huang", "count==="+count);
}
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (cursor != null && !cursor.isClosed())
cursor.close();
}
return count;
}
根据总数比如总数如果是1w条数据,我们每次可以查出1000条数据,插入1000条后,再查出下1000条数据,直到数据查询完为止。修改后的DecodeTask:
class DecodeTask extends AsyncTask<String, Void, Void>{
@Override
protected Void doInBackground(String... params) {
String table = params[0];
Log.i("TAG", "解密表:"+table);
//查询创建语句
String createSql = baseSqlUtil.getSql(table);
//创建表格
decodeUtil.exeSql(createSql);
long count = baseSqlUtil.getCount(table);
//如果数据大于1000分页查,小于没有影响
if (count >1000) {
int index = (int) (count/1000)+1;
for (int i = 0; i < index; i++) {
int j = i*1000;
String sql = "select * from "+table+" limit "+j+" , 1000";
//查询数据
List<Map<String,String>> listData = baseSqlUtil.query(sql);
//插入数据
if (listData.size()>0) {
decodeUtil.insertData(table, listData);
}
}
}else{
//查询数据
List<Map<String,String>> listData = baseSqlUtil.query("select * from "+table);
//插入数据
if (listData.size()>0) {
decodeUtil.insertData(table, listData);
}
}
Log.i("TAG", "解密表"+table+"完成");
return null;
}
}
baseSqlUtil的query方法修改为:
/**根据表名查询表中所有的数据**/
public List<Map<String, String>> query(String querySql) {
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
Cursor c = null;
try {
if (getDb() != null) {
c = getDb().rawQuery(querySql, null);
int colCount = c.getColumnCount();
long count = 0;
if (colCount > 0) {
while (c.moveToNext()) {
count++;
Log.w("huang", "count==" + count);
Map<String, String> map = new HashMap<String, String>();
for (int i = 0; i < colCount; i++) {
String colName = c.getColumnName(i);
String value = c.getString(i);
map.put(colName, value);
}
list.add(map);
}
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (c != null && !c.isClosed())
c.close();
}
return list;
}
2.改进二:解密完成后,将两个数据库关闭!
3.加入进度条。
以上两点就不贴代码了。
解密完成后效果: