CODEWITHSUNDEEP
javaandroidsqlsqlitesql-injection
TWiStErRob
TWiStErRob

Reputation: 46480

How to bind INTEGER/NULL to a ? placeholder with rawQuery selectionArgs in Android?

Anything can change below to reach a working solution! I have full control over all of the below schema/data/query/code, so any reasonable improvement is welcome: I'm looking for a simple/clean/to-the-point solution. For example making two different queries (= ? and is null) is the last resort.

Question

I want to change the below code so I can call listCategoriesIn(1) and listCategoriesIn(null) and they both give the correct expected result. I can't make listCategoriesIn(null) work with a WHERE clause like c.parent = ?.

Table

CREATE TABLE Category (
    _id         INTEGER      NOT NULL,
    name        VARCHAR      NOT NULL,
    parent      INTEGER          NULL --< important bit
        CONSTRAINT fk_Category_parent
            REFERENCES Category(_id)
            ON UPDATE CASCADE
            ON DELETE CASCADE,
    PRIMARY KEY(_id AUTOINCREMENT),
    UNIQUE(name)
);

Sample Data

INSERT INTO Category
                  (_id, parent, name)
          SELECT     0,   NULL, 'cat0'         --< expected for listCategoriesIn(null)
    UNION SELECT     1,   NULL, 'cat1'         --< expected for listCategoriesIn(null)
    UNION SELECT    11,      1,   'cat1-1'     --< expected for listCategoriesIn(1)
    UNION SELECT    12,      1,   'cat1-2'     --< expected for listCategoriesIn(1)
    UNION SELECT   121,     12,     'cat1-2-1'
    UNION SELECT   122,     12,     'cat1-2-2'
    UNION SELECT    13,      1,   'cat1-3'     --< expected for listCategoriesIn(1)
    UNION SELECT   131,     13,     'cat1-3-1'
    UNION SELECT     2,   NULL, 'cat2'         --< expected for listCategoriesIn(null)
    UNION SELECT    21,      2,   'cat2-1'
    UNION SELECT     3,   NULL, 'cat3'         --< expected for listCategoriesIn(null)
;

Query

IRL I'm using much more complex ones involving views, sub-queries, multiple JOINs.

select
    c.*,
    (select count() from Category where parent = c._id) as count
from Category c
where c.parent = ? --< important bit
;

Wrong Code #1

public Cursor listCategoriesIn(SQLiteDatabase db, Long categoryID) {
    // public Cursor SQLiteDatabse.rawQuery(String sql, String[] selectionArgs);
    return db.rawQuery(CATEGORY_QUERY, new String[] {
        String.valueOf(categoryID)
    });
}

listCategoriesIn(1): works OK

listCategoriesIn(null): the resulting Cursor is empty, possibly = 'null' or = NULL is bound.

Wrong Code #2

public Cursor listCategoriesIn(SQLiteDatabase db, Long categoryID) {
    // public Cursor SQLiteDatabse.rawQuery(String sql, String[] selectionArgs);
    return db.rawQuery(CATEGORY_QUERY, new String[] {
        categoryID == null? null : categoryID.toString()
    });
}

listCategoriesIn(1): works OK

listCategoriesIn(null): java.lang.IllegalArgumentException: the bind value at index 1 is null

Upvotes: 2

Views: 820

Answers (2)

TWiStErRob
TWiStErRob

Reputation: 46480

After sleeping on it and starting a new day I had an epiphany, it's possible without duplication of the query:

where c.parent = ? or (? = 'null' and c.parent is null)

However we need to duplicate the argument, here's the corresponding Java call:

public Cursor listCategoriesIn(SQLiteDatabase db, Long categoryID) {
    return db.rawQuery(CATEGORY_QUERY, new String[] {
        String.valueOf(categoryID), String.valueOf(categoryID)
    });
}

There's no NPE when categoryID is null, because it binds as "null" (see valueOf). Passing null->'null' to the driver activates the second part of the WHERE clause, and of course none of the INTEGER values will be = 'null' so the first part doesn't play.

This trick works because rawQuery binds everything as Strings and the SQLite engine can handle comparing INTEGERs with numbers in a TEXT. I'm sure the details can be found in the SQLite Datatypes documentation.

Upvotes: 1

hfann
hfann

Reputation: 599

I think you need to send a different SQL statement when a field is NULL.

    public Cursor listCategoriesIn(SQLiteDatabase db, Long categoryID) {
        if(categoryID == null)
            return db.rawQuery(CATEGORY_QUERY_NULL, null);
        else
            return db.rawQuery(CATEGORY_QUERY, new String[] { categoryID.toString() });
   }

where CATEGORY_QUERY_NULL is the same query as CATEGORY_QUERY but with c.parent IS NULL instead of c.parent = ?. See this answer for details.

Upvotes: 0

Related Questions