Encrypt data in SQLite

Question

How can I encrypt my data?

As far as the solutions I study there are two ways:

1. Encrypt data using an algorithm provided by android. I use "Cipher" is provided by android to encrypt my data. But I'm having problems retrieving data with large number of records, making the app's performance significantly reduced.
2. I coded the whole database and then every session I decoded and saved in the cache but no algorithm was found to solve this.

Answer 1

I use the following for encrypting/decrypting at a column level, i.e. it is only applied to the sensitive data.

class EncryptDecrypt {
    private Cipher cipher;
    private static SecretKeySpec secretKeySpec;
    private static IvParameterSpec ivParameterSpec;
    private boolean do_encrypt = true;

    /**
     * Construct EncryptDecrypt instance that checks the current logged-in
     * user status; basically if the user is the special NOUSER, where
     * the user does not use a password, then encryption decryption is
     * skipped, the alternative constructor does not undergo this check and
     * thus will always encrypt (see alternative)
     * @param context   The context, required for database usage (user)
     * @param skey      The secret key to be used to encrypt/decrypt
     * @param userid    The userid (so that the salt can be obtained)
     */
    EncryptDecrypt(Context context, String skey, long userid) {
        DBUsersMethods users = new DBUsersMethods(context);
        if (MainActivity.mLoginMode == LoginActivity.LOGINMODE_NONE) {
            do_encrypt = false;
            return;
        }
        String saltasString = users.getUserSalt(userid);
        String paddedskey = (skey + saltasString).substring(0,16);

        secretKeySpec = new SecretKeySpec(paddedskey.getBytes(),"AES/CBC/PKCS5Padding");
        ivParameterSpec = new IvParameterSpec((saltasString.substring(0,16)).getBytes());
        try {
            cipher = Cipher.getInstance("AES/CBC/PKCS5Padding");
        } catch (Exception e){
        }
    }

    /**
     * Construct EncryptDecrypt instance that does not check user login-in
     * mode, thus the assumption is that this user is NOT the special user
     * NOUSER that doesn't require a password to login; this constructor
     * is designed to ONLY be used when a user has been added by NOUSER,
     * and to then encrypt the data using the enccryptForced method solely
     * to encrypt any existing card data for the new user that has a password.
     *
     * @param context   The context, required for database usage (user)
     * @param skey      The secret key to be used to encrypt/decrypt
     * @param userid    The userid (so that the salt can be obtained)
     * @Param mode      Not used other than to distinguish constructor
     */
    EncryptDecrypt(Context context, String skey, long userid, boolean mode) {
        DBUsersMethods users = new DBUsersMethods(context);
        String saltasString = users.getUserSalt(userid);
        String paddedskey = (skey + saltasString).substring(0,16);
        secretKeySpec = new SecretKeySpec(paddedskey.getBytes(),"AES/CBC/PKCS5Padding");
        ivParameterSpec = new IvParameterSpec((saltasString.substring(0,16)).getBytes());
        try {
            cipher = Cipher.getInstance("AES/CBC/PKCS5Padding");
        } catch (Exception e){
            //e.printStackTrace();
        }
    }

    /**
     * Normal encryption routine that will not encrypt data if the user is
     * the special case NOUSER (i.e LOGIN mode is NOUSER), otherwise data
     * is encrypted.
     *
     * @Param toEncrypt     The string to be encrypted
     * @return              The encryted (or not if NOUSER) data as a string
     */
    String encrypt(String toEncrypt) {
        if (!do_encrypt) {
            return toEncrypt;
        }
        byte[] encrypted;
        try {
            cipher.init(Cipher.ENCRYPT_MODE,secretKeySpec,ivParameterSpec);
            encrypted = cipher.doFinal(toEncrypt.getBytes());
        } catch (Exception e) {
            //e.printStackTrace();
            return null;
        }
        return Base64.encodeToString(encrypted,Base64.DEFAULT);
    }

    /**
     * Encryption, irrespective of the USER type, noting that this should
     * only be used in conjunction with an EncryptDecrypt instance created
     * using the 2nd/extended constructor
     *
     * @param toEncrypt     The string to be encrypted
     * @return              The encrypted data as a string
     */
    String encryptForced(String toEncrypt) {
        byte[] encrypted;
        try {
            cipher.init(Cipher.ENCRYPT_MODE,secretKeySpec,ivParameterSpec);
            encrypted = cipher.doFinal(toEncrypt.getBytes());
        } catch (Exception e) {
            //e.printStackTrace();
            return null;
        }
        return Base64.encodeToString(encrypted,Base64.DEFAULT);
    }

    /**
     * Decrypt an encrypted string
     * @param toDecrypt     The encrypted string to be decrypted
     * @return              The decrypted string
     */
    String decrypt(String toDecrypt)  {
        if (!do_encrypt) {
            return toDecrypt;
        }
        byte[] decrypted;
        try {
            cipher.init(Cipher.DECRYPT_MODE,secretKeySpec,ivParameterSpec);
            decrypted = cipher.doFinal(Base64.decode(toDecrypt,Base64.DEFAULT));
        } catch (Exception e) {
            //e.printStackTrace();
            return null;
        }
        return new String(decrypted);
    }
}

Example usage (encryption) :-

    ........
    db.beginTransaction();
    ........
    newed = new EncryptDecrypt(mContext,newPassword,newUserId,true);

    // Process the MatrixCursor created above
    while (mxc.moveToNext()) {
        // Generate the whereclause to determine which row is to be updated
        whereclause = DBCardsTableConstants.CARDID.getDBColumnName() + "=?";
        // Set the value to replace the ? plcaeholder with the current CARDID
        whereargs = new String[]{
                Long.toString(
                        mxc.getLong(
                                mxc.getColumnIndex(
                                        DBCardsTableConstants.CARDID.getDBColumnName()
                                )
                        )
                )
        };

        String mxc_nameoncard = mxc.getString(mxc.getColumnIndex(DBCardsTableConstants.CARDNAMEONCARD.getDBColumnName()));
        String mxc_number = mxc.getString(mxc.getColumnIndex(DBCardsTableConstants.CARDNUMBER.getDBColumnName()));
        String mxc_cvv = mxc.getString(mxc.getColumnIndex(DBCardsTableConstants.CARDCVVCODE.getDBColumnName()));
        String mxc_pin = mxc.getString(mxc.getColumnIndex(DBCardsTableConstants.CARDPIN.getDBColumnName()));
        String mxc_exp = mxc.getString(mxc.getColumnIndex(DBCardsTableConstants.CARDEXPIRYDATE.getDBColumnName()));
        String enc_nameoncard = newed.encryptForced(mxc_nameoncard);
        String enc_number = newed.encryptForced(mxc_number);
        String enc_cvv = newed.encryptForced(mxc_cvv);
        String enc_pin = newed.encryptForced(mxc_pin);
        String enc_exp = newed.encryptForced(mxc_exp);

        // Prepare the data to be updated.
        ContentValues cv = new ContentValues();
        cv.put(DBCardsTableConstants.CARDOWNER.getDBColumnName(),
                newUserId
        );
        cv.put(DBCardsTableConstants.CARDNAMEONCARD.getDBColumnName(),enc_nameoncard);
        cv.put(DBCardsTableConstants.CARDNUMBER.getDBColumnName(),enc_number);
        cv.put(DBCardsTableConstants.CARDCVVCODE.getDBColumnName(),enc_cvv);
        cv.put(DBCardsTableConstants.CARDPIN.getDBColumnName(),enc_pin);
        cv.put(DBCardsTableConstants.CARDEXPIRYDATE.getDBColumnName(),enc_exp);
        // Perform the update
        db.update(DBCardsTableConstants.CARDS.getDBTableName(),
                cv,
                whereclause,
                whereargs
        );
    }
    // Done with the MatrixCursor so close it
    mxc.close();
    // Done updating so apply all the changes
    db.setTransactionSuccessful();
    // Done with the transaction
    db.endTransaction();

Example Usage (decryption)

Cursor getDecyrptedCard(long cardid) {
    EncryptDecrypt ed = new EncryptDecrypt(mContext,
            LoginActivity.getCurrentUserPassWord(),
            MainActivity.mCurrentUserid);
    MatrixCursor cnvcsr = new MatrixCursor(mCardsTableColumns,10);
    String whereclause = DBCardsTableConstants.CARDID.getDBColumnName() +
            "=? AND " +
            DBCardsTableConstants.CARDOWNER.getDBColumnName() +
            "=?";
    String[] whereargs = {Long.toString(cardid), Long.toString(MainActivity.mCurrentUserid)};
    Cursor basecsr = db.query(DBCardsTableConstants.CARDS.getDBTableName(),
            null,
            whereclause,
            whereargs,
            null,null,null,null);
    // Check to see of card exists (always should)
    if (!basecsr.moveToFirst()) {
        cnvcsr.addRow(new Object[]{0L,0L,"NOTACARD","NOTACARD","","","","",""});
        return cnvcsr;
    }
    // If Card is for NOUSER then no decryption requires so return
    // base cursor after repositioning to before first.
    if (MainActivity.getLoginMode() == LoginActivity.LOGINMODE_NONE) {
        basecsr.moveToPosition(-1);
        return basecsr;
    }
    // Get data to decrypt
    String extracted_cardnameoncard = basecsr.getString(basecsr.getColumnIndex(
            DBCardsTableConstants.CARDNAMEONCARD.getDBColumnName()
    ));
    String extracted_cardnumber = basecsr.getString(basecsr.getColumnIndex(
            DBCardsTableConstants.CARDNUMBER.getDBColumnName()
    ));
    String extracted_cardcvv = basecsr.getString(basecsr.getColumnIndex(
            DBCardsTableConstants.CARDCVVCODE.getDBColumnName()
    ));
    String extracted_cardpin = basecsr.getString(basecsr.getColumnIndex(
            DBCardsTableConstants.CARDPIN.getDBColumnName()
    ));
    String extracted_cardexpiry = basecsr.getString(basecsr.getColumnIndex(
            DBCardsTableConstants.CARDEXPIRYDATE.getDBColumnName()
    ));
    // Decrypt data
    String decrypted_nameoncard = ed.decrypt(extracted_cardnameoncard);
    String decrypted_cardnumber = ed.decrypt(extracted_cardnumber);
    String deccrypted_cardcvv = ed.decrypt(extracted_cardcvv);
    String decrypted_expiry = ed.decrypt(extracted_cardexpiry);
    String decrypted_cardpin = ed.decrypt(extracted_cardpin);

    // Store decrypted data
    cnvcsr.addRow(new Object[]{
            basecsr.getLong(
                    basecsr.getColumnIndex(
                            DBCardsTableConstants.CARDID.getDBColumnName()
                    )),
            basecsr.getLong(
                    basecsr.getColumnIndex(
                            DBCardsTableConstants.CARDTYPEREF.getDBColumnName()
                    )
            ),
            basecsr.getLong(
                    basecsr.getColumnIndex(
                            DBCardsTableConstants.CARDOWNER.getDBColumnName()
                    )
            ),
            basecsr.getString(
                    basecsr.getColumnIndex(
                            DBCardsTableConstants.CARDNAME.getDBColumnName()
                    )
            ),
            decrypted_nameoncard,
            decrypted_cardnumber,
            deccrypted_cardcvv,
            decrypted_cardpin,
            decrypted_expiry,
            basecsr.getString(
                    basecsr.getColumnIndex(
                            DBCardsTableConstants.CARDNOTES.getDBColumnName()
                    )
            ),
            basecsr.getInt(
                    basecsr.getColumnIndex(
                            DBCardsTableConstants.CARDCOLOUR.getDBColumnName()
                    )
            )
    });

    basecsr.close();
    return cnvcsr;
}

Note! NOUSER is when a single user of the App decides to not use a password/login.