How to deal with unrecognized characters in SELECT query statement in SQLiteDatabase?

Question

Here is my code in Android Studio:

fun find(name: String) {
    val db = this.readableDatabase
    val cursor = db.rawQuery("SELECT * FROM $MY_TABLE WHERE $NAME='${name}'", null)

    if (cursor.moveToFirst()) {
        do {
            ...
        } while (cursor.moveToNext())
    }

    cursor.close()
    db.close()
}

The problem here is that if there is a ' in the argument value of the name I'm trying to find, an error occurs. When I try to find a WHERE match in a SELECT statement, I know that ' is causing the problem. How do I fix this? And what other characters shouldn't be used in the SELECT statement?

Answer 1

You can use a ? placeholder and pass the parameter name as the item of an array in the 2nd argument of rawQuery():

val cursor = db.rawQuery("SELECT * FROM $MY_TABLE WHERE $NAME = ?", arrayOf(name))

This is the safe and recommended way to pass parameters and you don't need to worry about quoting the string parameter because it will be taken care of by rawQuery().