SQLite database "No such column" error on verified existing column

Question

I am stuck on an "SQLiteException: no such column:" error. I am trying to make some code to check if an item exists in the database before storing it, I don't know if it's the best way or not but it does the job. Or not really, when I use all numbers for the data in the particular column that I'm searching it works fine. But if there is any letter in the column data it crashes.

The MainActivity

private ConversationsDatabaseHelper db;
//onCreate stuff here
db = new ConversationsDatabaseHelper(this);
String threadId = "886";
Log.d(TAG, "dbTest: EXISTS; " + db.conversationExists(threadId));

and in the databaseHelper class is the converstionExists function

public boolean conversationExists(String threadId) {
// Select All Query
String selectQuery = "SELECT  * FROM " + Conversations.TABLE_NAME + " WHERE " +
        Conversations.COLUMN_THREAD_ID + " LIKE " + threadId;

SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
boolean returnValue = false;
// Check if this message exists
if (cursor.moveToFirst()) {
    returnValue = true;
}
// close db connection
db.close();
return returnValue;
}

So if I use for example "886" as the threadId value then all is fine. If I create a row with matching threadId then it returns true. In this case I did not so hence false. stack trace...

dbTest: EXISTS; false

but using a886 results in

Caused by: android.database.sqlite.SQLiteException: no such column: a886 (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM conversations WHERE thread_id LIKE a886

and 88a6 results in this

 Caused by: android.database.sqlite.SQLiteException: unrecognized token: "88a6" (code 1 SQLITE_ERROR): , while compiling: SELECT  * FROM conversations WHERE thread_id LIKE 88a6

It almost looks like mixing letters and numbers might be part of the reason here but should not be as the column was created to hold TEXT datatype. here is the database create table query string.

    public static final String TABLE_NAME = "conversations";

public static final String COLUMN_ID = "id";
public static final String COLUMN__ID = "_id";
public static final String COLUMN_GROUP_ID = "group_id";
public static final String COLUMN_LAST_MESSAGE_ID = "last_message_id";
public static final String COLUMN_THREAD_ID = "thread_id";
public static final String COLUMN_ADDRESS = "address";
public static final String COLUMN_CONTACT = "contact";
public static final String COLUMN_BODY = "body";
public static final String COLUMN_DATE = "date";
public static final String COLUMN_TYPE = "type";
public static final String COLUMN_STATE = "state";
public static final String COLUMN_READ = "read";
public static final String COLUMN_STATUS = "status";
public static final String COLUMN_CT = "ct";    
// Create table SQL query
public static final String CREATE_TABLE =
        "CREATE TABLE " + TABLE_NAME + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY ,"
                + COLUMN_LAST_MESSAGE_ID + " TEXT,"
                + COLUMN__ID + " TEXT,"
                + COLUMN_GROUP_ID + " TEXT,"
                + COLUMN_THREAD_ID + " TEXT,"
                + COLUMN_ADDRESS + " TEXT,"
                + COLUMN_CONTACT + " TEXT,"
                + COLUMN_BODY + " TEXT,"
                + COLUMN_DATE + " TEXT,"
                + COLUMN_TYPE + " TEXT,"
                + COLUMN_STATE + " TEXT,"
                + COLUMN_READ + " TEXT,"
                + COLUMN_STATUS + " TEXT,"
                + COLUMN_CT + " TEXT"
                + ")";

I am "up a creek" with this and any help would be greatly appreciated.

I suggest you learn about the Room API. This makes it much easier to work with a SQLite database rather than writing raw HTML. — Code-Apprentice, Oct 12 '21 at 21:41
Have a look at [this article](https://stackoverflow.com/questions/1601151/how-do-i-check-in-sqlite-whether-a-table-exists) about querying to see if a table exists. — AwiringCameron, Oct 12 '21 at 21:45

MikeT · Accepted Answer · 2021-10-12T21:55:59.973

The value a886 should be a text/string literal not a numeric literal. Therefore it should be enclosed in single quotes. The errors are because:-

a886 fails with no column found as it's taken to be a column name as it's not a literal.
whilst 88a6 is first not a valid literal (due to the a) and therefore a column name but then an invalid column (cannot start with a numeric unless enclosed) name and thus not a known token.
See Literal Values (Constants)

You could fix this using (enclosing the threadId in single quotes ) :-

String selectQuery = "SELECT  * FROM " + Conversations.TABLE_NAME + " WHERE " +
    Conversations.COLUMN_THREAD_ID + " LIKE '" + threadId + "'";

However, it is recommended to use bound parameters to protect against SQL Injection.

Thus it would be recommended to use :-

String selectQuery = "SELECT  * FROM " + Conversations.TABLE_NAME + " WHERE " +
        Conversations.COLUMN_THREAD_ID + " LIKE ?";

along with :-

Cursor cursor = db.rawQuery(selectQuery, new String[]{threadId});

i.e. the ? is replaced by the threadId value properly enclosed.

You may wish to consider using the convenience query method rather than rawQuery, this would be :-

Cursor cursor = db.query(Conversations.TABLE_NAME,null,Conversations.COLUMN_THREAD_ID + " LIKE ?", new String[]{theadId},null,null, null);

The SQL is built for you.

It almost looks like mixing letters and numbers might be part of the reason here but should not be as the column was created to hold TEXT datatype.

SQlite has no issue storing any value in any type. The type, which itself can be virtually anything (rules are used to determine the resultant type), is only an indication of the value that will be stored. The only exception is that a rowid or an alias of the rowid MUST be an integer value (your id column is an alias of the rowid column).

Commenting for emphasis: Use bound parameters, not string concatenation, to avoid SQL injection attacks. — Code-Apprentice, Oct 12 '21 at 21:42
Thanks this fixes the problem. Also, I do not believe that I am exposed to any possible injection attacks as none of the variables are user-accessible. The thread id is obtained from the SMS thead_id column which can't be edited by a user as far as I know. At the same time, you can never be too secure. Thanks again, this really helped me out. — edgar_wideman, Oct 12 '21 at 22:16

SQLite database "No such column" error on verified existing column

1 Answers1