Search for a specific string in an SQLite database - Android SDK

Question

I'm using an SQLite database in Android SDK and I know this is a very simple question but I have the following function below. I'm trying to search for if a username is within the SQL database but I cant think of how I would structure the query.

What would be the proper way to search for the if the string "user" is within the table "ACCOUNT_TABLE"

public boolean isUser(String user){
        SQLiteDatabase db = this.getReadableDatabase();
        String queryString = "SELECT " + COLUMN_USERNAME + " FROM " + ACCOUNT_TABLE + " WHERE " (???);

        db.close();
        /*if(found in database) 
            return true;
        else 
            return false;*/
    }

I am neither proficient with SQL lite, nor Java, but I noticed that you didn't specify a column in your "WHERE" clause. Also, I would strongly recommend to use prepared statement instead of raw SQL, to protect yourself against SQL injection. There seems to be a nice tutorial here : sqlitetutorial.net/sqlite-java/select — Random User
– Random User, Commented Nov 9, 2020 at 16:55
yeah I planned on reformatting it later using query parameters. I just didnt know how to get it for the time being. I'll check out the link tho, thank you! — Ovii
– Ovii, Commented Nov 9, 2020 at 17:14

forpas · Accepted Answer · 2020-11-09 18:08:27Z

1

The proper way to do this is with the rawQuery() method, which takes 2 arguments:

the sql SELECT statement with ? placeholders for the parameters that you want to pass
a String array containing all the values for the parameters of the sql statement, in the order they appear in the statement

rawQuery() returns a Cursor which you can check if it contains any rows and if it does this means that the user you search for exists in the table:

public boolean isUser(String user) {
    SQLiteDatabase db = this.getReadableDatabase();
    String queryString = "SELECT 1 FROM " + ACCOUNT_TABLE + " WHERE " + COLUMN_USERNAME + " = ?";
    Cursor c = db.rawQuery(queryString, new String[]{user});
    boolean result = c.getCount() > 0;
    c.close();
    db.close();
    return result;
}

answered Nov 9, 2020 at 18:08

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tobi Over a year ago

you shouldn't call db.close() this is bad for performance

lareb · Accepted Answer · 2020-11-09 17:24:39Z

0

Try this query:

String queryString = "SELECT " + COLUMN_USERNAME + " FROM " + ACCOUNT_TABLE + " WHERE COLUMN_USERNAME like ‘%" +users+”%’”;

Here COLUMN_USERNAME is your database column and Users is the string you want to search.

answered Nov 9, 2020 at 17:24

lareb

966 bronze badges

8 Comments

Tobi Over a year ago

This is dangerous as user "tobias" will also be returned when you search for "tobi"

Ovii Over a year ago

so would

String queryString = "SELECT " + COLUMN_USERNAME + " FROM " + ACCOUNT_TABLE + " WHERE " + COLUMN_USERNAME + " = %" + user +"%";

be correct right?

Tobi Over a year ago

No, the %tobi% will make tobi, argtobi and tobias return true

Tobi Over a year ago

Just =user as in my answer

lareb Over a year ago

Use “like” operator instead of “=“

|

Collectives™ on Stack Overflow

Search for a specific string in an SQLite database - Android SDK

2 Answers 2

1 Comment

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related