What is causing the errors in the following java code

Question

I have implemented a method to validate a user against an SQLite DB but the program keeps throwing an error when the login button is clicked the error codes follow

2019-04-15 21:38:27.455 4601-4601/com.example.mymedicare E/AndroidRuntime: FATAL EXCEPTION: main Process: com.example.mymedicare, PID: 4601 android.database.sqlite.SQLiteException: no such column: Brad (code 1): , while compiling: SELECT * FROM users WHERE Brad =? AND Brad1994 =? at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58) at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:37) at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44) at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1318) at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1257) at com.example.mymedicare.MyDBHelper.checkLogin(MyDBHelper.java:96)

I have tried reading the error log but can't find any serious errors my methods will be shown below

  // This is my check login method in mydbhelper   

   public boolean checkLogin(String username, String password) {
    SQLiteDatabase db = this.getWritableDatabase();

    String s;
    Cursor c = db.rawQuery("SELECT * FROM users WHERE " + username + " =? AND " + password + " =?", null);

    if (c.getCount() <= 0) {
        c.close();
        db.close();
        return false;
    } else {
        c.close();
        db.close();
        return true;
    }
}


   //This is LoggingIn activity where I have tried to implement the above 
   method 

   public class LoggingIn extends AppCompatActivity {

//defines page objects
private Button SignIn;
private EditText Usernames;
private EditText Passwords;
private MyDBHelper db;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_logging_in);

    //Finds objects by ID
     //edit text to string
    Usernames = (EditText) findViewById(R.id.Usernames);

    Passwords = (EditText) findViewById(R.id.Passwords);


    Button SignIn = (Button) findViewById(R.id.SignIn);
    db = new MyDBHelper(this);

    SignIn.setOnClickListener(new Button.OnClickListener() {
        @Override
        public void onClick(View v) {
            Boolean validUser = 
     db.checkLogin(Usernames.getText().toString(), 
     Passwords.getText().toString());

            if (validUser == true) {
                Intent i = new Intent(getApplicationContext(), 
                HomePage.class);
                startActivity(i);
            } else {
                Toast.makeText(getApplicationContext(), "Invalid login", 
                Toast.LENGTH_SHORT).show();
            }

        }
    });
}
}

The SQL you're constructing is bad. It's trying to execute SQL that literally says, `SELECT * FROM users WHERE Brad =? AND Brad1994 =?`, which isn't valid. Double-check how you're building the SQL. Your variables need to replace the question marks, not be inserted in place of the column names. — Jordan, Apr 15 '19 at 21:10
I assume your table has columns called `Brad` and `Brad1994`? — Joe C, Apr 15 '19 at 21:11
Hi no the table has username password, age, address etc. That error comes up when I try to log in brad and brad1994 are usernames and passwords respectively — Wine Dine VINE, Apr 15 '19 at 21:20

score 2 · Accepted Answer · answered Apr 15 '19 at 21:14

2

You are passing the parameters username and password as column names.
The correct way is:

Cursor c = db.rawQuery(
    "SELECT * FROM users WHERE usenamecolumn = ? AND passwordcolumn = ?", 
     new String[] {username, password});

Change usenamecolumn and passwordcolumn to your table's column names for username and password.

answered Apr 15 '19 at 21:14

forpas

160,666
10
38
76

Thanks I will try this when I get back and let you know the outcome ! – Wine Dine VINE Apr 15 '19 at 21:21
Thanks for all the input everybody really appreciate it! – Wine Dine VINE Apr 15 '19 at 22:12

score 0 · Answer 2 · answered Apr 15 '19 at 21:22

0

You probably want something closer to this:

    db.rawQuery("SELECT * FROM users WHERE username = ? AND password = ?", null);

In the way you've written it above you're attempting to perform a query using the search criteria (value) as the column name (key).

E.g. in plain English the first part of your original statement says "find something in column Brad" which is why your exception is popping up with "SQLiteException: no such column: Brad"

What you actually wanted to say was "find Brad in column username", which is what the revised statement I've added above should do for you (it's untested but on the right track to get you going).

I found this other StackOverflow post which might help in creating prepared statements as well -> How do I use prepared statements in SQlite in Android?

Hope this helps.

answered Apr 15 '19 at 21:22

mvee

263
2
15

This is close but every time I push login with this code it says invalid login regardless if the details are in the db or not not sure what to do next – Wine Dine VINE Apr 15 '19 at 22:02
Just looking at the docs (https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase), rawQuery has two constructors, one with a CancellationSignal object as its third parameter, which you've specified as "null" above, and one which does not have this third parameter. Have you tried removing the third constructor param from your rawQuery command? Also, try adding a break point at the line where you substantiate "Cursor c" and debugging the result of that, this might give you more to troubleshoot. – mvee Apr 20 '19 at 10:06

What is causing the errors in the following java code

2 Answers2