如何使用SQLiteAssetHelper从加密的数据库中读取数据? [英] How can I read data from an encrypted DB using SQLiteAssetHelper?

问题描述

我有一个加密的数据库，并已插入资产文件夹中.我正在使用SQLiteAssetHelper类从数据库检索数据.当我从加密的数据库中检索数据时，它会引发类似"E/SQLiteLog:(26)文件已加密或不是数据库"的错误.反正有使用SQLiteAssetHelper从加密的数据库读取数据吗?

I have an encrypted DB and inserted in asset folder. I am using SQLiteAssetHelper class to retrieve data from DB. When I retrieve data from encrypted DB it throws error like " E/SQLiteLog: (26) file is encrypted or is not a database". Is there anyway to read data from encrypted DB using SQLiteAssetHelper?

推荐答案

我相信您需要:-

a)将文件从资产文件复制到临时位置或最终目的地(数据/数据/package_name/数据库).

a) Copy the file from the assets file to a temporary location or to the final destination (data/data/package_name/databases).

• 其中package_name将根据软件包而定.
  • getDatabasePath(databasename).getPath()将以字符串形式检索路径，例如mDBPath = context.getDatabasePath(database).getPath();，因此您甚至不必编写程序包名称.
  • Where package_name will be according to the package.
    • getDatabasePath(databasename).getPath() will retrieve the path as a string e.g. mDBPath = context.getDatabasePath(database).getPath(); so you don't even have to code the package name.

    b)提示并获取密码.

    b) Prompt for and retrieve the pass-phrase.

    c)解密文件(将其复制到复制到临时位置的最终目的地).

    c) Decrypt the file (copying it to the final destination copied to a temporary location).

    d)然后访问数据库.

    d) Then access the database.

    注意！数据库将不再被加密，因此如果您想要加密，则必须引入加密.

    Note! The database would no longer be encrypted so if you wanted encryption you would have to then introduce encryption.

    您可以修改以下代码，即添加解密代码(如所示).

    You could adapt the following code i.e. add the decrypt code (as indicated).

    • 请注意，该代码不是用于生产的，而是为试验/探索期初资产而创建的代码.因此，它几乎记录了所有内容.

    • Note this code is not intended for production but is code that was created for experimentation/exploration of opening assets. As such it's way over the top with logging virtually everything.

    它还被设计为最多可容纳10个数据库(根据MAXIMUM_HELPERS和helper_index).该示例用法使用3个数据库(注释掉的第4个用于测试非数据库文件).

    It's also been designed to cater for up to 10 databases (as per MAXIMUM_HELPERS and helper_index). The example usage uses 3 databases (the commented out 4th was for testing a non-databse file).

    代码(注意在其中放置解密代码的地方):-

    The code (note split where decryption code would be placed) :-

    public class OpenAssetDBHelper extends SQLiteOpenHelper {
    
        private static final String LOGTAG = "OADB-HLPR";
        public static final int MAXIMUM_HELPERS = 10;
        private String mDBPath, mAssetPath;
        private static OpenAssetDBHelper mInstance[] = new OpenAssetDBHelper[MAXIMUM_HELPERS];
        private SQLiteDatabase mDB;
    
        /**
         * OpenAssetDBHelper Class that will copy a predefined database
         * from the assets folder and then open it is the database;
         *
         * The copy will only be done if the database does not exist;
         *
         * Note this code is intended to be used for development and/or
         * experimentation, hence the extensive logging.
         */
    
        /**
         * get an OpenAssetDBHelper instance as a singleton;
         * Note! caters for up to 10 OpenAssetDBHelpers for up to 10 databases
         * as specified by the helper_index
         *
         * @param helper_index  Index to this instance/database
         *                      (0-MAXIMUM_HELPERS less 1)
         * @param context       Context for the database
         * @param database      Database name (i.e. file name)
         * @param dbversion     Database version (user_version)
         * @param assetfile     Name of the asset file to be copied to the database
         * @param asset_sub_directories String Array of the sub-directories where
         *                              the asset file is located;
         *                              MUST be in order
         * @return              The resultant OpenAssetDBHelper
         */
        public static synchronized OpenAssetDBHelper getInstance(
                int helper_index,
                Context context,
                String database,
                int dbversion,
                String assetfile,
                String[] asset_sub_directories) {
            // Checck that helper_index is within bounds
            if (helper_index > (MAXIMUM_HELPERS -1)) {
                throw new RuntimeException(
                        "Helper Index greater than " +
                                MAXIMUM_HELPERS
                );
            }
            if (helper_index < 0) {
                throw new RuntimeException(
                        "Helper Index cannot be negative, must be 0-" +
                                MAXIMUM_HELPERS
                );
            }
            // Create the respective OpenAssetDBHelper instance
            if(mInstance[helper_index] == null) {
                mInstance[helper_index] = new OpenAssetDBHelper(context,
                        database,
                        dbversion,
                        assetfile,
                        asset_sub_directories);
            }
            return mInstance[helper_index];
        }
    
        /**
         * Construct an OpenAssetDBHelper instance;
         * Note! can only be called within class
         *
         * @param context       the context to be used
         * @param database      the database name (equates to filename)
         * @param dbversion     the databaae version (user_version)
         * @param assetfile     The name of the asset file i.e. the pre-defined db
         * @param directories   The hierarchy of directories within the assets folder
         *                      where the asset file is located
         *                      (null or zero elements = in the assets folder)
         */
        private OpenAssetDBHelper(Context context,
                                  String database,
                                  int dbversion,
                                  String assetfile,
                                  String[] directories) {
            super(context, database, null, dbversion);
            Log.d(LOGTAG,"OpenAssetDBHelper being constructed.");
    
            mDBPath = context.getDatabasePath(database).getPath();
            if (assetfile == null || assetfile.length() < 1) {
                assetfile = database;
            }
            mAssetPath = buildAssetPath(directories,assetfile);
    
            if (!ifDatabaseExists(mDBPath)) {
                Log.d(LOGTAG,"Database " + database + " not found at " + mDBPath + " so attempting to copy from assets.");
                if (copyDatabaseFromAssets(context,mDBPath, database, mAssetPath)) {
                    Log.d(LOGTAG, "Successfully Copied Database from Assets.");
                } else {
                    throw new RuntimeException("No Database Available.");
                }
            }
            // Force Database open and store it
            this.mDB = this.getWritableDatabase();
            logDatabaseTableInformation(mDB);
            Log.d(LOGTAG,"OpenAssetDBHelper constructed.");
        }
    
        @Override
        public void onCreate(SQLiteDatabase db) {
            Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " initiated.");
            // As Database is copied from assets nothing to do in onCreate!
            Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " completed.");
        }
    
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " initiated.");
            // Nothing to do as it's early days in the Database's lifetime.
            Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " completed.");
        }
    
        /**
         * Check to see if the Database exists,
         * if it doesn't exists then check to see if
         * the database directory exists,
         * if the directory(ies) does(do) not exist then make the directory(ies);
         *
         *
         * @param dbpath        The path to the database
         * @return              true if the database exists, else false
         */
        private boolean ifDatabaseExists(String dbpath) {
            Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " initiated.");
            File db = new File(dbpath);
            if(db.exists()) return true;
            File dir = new File(db.getParent());
            if (!dir.exists()) {
                dir.mkdirs();
            }
            return false;
        }
    
        /**
         * Copy the Database from the assets folder
         * @param context
         * @param dbpath
         * @return
         */
        private boolean copyDatabaseFromAssets(Context context,String dbpath, String dbname, String asset) {
            String thisclass = new Object(){}.getClass().getEnclosingMethod().getName();
            Log.d(LOGTAG,thisclass + " initiated");
            InputStream assetsdb;
            OutputStream database;
            File db = new File(dbpath);
            int filesize;
            // Get the asset file
            try {
                Log.d(LOGTAG,thisclass + " attempting to find asset " + asset);
                assetsdb = context.getAssets().open(asset);
                filesize = assetsdb.available();
                Log.d(LOGTAG,thisclass + " asset " + asset +
                        " located successfully with a size of " +
                        Integer.toString(filesize)
                );
            } catch (IOException e) {
                Log.d(LOGTAG,thisclass + " Did not locate asset " + asset);
                e.printStackTrace();
                return false;
            }
    

    >>>>您的解密将在此处<<<<

            // Read the first 16 bytes from the asset file
            byte[] dbcheck = new byte[16];
            try {
                assetsdb.read(dbcheck,0,16);
            } catch (IOException e) {
                Log.d(LOGTAG, thisclass + " failed trying to read 16 bytes to check for a valid database. ");
                e.printStackTrace();
                return false;
            }
    
            // Check that the asset file is an SQLite database
            String chkdb = new String(dbcheck);
            if(!chkdb.equals("SQLite format 3\u0000")) {
                Log.d(LOGTAG,thisclass + " asset " +
                        asset +
                        " is not a valid SQLite Database File (found " +
                        chkdb +
                        " at bytes 1-16 instead of SQLite format 3)");
                try {
                    assetsdb.close();
                } catch (IOException e) {
                    // Not worth doing anything
                }
                return false;
            }
            // Close the asset file
            try {
                assetsdb.close();
            } catch (IOException e) {
                Log.d(LOGTAG,thisclass +
                        " failed to close assets file after checking for a valid database."
                );
                return false;
            }
            // Re-open the asset file
            try {
                assetsdb = context.getAssets().open(asset);
                filesize = assetsdb.available();
            } catch (IOException e) {
                Log.d(LOGTAG, thisclass +
                        " failed trying to re-open asset " +
                        asset +
                        " after checking for a valid database."
                );
                e.printStackTrace();
                return false;
            }
    
            // Read the entire asset file into a buffer
            Log.d(LOGTAG, thisclass +
                    " copying asset database " +
                    dbname +
                    " into buffer of size " +
                    filesize
            );
            byte[] buffer = new byte[filesize];
            // Close the asset file
            try {
                assetsdb.read(buffer);
                Log.d(LOGTAG,thisclass +
                        " closing asset database " + dbname
                );
                assetsdb.close();
            } catch (IOException e) {
                Log.d(LOGTAG, thisclass +
                        " failed while copying asset database " +
                        dbname +
                        " (or closing asset database)."
                );
                e.printStackTrace();
                return false;
            }
            // Open the new database file
            try {
                Log.d(LOGTAG,thisclass + " attempting to open new database file " + dbpath);
                database = new FileOutputStream(dbpath);
            } catch (IOException e) {
                Log.d(LOGTAG, thisclass + " failed to open new database file.");
                e.printStackTrace();
                return false;
            }
            // Write the new database file
            try {
                Log.d(LOGTAG, thisclass + " writing new database file " + dbpath);
                database.write(buffer);
            } catch (IOException e) {
                Log.d(LOGTAG, thisclass + " failed while writing new database file " + dbpath);
                e.printStackTrace();
                return false;
            }
            // Flush the new database file
            try {
                Log.d(LOGTAG, thisclass + " flushing new database file " + dbpath);
                database.flush();
            } catch (IOException e) {
                Log.d(LOGTAG, thisclass + " failed while flushing new database file " + dbpath);
                e.printStackTrace();
                return false;
            }
            // Close the new database file
            try {
                Log.d(LOGTAG, thisclass + " closing new database file " + dbpath);
                database.close();
            } catch (IOException e) {
                Log.d(LOGTAG, thisclass + " failed while closing new database file " + dbpath);
                e.printStackTrace();
                return  false;
            }
            Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " completed.");
            return true;
        }
    
        /**
         * Log Database table Information
         */
        private void logDatabaseTableInformation(SQLiteDatabase db) {
            Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " initiated.");
            String mastertable = "sqlite_master";
            String typecolumn = "type";
            String namecolumn = "name";
            String sqlcolumn = "sql";
            String[] args = new String[]{"table","android_metadata"};
            Cursor csr = db.query(mastertable,
                    null,
                    typecolumn + "=? AND " + namecolumn + "!=?",
                     args,
                    null,null,null
            );
            while (csr.moveToNext()) {
                Log.d(LOGTAG,"Database contains Table " +
                        csr.getString(csr.getColumnIndex(namecolumn)) +
                        " created by SQL " +
                        csr.getString(csr.getColumnIndex(sqlcolumn))
                );
                logTableInformation(db, csr.getString(csr.getColumnIndex(namecolumn)));
            }
            csr.close();
            Log.d(LOGTAG,new Object(){}.getClass().getEnclosingMethod().getName() + " completed.");
        }
    
        /**
         * Write Table information, Table name, Column Count,
         * Row Count and Column Names to the Log
         * @param table Name of the table to be reported on
         */
        private void logTableInformation(SQLiteDatabase db, String table) {
            Cursor csr = db.query(table,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null
            );
            Log.d(LOGTAG,"Table is " +
                    table +
                    " Column Count = " +
                    Integer.toString(csr.getColumnCount()) +
                    " Row Count = " +
                    Long.toString(DatabaseUtils.queryNumEntries(mDB,table))
            );
            StringBuilder columns_as_string = new StringBuilder();
            for (String s: csr.getColumnNames()) {
                columns_as_string.append(s).append(" ");
            }
            Log.d(LOGTAG, "\tColumns are :- " + columns_as_string);
            csr.close();
        }
    
        /**
         * Build the sub-path to the asset, according to the directories specified
         *
         * @param directories   directories underneath the assets folder where
         *                      the asset files is located, null or empty
         *                      array if file is located directly in the
         *                      assets folder;
         *                      directories must be specified in the order
         *                      in which they appear in the path.
         * @param filename      The filename of the asset
         * @return              The fill sub-path to the asset
         */
        private String buildAssetPath(String[] directories, String filename) {
            StringBuilder sb = new StringBuilder();
            final String SEPERATOR = "/";
            if (directories != null && directories.length > 0) {
                for (String s: directories) {
                    sb.append(s);
                    if (!s.substring(s.length()-1,s.length()).equals(SEPERATOR)) {
                        sb.append(SEPERATOR);
                    }
                }
                sb.append(filename);
                return sb.toString();
            } else {
                return filename;
            }
        }
    }
    

    示例用法:-

    public class MainActivity extends AppCompatActivity {
    
        public static final int DBVERSION = 1;
        public static final String DBNAME1 = "openassetdb.db";
        public static final int DB1_INDEX = 0;
        public static final String DBNAME2 = "myshopping";
        public static final int DB2_INDEX = 1;
        public static final String DBNAME3 = "rumplestilskin";
        public static final int DB3_INDEX = 2;
        public static final String DBNAME4 = "notadatabase";
        public static final int DB4_INDEX = 3;
    
        OpenAssetDBHelper mOADBHlpr;
        OpenAssetDBHelper mShoppingHelper;
        OpenAssetDBHelper mShopper;
        OpenAssetDBHelper mNotADatabase;
    
        @Override
        protected void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
            // Example 1 - Database file located in the assets folder and
            //              database name same as file name
            mOADBHlpr = OpenAssetDBHelper.getInstance(DB1_INDEX,
                    this,
                    DBNAME1,
                    DBVERSION,
                    DBNAME1,
                    null
            );
            // Example 2 - Database file in databases directory of the assets folder
            //              database name different to the asset filename
            mShoppingHelper = OpenAssetDBHelper.getInstance(DB2_INDEX,
                    this,
                    DBNAME2,
                    DBVERSION,
                    "shopper",
                    new String[]{"databases"}
                    );
            // Example 3 - Databse file in databases directory of the assets folder
            //              database name different to the asset filename
            mShopper = OpenAssetDBHelper.getInstance(DB3_INDEX,
                    this,
                    DBNAME3,
                    DBVERSION,
                    "Shopper.sqlite",
                    new String[]{"databases"}
                    );
            /*
            mNotADatabase = OpenAssetDBHelper.getInstance(DB4_INDEX,
                    this,
                    DBNAME4,
                    DBVERSION,
                    "notadatabasefile",
                    new String[]{"databases"});
                    */
        }
    }
    

    说明

    • 3(已注释掉第4个)创建数据库帮助器.

      Explanatory Notes

      • 3 (4th commented out) Database helpers are created.

        第一个通过复制文件openassetdb为名为 openassetdb.db 的数据库(如果不存在)创建名为 mOADBHlpr 的数据库助手.资源文件夹中的.db.

        The first creates the database helper named mOADBHlpr for the database named openassetdb.db (if it doesn't exist) by copying the file openassetdb.db from the assets folder.

        • 传递给OpenAssetDBHelper的第一个参数是辅助程序0-9(在10个受支持的辅助程序实例中)的索引.

        • The first parameter passed to the OpenAssetDBHelper is the index of the helper 0-9 (of the 10 supported helper instances).

        第二个参数是上下文，

        这篇关于如何使用SQLiteAssetHelper从加密的数据库中读取数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！