How to Use Prepared Statements in Sqlite in Android

How do I use prepared statements in SQlite in Android?

I use prepared statements in Android all the time, it's quite simple:

SQLiteDatabase db = dbHelper.getWritableDatabase();
SQLiteStatement stmt = db.compileStatement("INSERT INTO Country (code) VALUES (?)");
stmt.bindString(1, "US");
stmt.executeInsert();

How to use prepared statements with Android sqlite?

Yes, insert() uses prepared statements under the hood: sqlite_prepare_v2() to compile the SQL and then sqlite3_bind_*() to bind the arguments from ContentValues. This prevents the usual SQL injection attacks.

To use prepared statements for performance i.e. do the SQL compilation only once and reuse the same prepared statement for multiple queries with different bind args, see SQLiteDatabase.compileStatement() and SQLiteStatement.

Queries with prepared statements in Android?

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'

How to create a prepared SELECT statement in SQLite on Android platform

SQLiteDatabase and SQLiteQueryBuilder always create a temporary statement.

SQLiteStatement works only for queries that return a single value.

The Android database API does not have any mechanism to create a prepared statement for a query.

(Compiling a statement is extremely fast in SQLite, but that cannot be the reason because prepared statements are supported for other statement types.)

Does SQLiteDatabase.query uses PreparedStatements ?

The recommendation to use prepared statements comes from PHP, where some old API did not allow SQL parameters (?) except when using a prepared statement.

But parameters and prepared statements are orthogonal concepts. To prevent injections (and avoid string/blob formatting problems), you need only parameters. (And query() gives you that.)

A prepared statement is useful only when you want to execute it multiple times.

(As it happens, query() indeed uses a prepared statement internally, but this is a consequence of the SQLite API, and is of no concern to you.)

How do I use a prepared statement in Spatialite Android?

There are a few tricks. They all use the exec() call, which has 3 arguments for this version. The statement from the source code is:

public void exec(String sql, jsqlite.Callback cb, String args[])

A jsqlite.Callback is an interface, of which there can be several. But the best way seems to be using a db.get_table(query,args) function. %q is the effective replacement for ? in the Android SQLite representation. Here's the transformation of the given code:

String query = "SELECT AsText(Transform(MakePoint(%q, %q, 4326), 32632));";
TableResult result=db.get_table(query,new String[]{""+TEST_LONG,""+TEST_LAT});

From there, you just have to get the results from TableResult. There isn't a method call to get the results from here, you actually have to grab the publicly declared variable and manually parse through it. Here's an example of how that can be done.

TableResult result=db.get_table(query,new String[]{""+lng,""+lat});
Vector<String[]> rows=result.rows;
for (String[] row:rows)
{
    for (String val:row)
    {
        Log.v(TAG,val);
    }
}

If you aren't doing a select, try something like this:

TableResult result=new TableResult();
db.exec("ATTACH DATABASE %q AS newDb",result,new String[]{path});

I assume the same pattern will work for INSERTS and the like

Getting raw SQL query after a prepared statement is built on android

It's not possible. The ? values are not bound at the SQL level but deeper, and there's no "result" SQL after binding the values.

Variable binding is a part of the sqlite3 C API, and the Android SQLite APIs just provide a thin wrapper on top. http://sqlite.org/c3ref/bind_blob.html

For debugging purposes you can log your SQL with the ?, and log the values of your bind arguments.