Protecting against SQL Injection

If you are building a ContentProvider that exposes data to third parties, you will be executing queries on behalf of a caller who has passed you raw input in the form of a projection (String[]), selection (String), and sortOrder (String). With anything SQL, it is dangerous to take unsanitized inputs and insert them into a query statement.

If the arguments to ContentResolver.query(uri, projection, selection, selectionArgs, sortOrder) are passed straight to SQLiteDatabase.query(...), they are simply concatenated to form a statement like this:

SELECT <projection> FROM <table> WHERE (<selection>) ORDER BY <sortOrder>

Suppose an attacker wants to extract some valuable information from the database backing the ContentProvider, such as by reading the contents of the sqlite_master table. There's no direct way to query that table through ContentResolver since the FROM clause is normally determined inside the ContentProvider based on the URI argument. However, there are some attack vectors still available.

Malicious projections

projection = { "* from sqlite_master;--" };

Passing this projection will produce the following query:

SELECT * from sqlite_master;-- FROM <table>

The -- is a comment, causing SQLite to ignore the remainder of the line (and thus the rest of the query). Clearly this is bad; this query will literally dump the contents of sqlite_master, allowing an attacker to discover tables and columns that should remain private.

To protect against this, use ContentProviderQueryBuilder and ProjectionMap. These two tools are described in Implementing a ContentProvider, but here's a simplistic example:

ProjectionMap map = new ProjectionMap();
map.putAll(MyModel.PROPERTIES);
ContentProviderQueryBuilder builder = new ContentProviderQueryBuilder()
        .setStrict(true)
        .setProjectionMap(map)
        .setDataSource(MyModel.TABLE);
Query query = builder.build(projection, selection, selectionArgs, sortOrder);

The ProjectionMap stores the properties that constitute a valid projection. In this case, all properties of the model called MyModel are present and mapped to their current names. (You can also map properties to other names as well.)

Because a ProjectionMap was provided to the ContentProviderQueryBuilder, the call to .build() will compare the caller's projection to the elements of the ProjectionMap. Any items not found in the ProjectionMap are either ignored or will throw an exception, depending on whether or not you have enabled "strict mode" in the ContentProviderQueryBuilder. In the code sample above, the malicious projection would cause an exception to be thrown.

Selections with strangeness

With projection-based attacks defended against, the attacker might try something like the following:

projection = { "a", "b", "c" } // 3 valid column names
selection = "0) UNION SELECT type, name, sql FROM sqlite_master;--"
  OR
projection = { "a", "b", "c" } // 3 valid column names
selection = "0) UNION SELECT type, name, sql FROM sqlite_master WHERE (1"
  OR
projection = { "a", "b", "c" } // 3 valid column names
selection = "0)/*"
sortOrder = "*/UNION SELECT type, name, sql FROM sqlite_master"

All three cases include premature completion of the WHERE clause (by using an unmatched closing parenthesis )), a UNION query on sqlite_master, and some trick to comment out or work around other syntax that is normally built into the statement. Here's how each would compile:

SELECT a, b, c FROM <table> WHERE (0) UNION SELECT type, name, sql FROM sqlite_master;--)
SELECT a, b, c FROM <table> WHERE (0) UNION SELECT type, name, sql FROM sqlite_master WHERE (1)
SELECT a, b, c FROM <table> WHERE (0)/*) ORDER BY */UNION SELECT type, name, sql FROM sqlite_master

Rather than try to check the selection string for anomalies ourselves, we take another approach: Compile the query twice, once with an additional set of parentheses around the WHERE clause and once without. If you construct each of those statements again with the additional parentheses, you'll find they no longer compile and SQLite will throw an error. Compensating for the number of parentheses in either compilation makes the other fail.

If you use ContentProviderQueryBuilder with strict mode enabled, and then use a SquidDatabase to execute the query, this is exactly what happens. The presence of any raw selection from the caller will cause this additional compilation step when the query is executed.

In summary, to protect yourself from SQL injection attacks inside your ContentProvider, you should use ContentProviderQueryBuilder and always

• provide a ProjectionMap
• call setStrict(true)
• execute the query using a SquidDatabase instance