sqlite LIKE problem in android

Question

Hello i've spent almost 2 hours trying to figure out why the LIKE statement doesn't work and i only get this error: 03-03 11:31:01.770: ERROR/AndroidRuntime(11767): Caused by: android.database.sqlite.SQLiteException: bind or column index out of range: handle 0x89d9f8

In SQLiteManager it works perfectly like this: SELECT Word FROM Sign WHERE Word LIKE 'he%'; But when i try to do it from java it won't work. Here's is my query, i've tried in a lot of ways with no luck:

Cursor cursor = m_db.query(MY_TABLE, new String[] {"rowid","Word"},"Word"+" LIKE '"+" ?"+"%'", new String[]{name}, null, null, null);

Any ideas? i'm i doing it wrong or is there a bug?

Thanks for your time.

Jona · Answer 1 · 2011-10-05T22:06:04.950

61

The solution is actually very easy. Just include the % inside your selectionArgs.

String []selectionArgs = {name + "%"});

edited Oct 05 '11 at 22:06

answered Jul 13 '11 at 22:39

Jona

13,325
15
86
129

2

This works for me. This solution is better than the answer of Olsavage because it's not vulnerable to SQL injection. – Kowlown Sep 28 '13 at 21:53
@Kowlown, can you please tell me how could an sql be done in your Olsavage's method, because both seem identical to me – juztcode Aug 15 '20 at 15:47
@juztcode The answer has been edited with the right way to prevent SQL injection. The 2014 edit is good, and can be used. – Kowlown Aug 16 '20 at 22:28

Olsavage · Accepted Answer · 2014-03-12T13:29:59.570

37

I think you shouldn't use selArgs for LIKE such a way. You may try this:

Cursor cursor = m_db.query(MY_TABLE, new String[] {"rowid","Word"},"Word"+" LIKE '"+name+"%'", null, null, null, null);

EDIT:

OK, if you want be safe from SQL injections, don't use above solution, use this:

Cursor cursor = m_db.query(MY_TABLE, new String[] {"rowid","Word"},"Word LIKE '?'", new String[]{name+"%"}, null, null, null);

edited Mar 12 '14 at 13:29

answered Mar 03 '11 at 10:53

Olsavage

1,118
8
11

That's right, it worked! is there any explanation for why this is happening with the LIKE statement? Thanks for the answer and have a nice day. – madcoderz Mar 03 '11 at 11:32
2

I think, you just can't use ? with quotes, like you did "Word LIKE '?%'". – Olsavage Mar 03 '11 at 11:40
6

And what about SQL injection?? Jonas answer seems to be better. – Erveron Jan 24 '13 at 18:12
2

Please don't use this 'solution'. It is vulnerable to SQL Injection. See http://xkcd.com/327/ for a good example. – Mar 05 '13 at 19:03
Downvote because of SQL injection problem with this solution. Use @Jona solution – Pascal Mar 12 '14 at 10:36
1

Single quotes around `?` are not necessary. – mixel Nov 24 '15 at 22:24
in my case i removed quotes : `"Word LIKE ?"` – Mojtaba Haddadi Dec 15 '19 at 06:03
@Olsavage , can you please tell me how could an sql be done in your first method, because both seem identical to me – juztcode Aug 15 '20 at 15:46

score 7 · Answer 3 · answered Nov 07 '13 at 09:45

7

This is how I did:

String []columns = {"_id", "name"};
String []selectionArgs = {name+"%"};
db.query(true,"mydb",columns,"name LIKE ?",selectionArgs,null,null,null);

answered Nov 07 '13 at 09:45

Sarvar Nishonboyev

12,262
10
69
70

score 2 · Answer 4 · edited May 23 '17 at 12:25

2

Another -- perhaps cleaner -- solution is to use the || operator as described here: Sqlite binding within string literal

edited May 23 '17 at 12:25

Community

1
1

answered Feb 24 '12 at 22:13

Steve Pomeroy

10,071
6
34
37

sqlite LIKE problem in android

4 Answers4

Linked

Related