Android sqlite raw query binding

Question

I have problem with binding parameters in raw query where values could be null. So I have next prepared statement:

String ss="SELECT * FROM Table1 WHERE ifnull(column1,-1) = ? ";

And if I execute it like I have to, I don't get any result

cursor = database.rawQuery(ss, new String[]{Long.toString(columnID)});

but if I replace question mark manually like this:

ss=ss.substring(0,ss.indexOf('?'))+Long.toString(columnID)+ss.substring(ss.indexOf('?')+1);
cursor = database.rawQuery(ss, null);

I get correct result, does any one now what is the reason for this?

Try removing the space after the question mark on ss. Sometimes SQLite is very sensible about those things. — Carlos J, Dec 03 '14 at 16:15
Please see my answer for this question: https://stackoverflow.com/questions/41092903/use-blob-field-as-query-parameter-in-sqlite/47908283#47908283 — awattar, Dec 20 '17 at 14:39

score 1 · Answer 1 · answered Dec 03 '14 at 16:37

1

The Android database API supports only strings as parameters, so your column1 values are not found unless they are strings.

Plain integers do not have formatting problems and cannot lead to SQL injections, so you can simply write them directly into the SQL query:

String ss = "SELECT * FROM Table1 WHERE ifnull(column1,-1) = " + columnID;
cursor = database.rawQuery(ss, null);

answered Dec 03 '14 at 16:37

CL.

yes, I know that but I convert long to string so that should work. If I ignore null values and I made select like this: String ss="SELECT * FROM Table1 WHERE column1 = ? "; binding works perfectly, but in that case null values are ignored and I don't get rows with null values at column1 from DB if I wanted – user2704821 Dec 05 '14 at 08:17
NULLs cannot be compared with `=`, but this is a different question. – CL. Dec 05 '14 at 09:09
yes I know that, but point of my previous comment was just that, that binding of Long converted to string work fine in second case but not in first case when I have in select statement ifnull(column1,-1) – user2704821 Dec 05 '14 at 19:43

1 Answers1