Fluid Attacks Database

Need

Prevention of SQL injection by binding user input as query parameters

Context

• Usage of Kotlin 1.9+ on the JVM for building application services

• Usage of JDBC for database access

Description

1. Non compliant code

import java.sql.Connection

fun findUser(connection: Connection, userId: String): String? {
    // "' OR '1'='1" rewrites the WHERE clause
    val sql = "SELECT name FROM users WHERE id = '$userId'"
    connection.createStatement().use { statement ->
        statement.executeQuery(sql).use { rows -> return if (rows.next()) rows.getString("name") else null }
    }...

The `findUser` function below builds a SQL statement with a string template, placing the `userId` argument between single quotes, and runs it with `Statement.executeQuery`. The database receives a single string and cannot tell which part came from the developer and which from the caller. A `userId` of `' OR '1'='1` returns every row, and payloads with `UNION SELECT` read other tables, including password hashes. Depending on the driver, stacked statements such as `'; DROP TABLE users; --` can also modify or delete data. The same flaw appears with `+` concatenation, with Android `SQLiteDatabase.rawQuery`, and with `JdbcTemplate` or Exposed methods that receive a pre-built SQL string.

2. Steps

• Use `PreparedStatement` with `?` placeholders for every query that includes external data, and set values with `setString`, `setLong` and similar methods.

• Never build SQL with string templates or `+` concatenation from request data, including in `rawQuery`, `JdbcTemplate` and Exposed `exec` calls.

• Prefer query builders such as Exposed DSL or JPA criteria, which bind values automatically.

• Map dynamic identifiers such as column names and sort directions through an allowlist.

• Connect with a database account that has only the privileges the application needs.

3. Secure code example

import java.sql.Connection

fun findUser(connection: Connection, userId: String): String? =
    connection.prepareStatement("SELECT name FROM users WHERE id = ?").use { statement ->
        statement.setString(1, userId)
        statement.executeQuery().use { rows -> if (rows.next()) rows.getString("name") else null }
    }

The corrected function uses a `PreparedStatement` whose SQL text is a constant with a `?` placeholder, and supplies the value with `setString`. The driver sends the statement and the value separately, so the database always treats the value as data: quotes, comments or semicolons inside it cannot change the structure of the query. Prepared statements can also be cached by the driver and the database. Placeholders only protect values. When a caller must choose a column or a sort direction, the code has to map the input to a fixed set of identifiers instead of inserting it into the SQL text.

References

• 146. SQL injection