安卓应用安全指南 4.5.1 使用 SQLite 示例代码

原书：Android Application Secure Design/Secure Coding Guidebook

译者：飞龙

协议：CC BY-NC-SA 4.0

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;
    }
}