Android Sqlite selection args[] incorrect value?

Question

I have different variants of a query. All value types are Strings! (don't ask why...) The first one might look like this:

SELECT * FROM item WHERE arg1=false

The string ("sqlWhere") for the arg(s) looks like this: "arg1=?"

The string for the arg(s)-Array (args) at index 0 looks like this: "false"

This works fine. I've got 715 results.

Now, if i have something like this:

SELECT * FROM item WHERE (arg1=40 OR arg2=42) AND arg3=false

I'll expect 110 results for this. But there aren't any results, no Exception is thrown

If I'll generate a raw query in Firefox SQLite Manager:

SELECT COUNT(*) FROM item WHERE (arg1=40 OR arg2=42) AND arg3=false

I'll get the error: "no such column: false" If I generate a query like this:

SELECT COUNT(*) FROM item WHERE (arg1=40 OR arg2=42) AND arg3="false"

It'll not fail. BUT. I'm using selectionargs. I've read, that they should AVOID this situation. But they don't. Even, If I put into the selectionargs for arg3 = "\"+"false"+"\"" or arg3 = "\'+"false"+"\'" or even arg3="'"+"false"+"'" it won't work. Maybe an android specific problem? The value "false" comes from a list of values. It is initialized with an emty string.

Query-Method looks like this:

public List<Map<String,String>> getSearchResults(String sqlWhere, String[]args)
    {
        List<Map<String,String>> specs = new ArrayList<Map<String,String>>();
        String[] specnames = this.getSpecNames();

        Cursor cursor = mDb.query(dbSchema.SpecSchema.TABLE_NAME, specnames, sqlWhere, args, null, null, dbSchema.SpecSchema._ID + dbSchema.SORT_ASC);
        cursor.moveToFirst();
        while(!cursor.isAfterLast())
        {
            Map<String, String> temp = new HashMap<String, String>();
            for(int i = 0; i < specnames.length; i++)
                temp.put(specnames[i], cursor.getString(cursor.getColumnIndex(specnames[i])));
            specs.add(temp);

            cursor.moveToNext();
        }

        if(cursor != null)
            cursor.close();

        return specs;
    }

Do you call your method like `getSearchResults("(arg1=? OR arg2=?) AND arg3=?", new String[]{"40", "42", "false"});`? — biegleux, Jul 27 '12 at 12:52
@bieglux: I was terribly wrong. The queryWhere String and the args had a mismatch. queryWhere has another behavior. Adding only arg1 will have the desired result. Adding arg2 will fail. This is, if arg1 is added, the function call might look like this: `getSearchResults("arg1=?", new String[]{"false"});`. if i add arg2, the function call will be `getSearchResults("(arg1=?) AND arg2=?", new String[]{"false", "40"});` — wog, Jul 28 '12 at 00:03

score 3 · Answer 1 · answered Jul 27 '12 at 13:51

Use the SQLiteDatabase methods intead.

SELECT * FROM item WHERE (arg1=40 OR arg2=42) AND arg3=false

should be

SQLiteDatabase db;
String table = "item";
String projection = null;
String selection = "(arg1=? OR arg2=?) AND arg3=?";
String[] selectionArgs = new String[] { "40", "42", "false" };
String sortOrder = null;

db.query(item, projection, selection, selectionArgs, sortOrder);

It's not as concise, but it's much safer in that it'll protect against SQL injections and is less likely to cause problems (such as SQL syntax errors, etc.).

Please have a look at my question. I am using it. Data is validated BEFORE passing into getSearchResults(String sqlWhere, String[]args) — wog, Jul 27 '12 at 15:40

score 1 · Accepted Answer · edited May 23 '17 at 12:09

The documentation for SQLiteDatabase.query() says:

selectionArgs - You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.

When you execute this query:

SELECT * FROM item WHERE (arg1=40 OR arg2=42) AND arg3=false

and bind the arguments using the selectionArgs-parameter, the integers and the boolean will be bound as strings. This is not the same as in your database. I know you said all your columns are of type TEXT, but read the SQLite Documentation:

The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another.

That means, when you insert your integer values in your string-table, SQLite will convert those and store them as integers. The next important point is comparison:

SQLite may attempt to convert values between the storage classes INTEGER, REAL, and/or TEXT before performing a comparison. [...]

An expression that is a simple reference to a column value has the same affinity as the column.

The docs give the following example:

CREATE TABLE t1(
    a TEXT,      -- text affinity
    b NUMERIC,   -- numeric affinity
    c BLOB,      -- no affinity
    d            -- no affinity
);

-- Because column "a" has text affinity, numeric values on the
-- right-hand side of the comparisons are converted to text before
-- the comparison occurs.
SELECT a < 40,   a < 60,   a < 600 FROM t1;
0|1|1

In your case that means the following:

Your TEXT-table gets integers inserted, which are then converted (and stored) as INTEGERs.
When performing your comparison, you give in TEXT-values (which are your integers). Those should be (but don't need to be) converted to TEXT-values, because your table indicates it's values are of type TEXT.
You compare INTEGER to TEXT, which is not equal and therefor doesn't give you the correct results.

To insert different data-types (other then string) into the SQLiteDatabase, use Prepared Statements.

The Test Queries are send from the Firefox Extension "SQlite Manager" I have Strings (arg3 is VARCHAR(5)) in that Database AND I request Strings from the Database. I found the error, that arg1 and arg2 are Integers, but I pass Strings. Bizarrely if I create a Query with only arg1 and arg2 as Strings, it'll succeed. If I add arg3 as String, it'll fail. — wog, Jul 27 '12 at 15:34

score 0 · Answer 3 · answered Jul 27 '12 at 13:05

Ok, arg1 is the column name, it is not a variable name. So, when you do the first query, you are looking for all rows where the column named arg1=false. In all the subsequent queries you add column arg2 and then column arg3, are there columns arg2 and arg3? Can you post the code you used to create the table?

Android Sqlite selection args[] incorrect value?

3 Answers3

Linked