CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Muhammad Faizan
Muhammad Faizan

Reputation: 2088

How to use wildcards in a SQLite query?

I am trying to fetch data which contains specific string but query is not working, following is method for fetching data.

public Cursor getSearch(String item) {
    SQLiteDatabase db = this.getReadableDatabase();
    String mQuery = "SELECT * FROM hadiths WHERE text LIKE %"+item.toString()+"%";
    Cursor cursor = db.rawQuery(mQuery, null);
    return cursor;
}

Logcat shows following error.

Caused by: android.database.sqlite.SQLiteException: near "%": syntax error (code 1): , while compiling: SELECT * FROM hadiths WHERE text LIKE %fast%

I know that the wildcard %% and string variable item is causing issue but how do I use string variable along with wildcard?

Upvotes: 5

Views: 12955

Answers (1)

Barns
Barns

Reputation: 4848

Edit:

As mentioned below by Jiří, parameters should be used to help prevent SQL injection issues.

In order to add parameters you could do something similar to this:

String mQuery = "SELECT * FROM hadiths WHERE text LIKE ?”;
String param1 = “%” + item + ”%”;

Cursor cursor = db.rawQuery(mQuery, new String [] {param1});

In order to add another parameter:

String mQuery = "SELECT * FROM hadiths WHERE text LIKE ? AND Name = ?”;
String param1 = “%” + item + ”%”;
String param2 = name;

Cursor cursor = db.rawQuery(mQuery, new String [] {param1, param2});

This code is a bit cumbersome, but it is to illustrate that the parameters must be added in the order in which they are should be added to the query.

see SQLite documentation: https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase

Original answer here for posterity. WARNING Dangerous SQL injection issue!

You need to add single quotes to the query.

String mQuery = "SELECT * FROM hadiths WHERE text LIKE '%"+item+"%'";

Take a look at the SQLite docs: https://www.tutorialspoint.com/sqlite/sqlite_like_clause.htm

Note: There is no need to use toString() since "item" is already of type String.

Upvotes: 8

Related Questions