SQLite : verification if value exist

Question

Basicly, i want to verify if a usename is not already written in the Database. Since i'm pretty new, i don't know how to do and tried that, but i'm getting error with this codes. I seriously don't know if there is a better way to do this, and if ther is, please tell me, thank you.

   private long addEntry(String strUsename, String strName,
        String strFirstName, String strPassword) {
    // TODO Auto-generated method stub
    ContentValues cv = new ContentValues();
    cv.put(KEY_USERNAME, strUsename);
    cv.put(KEY_FIRSTNAME, strFirstName);
    cv.put(KEY_NAME, strName);      
    cv.put(KEY_PASSWORD, strPassword);
    return accountDB.insert(DATABASE_TABLE, null, cv);
}

public String Verification_add(String ver_username, String strName,
        String strFirstName, String strPassword){
    String result = ""; 
    String[] columns = {KEY_ROWID, KEY_USERNAME, KEY_NAME, KEY_FIRSTNAME, KEY_PASSWORD}; 
    Cursor c = accountDB.query(DATABASE_TABLE, columns, KEY_USERNAME + "=" + ver_username, null, null, null, null);
    if (!c.moveToFirst()){ // IF IT'S FALSE, that mean the username is not taken, ritgh ? 
        addEntry(ver_username, strName, strFirstName, strPassword);
        c.close(); 
        return result = "Account[...] code: 1 !"; 
    }else{
        c.close(); 
        return result = "Username already [...] code:2 "; 
    } 

}

Here is the logcat report :

02-24 23:09:32.201: E/Verification_add(1389): MY ERROR !! 
02-24 23:09:32.201: E/Verification_add(1389): java.lang.NullPointerException
02-24 23:09:32.201: E/Verification_add(1389):   at com.example.communication.DataBaseRegistration.Verification_add(DataBaseRegistration.java:79)
02-24 23:09:32.201: E/Verification_add(1389):   at com.example.communication.Registration.onClick(Registration.java:42)
02-24 23:09:32.201: E/Verification_add(1389):   at android.view.View.performClick(View.java:4202)
02-24 23:09:32.201: E/Verification_add(1389):   at android.view.View$PerformClick.run(View.java:17340)
02-24 23:09:32.201: E/Verification_add(1389):   at android.os.Handler.handleCallback(Handler.java:725)
02-24 23:09:32.201: E/Verification_add(1389):   at android.os.Handler.dispatchMessage(Handler.java:92)
02-24 23:09:32.201: E/Verification_add(1389):   at android.os.Looper.loop(Looper.java:137)
02-24 23:09:32.201: E/Verification_add(1389):   at android.app.ActivityThread.main(ActivityThread.java:5039)
02-24 23:09:32.201: E/Verification_add(1389):   at java.lang.reflect.Method.invokeNative(Native Method)
02-24 23:09:32.201: E/Verification_add(1389):   at java.lang.reflect.Method.invoke(Method.java:511)
02-24 23:09:32.201: E/Verification_add(1389):   at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
02-24 23:09:32.201: E/Verification_add(1389):   at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
02-24 23:09:32.201: E/Verification_add(1389):   at dalvik.system.NativeStart.main(Native Method)

Thank you !

Where exactly is line 79 (the location of the null pointer exception) in your code? — ebarrenechea, Feb 24 '13 at 23:33
c may be null. Please look at http://stackoverflow.com/questions/5457699/cursor-adapter-and-sqlite-example — Chetter Hummin, Feb 24 '13 at 23:33
The line 79 is : Cursor c = accountDB.query(DATABASE_TABLE, columns, KEY_USERNAME + "=" + ver_username, null, null, null, null); — R00t, Feb 24 '13 at 23:37
Does `accountDB` have a value? (Also, don't use user-supplied values in SQL without checking; a user could crash your app by using a quote or apostrophe in the user-name.) — 323go, Feb 25 '13 at 00:18
@pst, I'm talking about patterns, not implementation details. — 323go, Feb 25 '13 at 03:24
@323go I am talking about *correct patterns* for accessing SQL from code. Specific code would be an "implementation detail". The use of "checking [to prevent a] crash by using a quote" shows exactly why this "checking" is not a good pattern to use. Granted, checking user-input is good for many reasons, such as *ensuring correct information*, but it is not related to using values at a *data level*, which is the problem/issue using placeholders entirely prevents. — , Feb 25 '13 at 05:05
Sorry, @pst, you're tripping yourself up over semantics. "Checking parameters" is a pattern. Doing so by using placeholders is an implementation detail. — 323go, Feb 25 '13 at 13:40
@323go Yeah, it has a value(well, at least, it should) : return accountDB.insert(DATABASE_TABLE, null, cv); And thanks for verifications, i know what a 'bad guy' could do with SQLinjection, i guess it's same here. — R00t, Feb 25 '13 at 13:42
"It should" isn't sufficient. DOES it? Place a breakpoint on the line and inspect the member. — 323go, Feb 25 '13 at 13:43
@323go There is *no checking* when using placeholders - this is the power and beauty of placeholders. There is an important difference. Perhaps [checking](http://www.thefreedictionary.com/checking) is not the appropriate word. — , Feb 25 '13 at 17:54
@R00t The use of `KEY_USERNAME + "=" + ver_username` is vulnerable to SQL injection. The correct solution to avoid all SQL injection is to use placeholders. If using placeholders, no "checks" must be done, because it doesn't matter. Safe pragmatic coding standards are to uniformly use placeholders to ensure basic data vulnerabilities are eliminated: that is, there is no need to concern oneself which what *data* is in `ver_username`, but merely what *information* it contains which is a different issue entirely .. — , Feb 25 '13 at 17:58
@R00t Anyway, my money would be on `accountDB` being null, as no other possibly null value is used in that method as a reciever, but the best way to check this is to just attach the debugger, break on exceptions, and inspect the values, as told. — , Feb 25 '13 at 18:03
@R00t A NPE is only *normally* caused by `receiver.FieldOrMethod`, where `receiver` evaluates to null. It is possible to errantly throw an NPE, but I have still yet to see such egregious (in the bad sense) code. — , Feb 25 '13 at 18:06
Please do a Log of `accountDB` before that line 79 and show us the result. — m0skit0, Aug 16 '13 at 14:31

score 0 · Answer 1 · answered Aug 16 '13 at 14:25

I'm guessing you are doing this check so you wont add the same name twice or more: Use this method: PS: you can just call updateRecord(String name) if you just want to know if the name is in the DB.

public long insertRecord() {

    ContentValues cv = new ContentValues();
    //put values

    if(!updateRecord(cv, name))
        return db.insert(P_TABLE, null, cv);
    else return 0;
}

private boolean updateRecord(ContentValues values, String name) {
    return db.update(P_TABLE, values, KEY_NAME + "=" + name, null) > 0;
}

SQLite : verification if value exist

1 Answers1