SQLite rawQuery selectionArgs and Integers Fields

Question

As the Android documents says, the selectionArgs parameters of the rawQuery method is parsed as strings.

SQLiteDatabase.rawQuery(String sql, String[] selectionArgs)

selectionArgs: You may include ?s in where clause in the query, which will be replaced by the values from selectionArgs. The values will be bound as Strings.

But today, I was faced with a problem that took a great part of my day. Imagine the following query:

SELECT * FROM TABLE_A WHERE IFNULL(COLUMN_A, 0) >= 15

COLUMN_A is INTEGER. The table has about 10 rows that attends to that criteria. Running the query on a database editor, the result was always correct, but, on the smartphone, the statement always returned no rows.

After some time, a changed the query to:

SELECT * FROM TABLE_A WHERE IFNULL(COLUMN_A, 0) >= '15'

and the editor returned no rows, just like Android. So, changing the query to:

SELECT * FROM TABLE_A WHERE CAST(IFNULL(COLUMN_A, 0) as INTEGER) >= '15'

solved the problem. Another test that was done was:

SELECT * FROM TABLE_A WHERE COLUMN_A >= '15'

also, returned the correct result.

This appears to be a problem that involves the way that Android bounds the parameters to the query (as strings) with the IFNULL clause.

So, does anybody knows why this happened? Are there any suggestions to solve this without using CAST on the query?

Answer 1

The reason why you bind your values to the query is to prevent an SQL-injection attack.

Basically, you send your query (including placeholders) to your database and say "My next query is going to be of this form and none other!". When an attacker then injects a different query-string (through a form-field, for example) the database says "Hey, that's not the query you said you would send!" and throws an error at you.

Since commands (which can be injected into your actual query as show in the linked article) are strings, the string-datatype is the more "dangerous" one. If a user tries to inject some code into your field which should only take numbers and you try to cast/parse the input to an integer (before putting the value into your query), you'll get an exception right away. With a string, there is no such kind security. Therefor, they have to be escaped probably. That might be the reason that the bind values are all interpreted as strings.

The above is bogus! It doesn't matter if the arguments you bind are strings or integers, they're all equally dangerous. Also, pre-checking your values in-code results in a lot of boilerplate code, is error-prone and unflexible!

---

To prevent your application from SQL-Injections and also speed up multiple database writing operations (using the same query with different values), you should use "prepared statements". The correct class for writing to the database in the Android SDK is SQLiteStatement.

To create a prepared statement, you use the compileStatement()-method of your SQLiteDatabase-object and bind the corresponding values (which will be replaced with the ?-marks in your query) using the correct bindXX()-method (which are inherited from SQLiteProgram):

SQLiteDatabase db = dbHelper.getWritableDatabase();
SQLiteStatement stmt = db.compileStatement("INSERT INTO SomeTable (name, age) values (?,?)");
// Careful! The index begins at 1, not 0 !!
stmt.bindString(1, "Jon");
stmt.bindLong(2, 48L);
stmt.execute();
// Also important! Clean up after yourself.
stmt.close();

Example taken from this older question: How do I use prepared statements in SQlite in Android?

---

Sadly, SQLiteStatement does not have an overload that returns a Cursor, so you can't use it for SELECT-statements. For those, you can use the rawQuery(String, String[])-method of SQLiteDatabase:

int number = getNumberFromUser();
String[] arguments = new String[]{String.valueOf(number)};
db.rawQuery("SELECT * FROM TABLE_A WHERE IFNULL(COLUMN_A, 0) >= ?", arguments);

Note that the rawQuery()-method takes a String-array for the argument values. This actually doesn't matter, SQLite will automatically convert to the correct type. As long as the string-representations equal what you would expect in an SQL query, you're fine.

Answer 2

I have the exact same problem I guess. The thing that you are trying to mention is that you can't make actual calculations regarding sqlite database. I have found out that the problem is bigger than the one that you mention. SQLite Database does not seem to understand any field types regarding the field value. Meaning that if you are trying to insert a String value in an INTEGER field type the insertion would not complain.

So the problem is even bigger as you can see. Although I have seen that if you have a column that has only Integers and you make a where statement like: where id = 1 without ' ' then there is a result dataset. So I might ask you if you are sure that this statement does not work: "SELECT * FROM TABLE_A WHERE IFNULL(COLUMN_A, 0) >= 15". But the where id >= '15' does work because it takes the string representation of id which is actual 2 unicode characters(!!!) and tries to make an operator >= to '15' which DOES apply.

The first time I came across these issues surprised me and I have decided to dynamically create the SQL without binding parameters and executing as a whole String. I know it isn't the best way to access the database, without binding parameters, but it is a solution and a good one because security reasons are not that important though your database is secured and your methods of accessing it are private to your Application. If the "intruder" has the ability to access the database by a root phone he could just put it in an SQLITE Studio and he is done.