安卓应用安全指南 4.5.1 使用 SQLite 示例代码
原书：Android Application Secure Design/Secure Coding Guidebook
译者：飞龙
4.5.1.1 创建/操作数据库
在 Android 应用中处理数据库时，可以通过使用SQLiteOpenHelper [10] 来实现数据库文件的适当安排和访问权限设置（拒绝其他应用访问的设置）。 下面是一个简单的应用示例，它在启动时创建数据库，并通过 UI 执行搜索/添加/更改/删除数据。 示例代码完成了 SQL 注入的防范，来避免来自外部的输入执行不正确的 SQL。
[10] 对于文件存储，可以将绝对文件路径指定为
SQLiteOpenHelper构造函数的第二个参数（名称）。 因此，如果指定了 SD 卡路径，则需要注意，存储的文件可以被其他应用读取和写入。
1) SQLiteOpenHelper应该用于创建数据库。
2) 使用占位符。
3) 根据应用要求验证输入值。
SampleDbOpenHelper.java
package org.jssec.android.sqlite;
import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;
public class SampleDbOpenHelper extends SQLiteOpenHelper {
private SQLiteDatabase mSampleDb; //Database to store the data to be handled
public static SampleDbOpenHelper newHelper(Context context) {
//*** POINT 1 *** SQLiteOpenHelper should be used for database creation.
return new SampleDbOpenHelper(context);
}
public SQLiteDatabase getDb() {
return mSampleDb;
}
//Open DB by Writable mode
public void openDatabaseWithHelper() {
try {
if (mSampleDb != null && mSampleDb.isOpen()) {
if (!mSampleDb.isReadOnly())// Already opened by writable mode
return;
mSampleDb.close();
}
mSampleDb = getWritableDatabase(); //It's opened here.
} catch (SQLException e) {
//In case fail to construct database, output to log
Log.e(mContext.getClass().toString(), mContext.getString(R.string.DATABASE_OPEN_ERROR_MESSAGE));
Toast.makeText(mContext, R.string.DATABASE_OPEN_ERROR_MESSAGE, Toast.LENGTH_LONG).show();
}
}
//Open DB by ReadOnly mode.
public void openDatabaseReadOnly() {
try {
if (mSampleDb != null && mSampleDb.isOpen()) {
if (mSampleDb.isReadOnly())// Already opened by ReadOnly.
return;
mSampleDb.close();
}
SQLiteDatabase.openDatabase(mContext.getDatabasePath(CommonData.DBFILE_NAME).getPath(), null, SQLiteDatabase.OPEN_READONLY);
} catch (SQLException e) {
//In case failed to construct database, output to log
Log.e(mContext.getClass().toString(), mContext.getString(R.string.DATABASE_OPEN_ERROR_MESSAGE));
Toast.makeText(mContext, R.string.DATABASE_OPEN_ERROR_MESSAGE, Toast.LENGTH_LONG).show();
}
}
//Database Close
public void closeDatabase() {
try {
if (mSampleDb != null && mSampleDb.isOpen()) {
mSampleDb.close();
}
} catch (SQLException e) {
//In case failed to construct database, output to log
Log.e(mContext.getClass().toString(), mContext.getString(R.string.DATABASE_CLOSE_ERROR_MESSAGE));
Toast.makeText(mContext, R.string.DATABASE_CLOSE_ERROR_MESSAGE, Toast.LENGTH_LONG).show();
}
}
//Remember Context
private Context mContext;
//Table creation command
private static final String CREATE_TABLE_COMMANDS
= "CREATE TABLE " + CommonData.TABLE_NAME + " ("
+ "_id INTEGER PRIMARY KEY AUTOINCREMENT, "
+ "idno INTEGER UNIQUE, "
+ "name VARCHAR(" + CommonData.TEXT_DATA_LENGTH_MAX + ") NOT NULL, "
+ "info VARCHAR(" + CommonData.TEXT_DATA_LENGTH_MAX + ")"
+ ");";
public SampleDbOpenHelper(Context context) {
super(context, CommonData.DBFILE_NAME, null, CommonData.DB_VERSION);
mContext = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
try {
db.execSQL(CREATE_TABLE_COMMANDS); //Execute DB construction command
} catch (SQLException e) {
//In case failed to construct database, output to log
Log.e(this.getClass().toString(), mContext.getString(R.string.DATABASE_CREATE_ERROR_MESSAGE));
}
}
@Override
public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
// It's to be executed when database version up. Write processes like data transition.
}
}DataSearchTask.java（SQLite 数据库项目）
package org.jssec.android.sqlite.task;
import org.jssec.android.sqlite.CommonData;
import org.jssec.android.sqlite.DataValidator;
import org.jssec.android.sqlite.MainActivity;
import org.jssec.android.sqlite.R;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.AsyncTask;
import android.util.Log;
//Data search task
public class DataSearchTask extends AsyncTask<String, Void, Cursor> {
private MainActivity mActivity;
private SQLiteDatabase mSampleDB;
public DataSearchTask(SQLiteDatabase db, MainActivity activity) {
mSampleDB = db;
mActivity = activity;
}
@Override
protected Cursor doInBackground(String... params) {
String idno = params[0];
String name = params[1];
String info = params[2];
String cols[] = {"_id", "idno","name","info"};
Cursor cur;
//*** POINT 3 *** Validate the input value according the application requirements.
if (!DataValidator.validateData(idno, name, info)){
return null;
}
//When all parameters are null, execute all search
if ((idno == null || idno.length() == 0) &&
(name == null || name.length() == 0) &&
(info == null || info.length() == 0) ) {
try {
cur = mSampleDB.query(CommonData.TABLE_NAME, cols, null, null, null, null, null);
} catch (SQLException e) {
Log.e(DataSearchTask.class.toString(), mActivity.getString(R.string.SEARCHING_ERROR_MESSAGE));
return null;
}
return cur;
}
//When No is specified, execute searching by No
if (idno != null && idno.length() > 0) {
String selectionArgs[] = {idno};
try {
//*** POINT 2 *** Use place holder.
cur = mSampleDB.query(CommonData.TABLE_NAME, cols, "idno = ?", selectionArgs, null, null, null);
} catch (SQLException e) {
Log.e(DataSearchTask.class.toString(), mActivity.getString(R.string.SEARCHING_ERROR_MESSAGE));
return null;
}
return cur;
}
//When Name is specified, execute perfect match search by Name
if (name != null && name.length() > 0) {
String selectionArgs[] = {name};
try {
//*** POINT 2 *** Use place holder.
cur = mSampleDB.query(CommonData.TABLE_NAME, cols, "name = ?", selectionArgs, null, null, null);
} catch (SQLException e) {
Log.e(DataSearchTask.class.toString(), mActivity.getString(R.string.SEARCHING_ERROR_MESSAGE));
return null;
}
return cur;
}
//Other than above, execute partly match searching with the condition of info.
String argString = info.replaceAll("@", "@@"); //Escape $ in info which was received as input.
argString = argString.replaceAll("%", "@%"); //Escape % in info which was received as input.
argString = argString.replaceAll("_", "@_"); //Escape _ in info which was received as input.
String selectionArgs[] = {argString};
try {
//*** POINT 2 *** Use place holder.
cur = mSampleDB.query(CommonData.TABLE_NAME, cols, "info LIKE '%' || ? || '%' ESCAPE '@'", selectionArgs, null, null, null);
} catch (SQLException e) {
Log.e(DataSearchTask.class.toString(), mActivity.getString(R.string.SEARCHING_ERROR_MESSAGE));
return null;
}
return cur;
}
@Override
protected void onPostExecute(Cursor resultCur) {
mActivity.updateCursor(resultCur);
}
}DataValidator.java
package org.jssec.android.sqlite;
public class DataValidator {
//Validate the Input value
//validate numeric characters
public static boolean validateNo(String idno) {
//null and blank are OK
if (idno == null || idno.length() == 0) {
return true;
}
//Validate that it's numeric character.
try {
if (!idno.matches("[1-9][0-9]*")) {
//Error if it's not numeric value
return false;
}
} catch (NullPointerException e) {
//Detected an error
return false;
}
return true;
}
// Validate the length of a character string
public static boolean validateLength(String str, int max_length) {
//null and blank are OK
if (str == null || str.length() == 0) {
return true;
}
//Validate the length of a character string is less than MAX
try {
if (str.length() > max_length) {
//When it's longer than MAX, error
return false;
}
} catch (NullPointerException e) {
//Bug
return false;
}
return true;
}
// Validate the Input value
public static boolean validateData(String idno, String name, String info) {
if (!validateNo(idno)) {
return false;
}
if (!validateLength(name, CommonData.TEXT_DATA_LENGTH_MAX)) {
return false;
}else if(!validateLength(info, CommonData.TEXT_DATA_LENGTH_MAX)) {
return false;
}
return true;
}
}