Android studio SQLiteOpenHelper select from * where... no such column error

Question

I'm trying to select password from a database where name = the name I sent in the parameters of the method. I'm writing all the names of the database so the name is 100% inside the database.

But I get the following error: android.database.sqlite.SQLiteException: no such column: kristofer (code 1): , while compiling: SELECT * FROM login WHERE _name = kristofer

public String databasePassword(String name){
        String dbString = "";
        SQLiteDatabase db = getWritableDatabase();
        String query = "SELECT * FROM " + TABLE_LOGIN + " WHERE "+COLUMN_NAME + " = " + name;
        //Cursor point to a location in your results
        Cursor c = db.rawQuery(query, null);
        //Move to the first row in your results
        c.moveToFirst();

        while(!c.isAfterLast()){
            if(c.getString(c.getColumnIndex("_password"))!=null){ //Loop through every row in a database
                dbString += c.getString(c.getColumnIndex("_password"));
                dbString += "\n";
            }
            c.moveToNext();
        }
        db.close();
        return dbString;
    }

sql's basics: use parameters(best solution), or use literals in right way(not a good solution) — Selvin, Feb 24 '17 at 10:02

score 2 · Accepted Answer · edited May 23 '17 at 12:25

2

Write your query as follows

"SELECT * FROM " + TABLE_LOGIN + " WHERE "+COLUMN_NAME + " = '" + name+"'";

you are missing single quotes around the name value. Best solution is going with parameters

edited May 23 '17 at 12:25

Community

1
1

answered Feb 24 '17 at 10:02

nobalG

4,544
3
34
72

score 2 · Answer 2 · answered Feb 24 '17 at 10:05

To avoid formatting problems like this (and SQL injection attacks), always use parameters for string values:

String query = "SELECT * FROM "+TABLE_LOGIN+" WHERE "+COLUMN_NAME+" = ?";
Cursor c = db.rawQuery(query, new String[]{ name });

And if you want to read only a single value from the database, use a helper function that manages the cursor for you:

public String databasePassword(String Name) {
    SQLiteDatabase db = getWritableDatabase();
    try {
        return DatabaseUtils.stringForQuery(db,
                "SELECT _password FROM "+TABLE_LOGIN+" WHERE "+COLUMN_NAME+" = ?",
                new String[]{ name });
    } finally {
        db.close();
    }
}

score 0 · Answer 3 · answered Feb 24 '17 at 10:02

This is not an Android specific issue, it's basic SQL error. That statement should look like the following:

SELECT * FROM login WHERE _name = "kristofer"

If you properly inject your values it will do this for you.

Values in "" or '' are treated as values/strings, without the enclosing quotes it is treated as the name of a column to lookup.

Use the follwoing:

String[] vals = { name };
String query = "SELECT * FROM " + TABLE_LOGIN + " WHERE " + COLUMN_NAME + " = ?";
Cursor c = db.rawQuery(query, vals);

Android studio SQLiteOpenHelper select from * where... no such column error

3 Answers3