Android Security: SQL injection with the Room Persistence Library

During Google I/O 2017 the Room Persistence Library was announced. Room provides an abstraction layer over SQLite in a similar way to Retrofit with network requests.

SQL injection attacks are well documented on the web, indeed, OWASP rank injection as their number 1 security concern in 2017. SQLite built into Android is also susceptible to client-side SQL injection attacks, putting any data you store locally at risk.

As such, I thought it would be interesting to look at Room to see if it is possible to perform SQL injection attacks when using it. Surprisingly it is, so read on to find out more.

SQL injection on Android

The cause of SQL injection stems from SQL queries created dynamically or concatenated with user-supplied input which you execute using the query or rawQuery methods in SQLiteDatabase. OWASP provide some example SQL injection strings.

Standard use of Room

You use annotations such as @Query:

@Dao
public interface UserDao {
@Query("SELECT * FROM user WHERE first_name LIKE :first")
User findByFirstname(String first);

}

And Room generates the following code for you:

public User findByFirstname(String first) {
final String _sql = "SELECT * FROM user WHERE first_name LIKE ?";
final RoomSQLiteQuery _statement = RoomSQLiteQuery.acquire(_sql, 1);
int _argIndex = 1;
if (first == null) {
_statement.bindNull(_argIndex);
} else {
_statement.bindString(_argIndex, first);
}
final Cursor _cursor = __db.query(_statement);

You can see how the query string from the annotation has been turned into a parameterised query with ? in place of :first. Similarly, if your query uses an array then Room dynamically builds a parameterised query based on the size of the array. The bindXXXX methods insert the value into the query string and perform automatic escaping at execution time.

i.e. Room is NOT prone to SQL injection with standard use.

Misuse of Room

If you take a look at your database definition you will see that it extends RoomDatabase:

@Database(entities = {User.class}, version = 1)
public abstract class AppDatabase extends RoomDatabase {
public abstract UserDao userDao();
}

This inheritance exposes a few methods, including public Cursor query(String sql, String[] selectionArgs). In regular use, you would provide a parameterised query to the sql parameter and the parameters in selectionArgs. SQL injection is then possible as sql is an easily malformed raw string.

For instance, String sql = "select * from user where first_name = '" + first + "'"; would be prone as you perform the substitution yourself.

It seems a shame that Room as of v1.0.0-alpha1 requires your database class to be an abstract class and not an interface to hide the internals. You want to write @AppDatabase public interface AppDatabase, with the annotation processor outputting public class AppDatabase_Impl extends RoomDatabase implements AppDatabase.

Conclusions

SQL injection is possible with Room — however, it is only a risk if you do not use it as intended. Room makes it much easier to implement safe queries than the original SQLite with its use of data access objects, entities and interfaces.

#buildsecureapps