No such column sqlite exception

Question

I am trying to get some data from user profile, but it doesn't find the column. I already check space between every query and it seems nothing wrong. Till now i can't see the problem, any idea? Thanks before Here is my code and error message

Table Name

private static final String TABLE_PROFILE = "users"; 
private static final String TABLE_STATUS = "status";

Users Column Names

//Users column names
private static final String KEY_USER_ID = "user_id";
private static final String KEY_USER_IMG = "picture";
private static final String KEY_USER_EMAIL = "email";
private static final String KEY_USER_NAME = "name";
private static final String KEY_USER_PASSWORD = "password";
private static final String KEY_USER_PHONE = "phone";

Status Column Names

//Status column names
private static final String KEY_STATUS_ID = "status_id";
private static final String KEY_STATUS = "status";

Table Create Statement

 //Users table create statement
private static final String CREATE_TABLE_PROFILE = "CREATE TABLE " + TABLE_PROFILE + 
        "(" + KEY_USER_ID + " INTEGER PRIMARY KEY," + KEY_USER_IMG + " BLOB," + 
        KEY_USER_EMAIL + " VARCHAR," + KEY_USER_NAME + " VARCHAR," + KEY_USER_PASSWORD + " VARCHAR," 
        + KEY_USER_PHONE + " VARCHAR," + KEY_CREATED_AT + " DATETIME," + KEY_STATUS_ID + " INTEGER," 
        + " FOREIGN KEY " + "(" + KEY_STATUS_ID + ")" + " REFERENCES " + TABLE_STATUS + "(" + KEY_STATUS_ID + ")" + ")";

getProfile method

public Profile getProfile(String username){
    SQLiteDatabase db = this.getReadableDatabase();
    String selectQuery = "SELECT * FROM " + TABLE_PROFILE + " WHERE " + KEY_USER_EMAIL + " = " + username;
    Log.e(LOG, selectQuery);
    Cursor c = db.rawQuery(selectQuery, null);

    if(c!=null)
        c.moveToFirst();

    Profile pf = new Profile();
    pf.setID(c.getInt(c.getColumnIndex(KEY_USER_ID)));
    pf.setImg(c.getBlob(c.getColumnIndex(KEY_USER_IMG)));
    pf.setEmail(c.getString(c.getColumnIndex(KEY_USER_EMAIL)));
    pf.setName(c.getString(c.getColumnIndex(KEY_USER_NAME)));
    pf.setPassword(c.getString(c.getColumnIndex(KEY_USER_PASSWORD)));
    pf.setPhone(c.getString(c.getColumnIndex(KEY_USER_PHONE)));
    pf.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));

    return pf;
}

logcat message

12-18 02:50:32.616: E/DatabaseHelper(7093): SELECT * FROM users WHERE email = test1
12-18 02:50:32.616: E/SQLiteLog(7093): (1) no such column: test1
12-18 02:50:32.636: E/AndroidRuntime(7093): FATAL EXCEPTION: main
12-18 02:50:32.636: E/AndroidRuntime(7093): android.database.sqlite.SQLiteException: no such column: test1 (code 1): , while compiling: SELECT * FROM users WHERE email = test1

You need to surround the `username` value inside the query with apices (**'**), since it's a **string** value. Your query must become: `SELECT * FROM users WHERE email = 'test1'` — Phantômaxx, Dec 17 '14 at 19:56
do you know what `null` is for in `db.rawQuery(selectQuery, null);`? — njzk2, Dec 17 '14 at 20:24
@njzk2 Yes, I'm aware of SQL injection. But: 1 - is your db really visible to other apps? 2 - just a simple example, not even to mention **bound arguments** (which are also nice for reducing concatenations, by the way). — Phantômaxx, Dec 18 '14 at 07:57

score 1 · Answer 1 · answered Dec 18 '14 at 08:35

In SQL, string literals must be quoted.

However, to avoid formatting problems like this (and SQL injection attacks), use parameters instead:

Cursor c = db.rawQuery("SELECT * FROM " + TABLE_PROFILE +
                       " WHERE " + KEY_USER_EMAIL + " = ?",
                       new String[]{ username });

score 0 · Answer 2 · answered Dec 17 '14 at 20:03

0

Isn't the KEY_USER_EMAIL column a string type? If that's the case, your where clause WHERE " + KEY_USER_EMAIL + " = " + username is trying to refer to the column value as KEY_USER_EMAIL = test1, whereas it should really refer / use it as,

KEY_USER_EMAIL = 'test1' Just change your Where clause to like this -

SELECT * FROM " + TABLE_PROFILE + " WHERE " + KEY_USER_EMAIL + " = '" + username + "'"

answered Dec 17 '14 at 20:03

omkar.ghaisas

235
5
19

Pls upvote, if the solution helps solve your problem at hand. Helps, others see a working solution quickly. – omkar.ghaisas Dec 17 '14 at 20:16
1

bad practice because subject to easy SQL injection – njzk2 Dec 17 '14 at 20:25
@omkar.ghaisas i cann't upvote since my reputation still below 15 :( – mrconga Dec 17 '14 at 20:44
Np :) Good to see, that it helped you, get over the roadblock and proceed with your other code. Look at this link - http://stackoverflow.com/questions/9857073/queries-with-prepared-statements-in-android (upvoted answer) to get an idea of how you can implement prepared statement instead of the way you are currently doing it. Hope it helps, you further. @njk2 - I agree, directly adding dynamic variable values into the sql statement is a bad option, as it is subject to sql injection. – omkar.ghaisas Dec 17 '14 at 20:56

score 0 · Answer 3 · answered Dec 17 '14 at 20:03

0

Use this

String selectQuery = "SELECT * FROM " + TABLE_PROFILE + " WHERE " + KEY_USER_EMAIL + " = " + "'" + username + "'";

Value of username needs to be passed as a string.

answered Dec 17 '14 at 20:03

Rohit5k2

17,948
8
45
57

1

bad practice because subject to easy SQL injection – njzk2 Dec 17 '14 at 20:24

No such column sqlite exception

3 Answers3

Linked

Related