SQLite rawQuery selectionArgs and integer fields

Question

As stated in the Android docs, the selectionArgs of the rawQuery method are parsed as strings.

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

selectionArgs: can you enable? s in the where clause in the query, which will be replaced by the values ​​from selectionArgs. Values ​​will be bound as strings.

But today I ran into a problem that took up most of my time. Submit the following request:

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

COLUMN_A - INTEGER. The table contains about 10 rows that meet these criteria. When executing a query in the database editor, the result was always correct, but on a smartphone the instruction always returned rows.

After some time I changed the request to:

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

and the editor did not return rows like Android. So, changing the request to:

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

solved a problem. Another test that was done was:

 SELECT * FROM TABLE_A WHERE COLUMN_A >= '15' 

also returned the correct result.

This is apparently a problem with Android binding parameters to the request (like strings) with an IFNULL clause.

So, does anyone know why this happened? Are there any suggestions for solving it without using CAST in the request?

Answer 1

The reason you bind your values ​​to the query is to prevent SQL injection attacks .

Essentially, you submit your request (including placeholders) to your database and say: "My next request will be this form and no one else!". When an attacker enters another query line (for example, through a form field), the database says: "Hey, this is not the request that you said you will send!" and gives you an error.

Since the commands (which can be entered into your actual query, as shown in the related article) are strings, the string-datatype type is more "dangerous". If the user tries to insert some code in your field that should only accept numbers, and you try to bring / analyze the input data to an integer (before placing the value in your query), you will immediately get an exception. There is no such good security with the string. Therefore, they probably need to escape. This may be the reason that all binding values ​​are interpreted as strings.

The above is fake ! It doesn't matter if the arguments you bind are strings or integers, all of them are equally dangerous. In addition, pre-checking your values ​​in the code leads to a lot of boilerplate code, error prone and inflexible!

---

To prevent the application from using SQL injection, as well as to speed up several write operations to the database (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 appropriate values ​​(which will be replaced with ? -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(); 

An example is taken from this old question: how to use prepared statements in SQlite in Android?

---

Unfortunately, SQLiteStatement does not have an overload that returns Cursor , so you cannot use it for SELECT -statements. For them you can use rawQuery(String, String[]) -method 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 rawQuery() -method accepts a String array for argument values. It really doesn't matter, SQLite automatically converts to the correct type. As long as the string representations match what you expect in the SQL query, everything is fine.

Answer 2

I have exactly the same problem. The fact is that you cannot do the actual calculations in the sqlite database. I found out that the problem is bigger than the one you mention. The SQLite database does not seem to understand the field types in relation to the field value. This means that if you try to insert a String value into a field of type INTEGER, the insert will not complain.

Thus, the problem is even greater, as you can see. Although I saw that if you have a column that has only integers, and you do a where statement, for example: where id = 1 without '', then there is a result data set. Therefore, I can ask you if you are sure that this statement does not work: "SELECT * FROM TABLE_A WHERE IFNULL (COLUMN_A, 0)> = 15". But where id> = '15' works because it takes a string representation of id, which is the actual 2 Unicode characters (!!!) and tries to make the>> operator up to '15', which applies.

The first time I came across these problems, I was surprised, and I decided to dynamically create SQL without binding parameters and execute a whole String. I know that this is not the best way to access the database without binding parameters, but this is a good solution, because the security reasons are not so important, although your database is protected and your access methods are confidential for your application. an "attacker" has the ability to access the database using the root phone, he can just put it in SQLITE Studio, and he is done.