SQLite Exception no such column when trying to select

Question

I have the following DB helper class:

public int studentExists(String studid) {     Cursor dataCount = mDb.rawQuery("select count(*) from usertable where " + KEY_STUDID + "=" + studid, null);     dataCount.moveToFirst();     int count = dataCount.getInt(0);     dataCount.close();     return count; } 

I use this in my app to see if a student id has been previously entered.

This works fine when the student id's are ints (346742) but whenever I try to add an alpha-numeric id (PB3874) it force closes the app.

Error:

06-13 18:22:20.554: ERROR/AndroidRuntime(8088): android.database.sqlite.SQLiteException: no such column: pb3874: , while compiling: select count(*) from usertable where studid=pb3874

I don't think its a data type issue (because I use the text type):

    private static final String DATABASE_CREATE =     "create table usertable (_id integer primary key autoincrement, "     + "studid text not null);"; 

But I'm confused why the error is saying no such column: pb3874 as I'm trying to simply select that value from the studid column. And also why this works perfectly for any int value. Anyone have any problem solving advice?

Answer 1

What's happening here is that SQLite thinks that 'pb3874' is actually a column name, rather than a string/text literal.

To specify that it's a text literal, you'll want to ensure your text value is wrapped in the appropriate single quotes:

To prevent SQL injection attacks, whenever you're taking input from the user, use a parameterized query:

("select count(*) from usertable where " + KEY_STUDID + "=?", studid); 

Without parameterization (very much discouraged when taking user input):

("select count(*) from usertable where " + KEY_STUDID + "='" + studid + "'", null);   

The reason your numeric values didn't produce this: SQLite converted those numeric literals for you.