sql - Queries with prepared statements in Android?

Question

In Android, android.database.sqlite.SQLiteStatement allows me to use prepared statements in SQLite to avoid injection attacks. Its execute method is suitable for create/update/delete operations, but there does not seem to be any method for queries that returns a cursor or the like.

Now in iOS I can create prepared statements of type sqlite3_stmt* and use them for queries, so I know this is not a limitation of SQLite. How can I perform queries with prepared statements in Android?

Answer 1

a prepared statement allows you to do two things

• speed up the performance since the database does not need to parse the statement each time
• bind & escape arguments in the statement so you are save against injection attacks

I don't know exactly where/when Androids SQLite implementation actually uses sqlite3_prepare (afiak not sqlite3_prepare_v2 - see here) but it does use it otherwise you could not get Reached MAX size for compiled-sql statement cache errors.

So if you want to query the database you have to rely on the implementation there is no way I know of to do it with SQLiteStatement.

Regarding the injection safety, every database query, insert, etc method has (sometimes alternative) versions that allow you to bind arguments.

E.g. if you want to get a Cursor out of

SELECT * FROM table WHERE column1='value1' OR column2='value2'

---

Cursor SQLiteDatabase#rawQuery(

• String sql, : full SELECT statment which can include ? everywhere
• String[] selectionArgs : list of values that replace ?, in order they appear

)

Cursor c1 = db.rawQuery(
    "SELECT * FROM table WHERE column1=? OR column2=?",
    new String[] {"value1", "value2"}
);

---

Cursor SQLiteDatabase#query (

• String table, : table name, can include JOIN etc
• String[] columns, : list of the columns required, null = *
• String selection, : WHERE clause withouth WHERE can / should include ?
• String[] selectionArgs, : list of values that replace ?, in order they appear
• String groupBy, : GROUP BY clause w/o GROUP BY
• String having, : HAVING clause w/o HAVING
• String orderBy : ORDER BY clause w/o ORDER BY

)

Cursor c2 = db.query("table", null, 
     "column1=? OR column2=?", 
      new String[] {"value1", "value2"},
      null, null, null);

---

Via ContentProviders - that case is slightly different since you interact with an abstract provider, not a database. There is acutally no guarantee that there is a sqlite database backing the ContentProvider. So unless you know what columns there are / how the provider works internally you should stick to what the documentation says.

Cursor ContentResolver#query(

• Uri uri, : an URI representing the data source (internally translated to a table)
• String[] projection, : list of the columns required, null = *
• String selection, : WHERE clause withouth WHERE can / should include ?
• String[] selectionArgs, : list of values that replace ?, in order they appear
• String sortOrder : ORDER BY clause w/o ORDER BY

)

Cursor c3 = getContentResolver().query(
     Uri.parse("content://provider/table"), null,
     "column=? OR column2=?", 
      new String[] {"value1", "value2"},
      null);

Hint: if you want to LIMIT here you can add it to the ORDER BY clause:

String sortOrder = "somecolumn LIMIT 5";

or depending on the implementation of the ContentProvider add it as a parameter to the Uri:

Uri.parse("content://provider/table?limit=5");
// or better via buildUpon()
Uri audio = MediaStore.Audio.Media.EXTERNAL_CONTENT_URI;
audio.buildUpon().appendQueryParameter("limit", "5");

---

In all cases ? will be replaced by the escaped version of what you put in the bind argument.

? + "hack'me" = 'hack''me'