Hello everyone, today is for this weekTechnical dismantling officerIn the second article, the theme is still the same as the theme of the previous article —How to Use SQLite on AndroidIn the last article we covered the basicsSQLiteDefinition, usage, and development of a basicDemonstrate the DEMOThere are not too familiar with the partner can poke here preview.

Of this article is the sole food guide series | old Android end SQLCipher attack and prevent, this topic is because before when research SQLCipher found an article, here is the original address: FreeBuf | SQLCipher attack and prevent, this also is to borrow the title with everyone again whereas SQLCipher.

Maybe you don’t know what is SQLCipher? Let’s take our time. First, follow the previous ideas, and then analyze the Android side of SQLite. Last week, we learned about the use of SQLite.

The source code for this article is on my personal Github: github.com/lateautumn4…

1. Flaws and solutions of SQLite

SQLite is a lightweight database frequently used in Android. Its advantages are self-evident: easy to use, easy to install, with the characteristics of a relational database, and so on. What are its disadvantages? And what are the technical means or solutions to solve these defects?

1.1 Defects of SQLite

1.1.1 Performance Problems

For ordinary APP developers, SQLite’s easy-to-use features are exciting, but for enterprise-level APP development, performance is always the first indicator of APP development, and SQLite was originally designed as a lightweight, high-performance database. You might wonder why you’re still having performance problems with this design. In fact, the high performance here is conditionally limited, in the case of small data volume, low concurrency, simple query structure, using SQLite on the Android side is no more than a very cost-effective choice, but once these limitations are exceeded, the problems of database design are exposed obviously.

1.1.2 security

What about security? Consider that every APP that uses SQLite has some private data stored in it. Using SQLite is very convenient for developers, but not only for them, but also for people who have “extra ideas”. The free version of SQLite has a fatal flaw: it doesn’t support encryption. That means the database on your APP is actually streaking! For those who want to get the data, as long as the mobile phone ROOT, you can enter the directory of each APP to get the DB file to easily get the data.

1.2 Solutions

1.2.1 Solutions to performance problems

Performance optimization is the most important step in improving SQLite. Optimization can improve SQLite performance in many ways.

  • ORM framework trade-offs

    Most applications will probably introduce an ORM framework to improve development efficiency. Object Relational Mapping (ORM) uses object-oriented concepts to associate tables and objects in a database, so that we don’t care about the underlying implementation of the database. The most commonly used ORM frameworks in Android include open source greenDAO and Google’s official Room. Although these ORM are optimized at the bottom and the loss of part of the execution efficiency has been reduced to a minimum, excessive use of ORM will still cause performance impact. So the trade-offs and dependencies on the ORM framework are also key to performance.

  • Concurrency issues

    If we use it in a projectSQLiteSo this is down hereSQLiteDatabaseLockedExecptionIt’s a problem that often comes up.SQLiteDatabaseLockedExecptionIt comes down to concurrency issues, andSQLiteConcurrency has two dimensions, one isMulti-process concurrencyThe other isMulti-threaded concurrency.

    Let’s first look at multi-process concurrency

    SQLite supports multi-process concurrency by default. It controls multi-process concurrency through file locks. The granularity of SQLite Locking is not very fine, it is for the entire DB file, there are 5 internal states, you can refer to the following article -SQLite Locking.

    In simple terms, multiple processes can simultaneously acquire a SHARED lock to read data, but only one process can acquire an EXCLUSIVE lock to write to the database. EXCLUSIVE prevents other processes from acquiring the SHARED lock to read data. In EXCLUSIVE mode, the database connection does not release the SQLite file lock until it is disconnected, thus avoiding unnecessary conflicts and speeding up database access.

    Let’s look at multi-threaded concurrency

    Multithreaded database access may be more common than multi-process access. SQLite supports concurrent multi-thread mode. You need to enable SQLITE_THREADSAFE. Of course, the system will enable multi-thread mode by default.

    Like the multi-process locking mechanism, SQLite locks are at database file level granularity for simplicity and do not implement table or even row level locks. Note that only one thread is operating with the same handle at the same time. In this case, we need to open the Connection Pool.

    Similar to multiple processes, multiple threads can read from the database simultaneously, but write to the database is still mutually exclusive. SQLite provides a Busy Retry scheme, which is triggered when a block occurs, allowing the thread to sleep for a period of time before retrying the operation.

    To further improve concurrency performance, we can enable WAL (write-ahead-logging) mode. In WAL mode, the modified data is written to a single WAL file. When a read operation begins, the current WAL file status is recorded and only data that precedes it is accessed. WAL locks are also introduced. In WAL mode, reads and writes can also be executed completely concurrently without blocking each other.

    Note, however, that writes are still not concurrent. If there are multiple write concurrent operation situation, still likely SQLiteDatabaseLockedExecption. At this point we can ask the application to catch the exception and wait a while before trying again.

    In general, with connection pooling and WAL mode, we can greatly improve SQLite read/write concurrency and greatly reduce the wait time due to concurrency. It is recommended that you try to enable it in your application.

  • Query optimization

    When it comes to query optimization, indexes are usually the first thing that comes to mind for developers. Let’s take a look at how to optimize queries around indexes.

    There are a number of articles on how to index properly on the Web, such as:

    1. How SQLite indexes work

    2. Official document: Query Planning

    The point is that a lot of times we think we’ve created an index, but it doesn’t actually work. The key here is how to index correctly. For example, operators such as BETWEEN, LIKE, OR are used, expressions are used, and CASE WHEN is used.

    There is a cost to creating an index. The table needs to be updated all the time. For example, there is no need to create an index for a small table. If a table frequently performs insert updates, then moderate index creation is also required. In general, there are several principles:

    • Create the correct index. Not only do we need to make sure that the index actually works in the query, we also want to be able to select the most efficient index. If too many indexes are created for a table, SQLite may not choose the best one to execute when querying.

    • Single – column index, multi – column index and composite index selection. Indexes should combine different queries in the data table with sorting statements. If the query result set is too large, it is expected that the query results can be returned directly in the index table by matching the indexes.

    • Index field selection. Integer indexes are much more efficient than string indexes, and SQLite will create indexes for primary keys by default, so try not to use complex fields for primary keys.

    • In general, index optimization is the simplest and most effective SQLite optimization, but it is not as simple as building an index. Sometimes we need to further adjust the query statement or even the table structure to achieve the best results.

The solution of SQLite performance can be done around the above three aspects, of course, this is only for the optimization of SQLite, if you want to more convenient direct access to the “previous fruits”, you can refer to 2017 wechat open source internal use of SQLite database WCDB, This project is also filled in a lot of SQLite left pits, for the wechat scene to do a lot of optimization, I believe you will use it to avoid a lot of unnecessary trouble.

1.2.2 Solutions to security problems

The solution to the security problem of SQLite is similar to that of many databases. At present, there are mainly two kinds of commonly used solutions:

  • Encrypt the content before writing it to the database

    This way is simple to use, in the process of entering/leaving the library only need to do the corresponding field encryption and decryption operation, to a certain extent to solve the problem of naked exposure of data. But there are big drawbacks:

  1. This way is not a thorough encryption, or through the database to view the table structure and other information.

  2. For the data in the database, the data is scattered, and encrypting and decrypting all the data will seriously affect the performance.

  • Encrypt database files

    The whole database file encryption, this way basically can solve the database information security problem. The existing SQLite encryption is basically implemented in this way. Several common encryption methods are:

    • SQLite Encryption Extension (SEE)

    In fact, SQLite was designed to expose the encryption and decrypt interface, but the free version is not implemented. SQLite Encryption Extension (SEE) is an encrypted version of SQLite and costs a fee

    • SQLiteEncrypt

    Using AES encryption, the principle is that the open source free version of SQLite does not implement the encryption related interface, SQLiteEncrypt is charged.

    • SQLiteCrypt

    Using 256-bit AES encryption, SQLiteEncrypt implements SQLite’s encryption-related interface, and SQLiteCrypt is also charged.

    • SQLCipher

    To be clear, SQLCipher is fully open source and the code is hosted on Github. SQLCipher also uses 256-bit AES encryption. Because it is based on the free version of SQLite, the main encryption interface is the same as SQLite, but it also adds some interfaces of its own.

    For most developers, the free version of SQLCipher is also a security hardening priority, considering both security and cost.

2 know SQLCipher

We have analyzed the advantages and disadvantages of SQLite, and have a basic understanding of the current common solutions. Back to our theme “attack and defense”, this issue will begin to introduce our protagonist -SQLCipher.

2.1 define

Based on SQLite interface design using 256-bit AES encryption algorithm security encryption database.

2.2 the characteristics of

  • Fast only 5-15% performance overhead encryption, the official gave a speed test project, interested friends can test themselves.
  • 100% of the data files in the database are encrypted, is to all data files, including data files and cache, structure files and so on encryption.
  • With good security mode (CBC mode, key derivation), you can choose your own encryption algorithm.
  • Zero configuration and application-level encryption
  • The OpenSSL encryption library provides algorithms

2.3 Encryption Principles

Now we only know that SQLCipher can provide security hardening protection for our database, then what is its implementation principle?

SQLite database is designed with security in mind and encryption related interfaces reserved. But no implementation is given. SQLite database source code by using SQLITE_HAS_CODEC macro to control whether to use database encryption. And reserved four structures for users to achieve their own database encryption effect. The four interfaces are:

  • Sqlite3_key () : specifies the key used by the database
  • Sqlite3_rekey () : resets the key for the database;
  • Sqlite3CodecGetKey () : Returns the current key for the database
  • Sqlite3CodecAttach () : Associates the key and page encoding functions with the database.

SQLCipher implements encryption based on the above four interfaces and user-defined interfaces. The following is a picture to understand the entire encryption process:

  • Encryption entryAs is shown in the figure aboveSQLiteFundamentals of encryptionAPIInterface, where the blue part is the existing module, the gray part is the developer to implement the part.

    • Sqlite3_key is the encrypted entry and needs to be called as soon as sqlite3_open is called to open the database.
    • Sqlite3_key and SQlite3_KEY_v2 are essentially the same, except that the former selects main DB by default, while the latter selects a DB file by name.
    • Sqlite3_rekey is used to change the password, and must be called before decrypting.

  • Encryption on read and write

The preceding figure shows encryption and decryptionAPIThe specific invocation of. When the data is encrypted during a write operation, theCODE2Function that performs a read operation to decrypt the dataCODEC1Function, and the end call issqlite3CodecThis function is controlled mainly by the parameters passed in.

  • The encryption process

Above,SQLCipherThe encryption process is as follows:

  • The key is passed in, and the 16-byte SALT is generated by the Rand_bytes algorithm and stored in the header of the first page of the database (SQLite DB file, the first 16 bytes of the header are fixed in SQLite Format, so you can use the header to store some data).
  • The key and SALT are encrypted by PKCS5_PBKDF2_HMAC_SHA1 algorithm and iterated several times to generate the key used for AES encryption. In this case, the key is encrypted. Even if the original password is leaked, the data cannot be decrypted.
  • The AES symmetric encryption algorithm is used to encrypt the file content (valid content, excluding the file header and reserved fields) on each page.
  • During encryption, after the file is encrypted with AES, the Hmac algorithm obtains the file verification code and fills it in the reserved field at the end of the page (SQLite automatically reserves a space at the end of the page).
  • When decrypting, the Hmac algorithm is first called to obtain the file identifier, and compared with the data at the end of the page. If the data is consistent, the file has not been tampered with; otherwise, the file has been tampered with, and an exception is thrown.

PS: The above is the default algorithm, SQLCipher has no fixed algorithm, users can set their own.

3 Debugging Tools

3.1 the command line

“The most basic debugging scheme”, only need to install SQLCipher in Linux system, other operations are the same as SQLite, the only need to pay attention to SQLCipher is encryption library, that is, we need to add password to DB manually, the operation is roughly as follows:

3.1.1 installing SQLCipher on Linux

3.1.2 Linux generates SQLCipher encryption library

For the originaltech_paoding.dbThe database generates newdecrypted_database.dbThe database

3.2 SQLiteStudio

For visual tool debugging, we still use SQLiteStudio as we introduced in the previous article, but we need to choose to Add a database of type SQLCipher when adding a Database.

Here,Cipher(also known as the encryption algorithm),KDF,Page SizeThe value of is the default good, yesSQLCipher3Value of the default algorithm, if usedSQLCipher4The data will need to be changed.

4 SQLCipher hardening process

For developers, using SQLCipher will not invade the original APP logic, just follow the following two steps to harden the SQLite database.

4.1 class replacement

SQLite: Android.database. SQLite: Android.database. SQLite: Android.database. SQLite: Android.database. SQLite: Android.database. SQLite

import android.database.sqlite.SQLiteDatabase;
Copy the code

For SQLCipher, SQLite classes and related apis are not broken, they are just overwritten, so we need to replace the original package. For SQLiteDatabase, we need to introduce them like this:

import net.sqlcipher.database.SQLiteDatabase;
Copy the code

In addition it is important to note that android. Database. Sqlite is android built-in packages of the project, and.net. Sqlcipher. Database. The SQLiteDatabase requires our introduction, I the introduction of the scheme from the website down corresponding aar package, Add it to your local libs and specify the import path in build.gradle

implementation(name:'android - database - sqlcipher - 2.6.2', ext:'aar')
Copy the code

In this way, we can use SQLCipher to develop smoothly.

4.2 Loading the encrypted SO library

Since the algorithm of SQLCipher is developed based on SO library, we need to use loadLibs method to load the SO encryption library of SQLCipher before normal use of SQLCipher, for example:

SQLiteDatabase.loadLibs(this);
Copy the code

loadLibsThe logic of the method is as follows:

5 DEVELOP SQLCipher Demo

As in the previous article, develop a basic Demo

5.1 create MySQLiteOpenHelper

5.2 create SQLiteCattleActivity

5.3 Dynamic effect display

The main code is shown in the figure above, so let’s take a look at what’s developedDemoThe display:SQLite 和SQLCipherThe functions have been integrated in the sameAPPThrough differentActivityTo show off.

6 enterprise SQLCipher attack and defense example

Above we understand the origin, principle and basic use of SQLCipher, this part we echo the title “attack and defense”, we combined with the market to use SQLCipher encryption database APP to see how they do security protection and those “lawbreakers” how to crack these protection.

6.1 Baidu Chinese

aboutBaidu Chinese APPI mainly downloaded the following versionsAPK I also found them in different versions for the wholeAPPWe’re analyzing the whole thingSQLCipherWe’ll summarize this later, but first we’ll use the latest versionAPPLet’s analyze, as a dictionary typeAPPThere will always be an additional internal database for offline query solutions, so we can look forBaidu Chinese APPTheir database scheme, where are we looking for their offline file entry that can be easily seen hereHere is aOffline filesThe download function can then be guessed by downloading offlinedbFile to populate their offline database, as shown in the second figureFree Offline PackThis list is most likely dynamically obtained through the network interface, because we can’t get it offline.The request can be obtained by capturing the network request packet of this interfaceThe db filesThe address of theBased on the address we got the correspondingThe db filesAfter the download is complete, you get such a filebaidu_dict.dbThe judgment is based onSQLiteFile, putSQLiteStudioIn order toSQLiteMode open will prompt an error, indicating that should be based onSQLCipherEncrypted files. What we need to look for isSQLCipherSo we began to trace the secret key.

6.1.1 SQLCipher Secret key Tracing Process

In the download phase we getbaidu_dict.dbThe key word, we are injadxIn the search ofThe amount of data obtained is not much, the other is withBaidu_Dict.apkRelevant, we can naturally ignore, we mainly observe the first search results, into the specific code viewThere’s no obvious mention of that, thoughbaidu_dict.dbThis key word, but let us find an important clueDB_PATHThe value of the/data/data/com.baidu.dict/databases/What is this address? Recall from the previous article that this list is everyAPPA directory for storing proprietary files, anddatabasesUsually people store themSQLiteWhere the data files are, that’s where the files that we download offline are usually stored, okay, so we don’t have to get them every time we download themThe db filesNow we can just go to this directory and see what we haveThe db files Basically all the relevant files are stored here, the next thing we need to get is the specific secret key, so where do we get the secret key? The simplest, we all knowSQLCipherEncrypted database is required throughgetWritableDatabaseIn order to getThe db instanceSo we can search directlygetWritableDatabaseHow about keywords?The search results are numerous, so we look directly for calls with non-empty parametersThe obvious result is that one is calledSOFunction to save the secret key, also is for the secret key protection. This is of course one way to find the secret key, but it’s a little tricky, so let’s look at it another way. The offline package serves offline search, so let’s look at the offline searchActivityWhat is the calling process of

Get the search firstActivity From the graph we know that iscom.baidu.dict.activity.NewSearchActivityAnd then check this outActivityOk, there’s too much code, so let’s just useObjectionGo to the Hook class to see the call flowLet’s re-enter thisActivityfromObjectionThe interface displays the following informationUsing the search function, the information becomes thisLocate thesearchAllInfoThis methodFrom the above code we can see that one class is worth paying attention to, namelyDictExtDBManagerIs called in the codeDictExtDBManager.checkLocalDBandDictExtDBManager.getInstanceResults obtained, if not available then adoptedsearchOnlineLet’s dig into this class. Part of the code has been removedWe can follow our logic from the code of this classIn the end, we found the exact location of the secret key algorithm

6.1.2 SQLCipher Secret key

Now we’ve found itAPPThe method to get the secret key, we keep tracking, the secret key is usedSOThe function is calledlibimagerender.so, we useIDASpecific view, directly viewExportsthetabYou’ll find specific functions that are statically registeredLocate the specific function, repair can get the real secret key, and then we use this secret key to decrypt the database.

6.1.3 SQLCipher Secret Key Hardening Procedure

After the above analysis of the obtaining process of SQLCipher secret key, say the evolution process of baidu Chinese APP for their SQLCipher secret key reinforcement, the text is not much to say, directly above.

6.2 Xinhua Dictionary

Originally also wanted to write a paragraph of analysis of Xinhua word code APP process, but limited by the length of the text, here is 1.6w words, will not continue, after the opportunity to share with you, you can also try their own.

7 food references

So that’s the storyExclusive consumption guide series | Android end SQLCipher attack and the newCompared to the previous article, this one will be more fromThe principle of,In actual combatLet’s talk about it from perspectiveAttack and prevent, including enterprise-level SQLCipher attack and defense cases. Now aboutSQliteThe last article in the series onSQliteSource code analysis.