FlawOpen

flawopen.com/sql-injection/Kotlin

● CWE-89 · Critical
FlawOpen Security Research

SQL Injection in Kotlin (Android SQLite, Room, Exposed)

A Kotlin string template like "... = $id" inside rawQuery(), execSQL() or a Room raw query lets untrusted input rewrite your SQL and return every row (CWE-89). The bound form for Android SQLite, Room and Exposed, and how to sort by a user-chosen column safely.[1], [4], [2], [7], [10], [6]

💡 Plain English Explainer (ELI5)

Think of an Android access terminal with fixed commands and a badge-number slot. Kotlin string templates build the whole SQL command before SQLite sees it, so $userId can become part of the command itself. rawQuery() with selectionArgs, Room @Query parameters, and Exposed DSL expressions keep values in data slots. A SimpleSQLiteQuery needs its bindArgs too. execSQL() is for statements that do not return rows; use Android’s update() and delete() APIs for row changes. Column names are command choices, not values, so choose ORDER BY from fixed trusted options. A Kotlin when expression can select the ORDER BY column from fixed trusted options.[4], [2], [3], [10], [11]

Core Concepts & Subsystem Terms

String templates ($id) in SQL
"... WHERE id = $userId" is ordinary string interpolation, evaluated before the database API is called. It looks close to Room's :name placeholders, but the value is already inside the SQL when the driver receives it.[1], [4]
rawQuery(sql, selectionArgs)
rawQuery(sql, selectionArgs) replaces each ? with a bound value; Android documents these arguments as strings. SQLite applies column-affinity rules during comparisons, but binding is what prevents the value from becoming SQL syntax. Do not treat affinity as input validation.[2], [12]
execSQL(sql, bindArgs)
execSQL(sql) is for statements that do not return rows and is not the API for SELECT, INSERT, UPDATE, or DELETE. Android provides insert(), update(), and delete() for those operations; update() and delete() accept a WHERE clause plus bound whereArgs. The execSQL(sql, bindArgs) overload supports only byte arrays, strings, longs, and doubles for statements that are appropriate for execSQL.[2]
Room @Query vs @RawQuery
@Query("... WHERE name = :name") is checked at compile time and binds :name as a parameter. @RawQuery accepts any SupportSQLiteQuery; a SimpleSQLiteQuery built from a string template is unbound SQL. If you need @RawQuery, pass values in its bindArgs array.[7], [8], [9]
Exposed DSL vs exec()
Exposed DSL expressions such as Users.team eq team generate bound parameters. exec("... '$team'") runs raw SQL; its args overload binds values, but a string template in the SQL text is sent as written.[10], [11]

Framework mappings

Step-by-Step Attack Flow

Step 1

Untrusted input reaches the repository

A deep link, intent extra, ContentProvider selection or backend request supplies userId, which is passed to findEmail() as a String.[4]

Step 2

The string template builds the SQL

"... WHERE id = $userId" interpolates first. With the benign test value 0 OR 1=1, the statement reads WHERE id = 0 OR 1=1.[1], [4]

Step 3

SQLite compiles the attacker's text

rawQuery receives null for its arguments, so there is nothing to bind and SQLite parses OR 1=1 as part of the WHERE clause.[2], [1]

Step 4

Every row comes back

The cursor can contain every row in the table. If a ContentProvider exposes this query, other apps may access data beyond the intended interface when export or permission settings are too broad; actual access depends on the provider configuration.[4], [13]

Source Code: Flaw vs. Secure Implementation

UNPATCHED FLAW
// UserRepository.kt: each query below interpolates input into the SQL text
class UserRepository(private val db: SQLiteDatabase) {

    // rawQuery: the template runs first, and no selectionArgs are passed
    fun findEmail(userId: String): String? =
        db.rawQuery("SELECT email FROM users WHERE id = $userId", null).use { c ->
            if (c.moveToFirst()) c.getString(0) else null
        }

    // Illustrative unsafe SQL construction is described in the prose; this read-only example avoids writes.

    // ORDER BY column taken straight from the caller
    fun listEmails(sortColumn: String): List<String> =
        db.rawQuery("SELECT email FROM users ORDER BY $sortColumn", null).use { c ->
            buildList { while (c.moveToNext()) add(c.getString(0)) }
        }
}

@Dao
interface UserDao {
    @RawQuery
    fun search(query: SupportSQLiteQuery): List<User>
}

// Room: a raw query built from a template bypasses Room's binding
fun searchByName(dao: UserDao, name: String): List<User> =
    dao.search(SimpleSQLiteQuery("SELECT * FROM users WHERE name = '$name'"))

// Exposed: exec() sends the interpolated SQL as written
fun countByTeam(team: String): Long? = transaction {
    exec("SELECT count(*) FROM users WHERE team = '$team'") { rs ->
        if (rs.next()) rs.getLong(1) else null
    }
}
HARDENED SECURE PATCH
// UserRepository.kt: values are bound, columns come from a fixed mapping
class UserRepository(private val db: SQLiteDatabase) {

    // rawQuery: ? marker, value passed in selectionArgs
    fun findEmail(userId: Long): String? =
        db.rawQuery("SELECT email FROM users WHERE id = ?", arrayOf(userId.toString())).use { c ->
            if (c.moveToFirst()) c.getString(0) else null
        }

    // when maps the key to a constant column name
    fun listEmails(sortKey: String): List<String> {
        val sql = when (sortKey) {
            "email" -> "SELECT email FROM users ORDER BY email"
            "created" -> "SELECT email FROM users ORDER BY created_at"
            else -> "SELECT email FROM users ORDER BY id"
        }
        return db.rawQuery(sql, null).use { c ->
            buildList { while (c.moveToNext()) add(c.getString(0)) }
        }
    }
}

@Dao
interface UserDao {
    @Query("SELECT * FROM users WHERE name = :name")
    fun findByName(name: String): List<User>
}

// Room: :name is checked at compile time and bound as a parameter
fun searchByName(dao: UserDao, name: String): List<User> = dao.findByName(name)

// Exposed DSL: eq generates a bound parameter
fun countByTeam(team: String): Long = transaction {
    Users.selectAll().where { Users.team eq team }.count()
}

Engineering & System Hardening Checklist

Sources