Using Like And Limit In Sqlite Queries In Android

I'm writing a function for an Android app, that should get the first 8 entries (names of cities) of a database which are matching a string. This is my query: Cursor cursor =

Solution 1:

WARNING: You should NOT use string concatenation with the + operator to insert user input in a SQL query.This leaves your app open to a SQL injection attack. I cannot emphasize this enough. Mitigating this common security flaw should be a top priority for all database developers.

Instead, you should use the ? place holder syntax:

Stringquery="SELECT " + CITIES_NAME +
                    " FROM " + TABLE_CITIES +
                    " WHERE " + CITIES_NAME +
                    " LIKE ?" +
                    " LIMIT 8";
String[] args = {nameLetters + "%%"};
Cursorcursor= database.rawQuery(query, args);

Even if the database is small and only used for your individual app, it is best to make this syntax a habit. Then when you work on larger, more critical databases, you won't have to worry about this issue as much.

This also has the advantage that it quotes the input for you. You completely avoid the error which you encountered that prompted the original question.

Solution 2:

For the sake of completeness I'll turn my comment into an answer, to hopefully help anyone else who may have this issue.

Think you need quotes around the like string eg SELECT city_name FROM CITIES WHERE city_name LIKE 'berl%' LIMIT 8