Sqlite verify if value of column exists

Question

i'm wondering if this method is right to verify if the value of _username already exists in the column "username"

public boolean verification(String _username) throws SQLException{
    Cursor c = dataBase.rawQuery("SELECT * FROM "+TABLE_NAME+" WHERE "+KEY_USERNAME+"="+_username, null);
    if (c!=null)
        return true; // return true if the value of _username already exists
    return false; // Return false if _username doesn't match with any value of the columns "Username"
}

Is there a better way to do the same thing, i'm really not sure about this, it seemed right for me. Thanks.

Sam · Answer 1 · 2013-03-23T16:19:44.607

6

Beware of SQL injection attacks! You should always use a parameterized query:

Cursor c = dataBase.rawQuery("SELECT 1 FROM "+TABLE_NAME+" WHERE "+KEY_USERNAME+"=?", new String[] {_username});

(Honestly I'm not sure how your first query didn't throw an exception since you forgot to wrap the string in quotes...)

Also rawQuery() will always return a Cursor, you must check if the Cursor is empty, not null.

As for "the best" approach, this works fine, but I recommend closing the Cursor to free up resources. All together:

public boolean verification(String _username) {
    Cursor c = dataBase.rawQuery("SELECT 1 FROM "+TABLE_NAME+" WHERE "+KEY_USERNAME+"=?", new String[] {_username});
    boolean exists = c.moveToFirst();
    c.close();
    return exists;
}

edited Mar 23 '13 at 16:19

answered Mar 23 '13 at 16:13

Sam

86,580
20
181
179

@Sajmon : answer quality is important instead of answering any question first – ρяσѕρєя K Mar 23 '13 at 16:28
@ρяσѕρєяK you right man i added to previous comment smiles :)) – Simon Dorociak Mar 23 '13 at 16:30
Thank you ! I thought rawQuerry would return 'null', but it works when i verified if the cursor is empty. Sorry i can't mark this comment as the answer even if this is the answer, i marked the first one that i saw. But you were really helpful, thank you ! – R00t Mar 23 '13 at 16:44

Simon Dorociak · Accepted Answer · 2013-03-23T16:19:47.087

2

Is there a better way to do the same thing, i'm really not sure about this, it seemed right for me. Thanks.

In the terms of security and purity yes, for sure.

public boolean verification(String _username) throws SQLException {
    int count = -1;
    Cursor c = null; 
    try {
       String query = "SELECT COUNT(*) FROM " 
                   + TABLE_NAME + " WHERE " + KEY_USERNAME + " = ?"
       c = dataBase.rawQuery(query, new String[] {_username});
       if (c.moveToFirst()) {
          count = c.getInt(0);
       }
       return count > 0;
    }
    finally {
       if (c != null) {
          c.close();
       }
    }
}

I recommend you to an usage of ? that is called placeholder. Each placeholder will be replaced with value from string array in the same order. This is called also parametrized statement as a defence agains SQL injection. When your work with Cursor is finished, release it.

edited Mar 23 '13 at 16:19

answered Mar 23 '13 at 16:14

Simon Dorociak

33,374
10
68
106

The notion that [`finally` trumps `return`](http://stackoverflow.com/q/65035/1267661) doesn't come naturally to me. – Sam Mar 23 '13 at 16:30
@Sam sam, i like using try-catch block, finally block will be applied always and it has many advantages. I know that i don't catching anything but user can for example in try block perform division by zero and problem is here... – Simon Dorociak Mar 23 '13 at 16:35
Thank you ! I did that, but instead of return count>0; i did count=>0; Did you just forget it ? if not, can you please explain me why count ==0 must return false ? c.getInt(0) can return 0 no ? Thank you anyway, your answer was really helpful – R00t Mar 23 '13 at 16:40
@R00t if your username exists in db so count is 1. if not, count is 0. so verification will be successful if count > 0(so if count is 1) it returns true. – Simon Dorociak Mar 23 '13 at 16:44
@Sajmon Ahh yes, i forgot that ID autoincrement begin with the value 1 . Thanks dude :) – R00t Mar 23 '13 at 16:56

Sqlite verify if value of column exists

2 Answers2

Linked