An unusual case of Client SQL Injection

It is widely known how dangerous using unsanitized data in SQL queries can be. Apart from not appending user’s provided data to an SQL query, another valid and secure alternative would be to use parametrized queries. One could think that just by using the Content Resolver provided by Android, SQL queries would be automatically protected, but that’s false if it’s used wrong. If arguments are directly concatenated to a Content Resolver‘s selection parameter, this action could lead to an SQL injection attack. This is due to the fact that the parameters of this method are simply appended to the SQL query.

The Content Resolver

But, before starting, what’s a Content Resolver? From the Android’s developer website, we can learn that: > The ContentResolver object communicates with the provider object, an instance of a class that implements ContentProvider. The provider object receives data requests from clients, performs the requested action, and returns the results. This object has methods that call identically-named methods in the provider object, an instance of one of the concrete subclasses of ContentProvider. The ContentResolver methods provide the basic “CRUD” (create, retrieve, update, and delete) functions of persistent storage. So, when an Activity needs to perform any data manipulation, it will create a ContetResover which will be in charge of calling the needed ContentProvider and the communication with the data storage will be established.

Apart from the basic functions mentioned above, the ContentResolver also offers the function query() which, as expected, will execute an SQL query with the information provided. This method has several overloads, but the relevant for this post is the following one:

query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder)

You may be wondering why. Keep reading and you’ll find it out.

SQL Injection

During one of our last source code reviews, the AppSec team bumped into a ContentResolver which was building an SQL query with data provided by the user. The interesting fact of this finding was that the unique parameter that the user was able to manipulate was the selection one. To test how this vulnerability could be exploited, we developed a Proof of Concept, the main code of which is the following:

  ContentResolver contentResolver = getApplicationContext().getContentResolver();
        return contentResolver.query(
                ContactsContract.Contacts.CONTENT_URI,
                new String[]{
                        ContactsContract.Contacts.DISPLAY_NAME_PRIMARY},
                ContactsContract.Contacts.DISPLAY_NAME_PRIMARY + " LIKE '%" + filter + "%' ",
                null,
                null
        );

What the above query does is that, given a name, it returns the list of contacts whose names contain the filter’s value. So, the query built by the ContentResolver would look like:

SELECT display_name FROM view_contacts WHERE ((1)) AND ((display_name LIKE '%Name%' ))

As you can already imagine, the filter is the value which can be manipulated by the user and which ends up inside the LIKE operator. Thus, the idea was to find a way to use this filter’s value to obtain data from other tables offered by the Android database. After some googling, we found a possible way to inject SQL commands. The solution was to use an UNION operator which would allow appending our custom query. Easy, right? We just had to craft a valid SQL query using the UNION operator.

After struggling with the filter’s value for a while, we noticed that the query built by the ContentResolver was slightly different depending on the information this filter had. The basic data one could think of providing as a filter’s value is:

Name')) union SELECT 1 FROM view_contacts WHERE ('1' LIKE '1'

But, an exception was returned as the query wasn’t valid:

SELECT display_name FROM view_contacts WHERE ((1)) AND ((display_name LIKE '%Name')) union SELECT 1 FROM view_contacts WHERE ('1' LIKE '1%' ))

It turned out that an opening parentheses was missing before the WHERE operator. So, our next step was to try adding another parenthesis, so the filter’s value was the following:

Name')) union SELECT 1 FROM view_contacts WHERE (('1' LIKE '1

We were almost sure that this would work but, to our surprise, the application crashed because, this time, a closing parentheses was missing at the end of the query:

SELECT display_name FROM view_contacts WHERE ((1)) AND (display_name LIKE '%Name')) union SELECT 1 FROM view_contacts WHERE (('1' LIKE '1%' )

SQLiteQueryBuilder strict property

As you can imagine, trying to find the right number of parentheses wasn’t the way to go. After a while we found a post in Yahoo’s squid repository that explained this same strange behaviour when something called strict mode was enabled. And what this strict mode is? Its main purpose is to make sure that the SQL query to be executed has a valid format and it does not contain any undesirable data (like SQL injections). But instead of checking the content of the query itself, it compiles the query twice: once with an additional parenthesis around the where clause and once without.

Thus, if the syntax of one of these queries is invalid because of an unbalanced parenthesis, the query is not executed. It turns out that, in Android, the class SQLiteQueryBuilder (used by the ContentResolver) has a very similar property called strict, which basically forces the same behaviour. And, as you have probably guessed, the ContentResolver class sets this to true in its SQLiteQueryBuilder.

After this discovery, we understood that trying to use a UNION operator wouldn’t allow us to perform an SQL injection attack, so we had run out of ideas.

The final solution

We were ready to give up, but we decided to do the last test, this time trying our luck performing a blind SQL injection. In this way we wouldn’t be using the UNION operator which was giving us headaches. So, the query we crafted was the following one:

Name' AND  (SELECT sqlite_version() FROM sqlite_master) LIKE '1

As can be seen, the main idea is to guess the value returned by the SELECT operator. In this case, we focused on obtaining the SQLite version. So, to get this information, we started testing with different numbers until the logical operation was satisfied, which told us that the number we were testing was the right one. So, as can be seen in the following snippet, every time we were guessing a number, we added a new value to our search until we got the whole SQLite version value.

Name' AND  (SELECT sqlite_version() FROM sqlite_master) LIKE '3.
Name' AND  (SELECT sqlite_version() FROM sqlite_master) LIKE '3.9
Name' AND  (SELECT sqlite_version() FROM sqlite_master) LIKE '3.9.2
Name' AND  (SELECT sqlite_version() FROM sqlite_master) LIKE '3.9.2$

And this is how we managed to retrieve information from tables we were not supposed to have access to. What’s more, this was achieved by only being able to manipulate the name of the person we wanted to look for in our mobile’s contacts list.

Conclusions

In this post we have seen a reason why user provided data can never be trusted, much less directly used in SQL queries. This time the injection attack wasn’t a direct one, as the used ContentResolver had enabled a property of SQLiteQueryBuilder, called strict that gave us a hard time trying to find a way to exploit the found vulnerability. This lead us to craft an SQL query which allowed us perform a blind SQL injection attack. It is true that the process used is not a quick one and it would be worth finding a way of automating it. Even though, we managed to retrieve information from an arbitrary table of our mobile’s database.

Therefore, if one of these tables had any sensitive information, such us encryption keys, these could be obtained by using this slow but effective process. Thanks for reading, and we hope you’ve enjoyed it!

Photo by Rami Al-zayat on Unsplash.