Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 576
Muhammad Faizan Avatar asked May 03 '18 14:05

Muhammad Faizan


People also ask

Can you declare variables in SQLite?

SQLite doesn't support native variable syntax, but you can achieve virtually the same using an in-memory temp table.

Do wildcards take longer to run in SQL?

Queries with leading wildcards generally are slow because they are not sargable. If by chance they pick up an index, they will usually full scan the index. Below is one example of a typical query that you might see when using a leading wildcard.

How many wildcards are in SQL?

In SQL, there are only two defined wildcard characters: _ : When used as a wildcard, an underscore represents a single character. For example, s_mmy would match sammy , sbmmy , or sxmmy . % : The percentage sign wildcard represents zero or more characters.


1 Answers

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.

like image 133
Barns Avatar answered Sep 22 '22 05:09

Barns