I've seen it happen in a dozen different code reviews: a developer is in a rush to ship a feature, and they use a Kotlin string template to build a database query. It looks clean, it's readable, and it works perfectly during happy-path testing. But from a security perspective, it's like leaving your front door wide open with a sign that says "Please don't steal my things."

The Temptation of String Templates

Let's say we're building a project management tool. We need a function that fetches a project by its unique slug provided in the URL. In a naive implementation, you might be tempted to do something like this:

fun getProjectBySlug(slug: String): Project? {
    val query = "SELECT * FROM projects WHERE slug = '$slug'"
    return db.executeSingle(query)
}

On the surface, this is just standard Kotlin. You're using a string template to inject the slug variable into the SQL command. If the user searches for "my-awesome-app", the database gets SELECT * FROM projects WHERE slug = 'my-awesome-app', and everything works. But here is the problem: you are treating user input as executable code.

When Your Database Starts Listening to the User

The danger isn't when the user is honest; it's when they're curious. If I'm a malicious actor, I won't send a slug. I'll send a fragment of SQL. Imagine if the slug variable becomes: ' OR '1'='1.

Your resulting query becomes: SELECT * FROM projects WHERE slug = '' OR '1'='1'. Because '1'='1' is always true, the database ignores the slug entirely and returns every single project in your system, regardless of permissions or ownership. In a worst-case scenario, an attacker could use a semicolon to terminate your query and start a new one, like '; DROP TABLE users; --, and suddenly your production database is empty. I can't stress this enough: never, ever trust a string coming from a request body, a query parameter, or a header.

Parameterized Queries and the Wall of Separation

The industry standard for fixing this is using parameterized queries (or Prepared Statements). Instead of merging the data into the query string yourself, you send the query template to the database first, and then you send the data separately.

fun getProjectBySlug(slug: String): Project? {
    val sql = "SELECT * FROM projects WHERE slug = ?"
    return db.prepare(sql).use { statement -> 
        statement.setString(1, slug)
        statement.executeQuery().singleOrNull()
    }
}

By using the ? placeholder, you're telling the database: "Here is the logic of the command. Expect a piece of data to follow." When statement.setString(1, slug) is called, the database driver ensures that the input is treated strictly as a literal value, not as part of the SQL command. If a user tries to pass ' OR '1'='1 now, the database will literally look for a project whose slug is the string "' OR '1'='1". It won't find one, and your system remains secure.

Now, in a real-world Kotlin project, you probably aren't writing raw JDBC. You're likely using a library like Exposed or SQLDelight. These libraries handle parameterization under the hood. For instance, in Exposed, using Projects.select { Projects.slug eq slug } automatically creates a parameterized query. The trade-off here is a slight bit of abstraction overhead, but compared to the catastrophic cost of a data breach, it's a price I'm always willing to pay.

📋 Practical Task

Exercise: Securing the User Profile Search API

You have been handed a legacy Kotlin service that allows administrators to search for users by their email address. The current implementation is vulnerable to SQL injection. Your task is to refactor the search function to eliminate the vulnerability.

Current Vulnerable Code:

class UserRepository(private val connection: Connection) {
    fun findUserByEmail(email: String): User? {
        // VULNERABLE: String concatenation used for query building
        val sql = "SELECT * FROM users WHERE email = '" + email + "'"
        val resultSet = connection.createStatement().executeQuery(sql)
        
        return if (resultSet.next()) {
            User(resultSet.getInt("id"), resultSet.getString("email"))
        } else null
    }
}

Requirements:

• Rewrite the findUserByEmail function using a PreparedStatement.
• Ensure the PreparedStatement is properly closed (use the .use {} extension function) to prevent memory leaks.
• Ensure the user input is bound to the query as a parameter rather than concatenated into the string.