Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLite Exception no such column when trying to select

Tags:

android

sqlite

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?

like image 625
bMon Avatar asked Jun 13 '11 22:06

bMon


1 Answers

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.

like image 154
p.campbell Avatar answered Sep 19 '22 05:09

p.campbell