Monday, March 28, 2011

SQLite LIKE statement

I ran across what turned out to be a simple issue, but it took me a while to finally catch my mistake, so I thought I should post it in case someone else comes across the same issue.

When using SQLite, you can search for words or phrases that are contained within your table. Let's say you have a SQLite database adapter class (which you should in order to keep access to the database limited to one class in you application), you need to create a statement that will query the database using the LIKE statement.

Let's say in your database adapter class that your actual database variable is db. (very clever I know).
 You can create a method to query the database for a record in column 2 that is equal to a given string:

        public Cursor searchTable(String searchPhrase){
            return mDb.query(true, DATABASE_TABLE, new String[]{column1, column2,column3},  column2+ " LIKE '" + searchPhrase+"'", null, null, null, null, null);
        }

This method will return a cursor with every record where column2 is EQUAL to the searchPhrase. This is where I got stuck for a little while, because I want the records that CONTAIN the searchPhrase, regardless of what else is in the field. So you can use (%) as wildcards before and/or after the phrase. Also, don't forget to include a (') around the searchPhrase. SQLite uses the (') to tell the difference between a column name and a value. Since column2 is a column name, we don't need to throw those around it, but the searchPhrase is a value.. so forgetting those will cause some pain as well. So here's the way you search with the wildcards:

        public Cursor searchTable(String searchPhrase){
            return mDb.query(true, DATABASE_TABLE, new String[]{column1, column2,column3},  column2+ " LIKE '%" + searchPhrase+"%'", null, null, null, null, null);
        }

Now this will return a cursor that contains all the records where the searchPhrase is found in column2. So just create an instance of your database adapter, open your database, and use the instance of the adapter to return this cursor. Hope that helps someone else out there! 

1 comment:

  1. If that searchPhrase is derived from User input (no doubt it is) then concatenating the WHERE clause like that yields a SQL injection vulnerability. You should actually do the following:

    public Cursor searchTable(String searchPhrase){
    return mDb.query(true, DATABASE_TABLE, new String[]{column1, column2,column3}, column2+ " LIKE '%?%'", new String[] {searchPhrase}, null, null, null, null);
    }

    ReplyDelete