SQL, or in full, Structured Query Language, are sets of instructions (queries) that developers can use to interact with databases. Databases can be SQL or NoSQL, and the latter may or may not support SQL-like queries. Examples of SQL databases include MySQL, Oracle, PostgreSQL, and Microsoft SQL Server.
SQL queries for reading or manipulating data look very similar to plain English. For example, the query SELECT * FROM users means, SQL should fetch data from the users table. The symbol * in the query means select all columns.
SQL is a very powerful tool for developers and database administrators. But, in the wrong hands, that power can cause great harm. For example, SQL injection is one type of exploit that malicious persons can use to do harm.
In this post, you'll learn about SQL injection and see some examples of Kotlin SQL injection. In addition to that, I'll guide you through some methods to prevent Kotlin SQL injection.
What Is SQL Injection?
In simple terms, SQL injection is a security vulnerability that enables malicious actors to alter database queries. It can lead to unauthorized access to sensitive user and application data stored in the database.
We can use the following query to demonstrate how SQL injection works:
SELECT * FROM users WHERE email='admin@example.com' OR 1=1;This query returns all rows from the users table. It returns all rows because 1=1 is always true and the OR expression evaluates to true whenever at least one side of the OR statement is true. In a real application that depends on user input for the value of email, a hacker can inject an expression like the one above to gain access to all data in a table.
Not all SQL injection attacks are targeted toward viewing restricted data. Attackers can also alter records in a database using SQL injection. A hacker can even drop an entire table with a statement like the following:
SELECT * FROM users WHERE email='admin@example.com'; DROP users;This code executes the DROP table statement after the SELECT statement, leading to a permanent loss of data.
There are many variations of SQL injection. To learn more about them, check out the detailed post about SQL injection here.
Now that we know what SQL injection is, in the next section, we'll proceed to discuss SQL injection in Kotlin.
Examples of SQL Injection in Kotlin and Prevention Techniques
You can use SQL to persist data in Kotlin applications, and in this section, we'll look at some examples of SQL injection that can occur in Kotlin. In addition, at the end of each example, we'll learn how to prevent it. So, let's start with our first example.
1. Use of Raw Queries
Here, raw SQL queries refers to the bare minimum SQL statement. For example, SELECT * FROM table WHERE col=value1. In Kotlin, it's possible to set dynamic values for value1 using a variable and string interpolation. In that case, the above SQL query becomes:
var city = "NYC"
val query = "SELECT * FROM table WHERE col=$city"In this example, we're considering a Kotlin server-side application that's powered by Ktor. The application should display a private message sent to a user using a URL like this,
https://example.com/message/36where 36 is a unique message ID. However, an attacker can alter the above URL to gain access to all messages saved on the system.
The following URL has been modified to do that:
https://example.com/messages/36%20OR%201=1Before we continue, here's what the underlining Kotlin code for retrieving data looks like:
val id = call.parameters["id"]
if (stmt.execute("SELECT * FROM messages WHERE id=$id;")) {
resultset = stmt.resultSet
}From this code, we can see that the value for id is read from the URL using call.parameter. Then, the SQL query uses the value for id in the where clause.
The next interesting thing to consider is the value 36%20OR%201=1 from our second URL. When the application loads the URL, it executes the following SQL query:
SELECT * FROM messages WHERE id=36 OR 1=1;The expression 1=1 is always true. As a result, SQL returns all rows from the messages table, granting a hacker unauthorized access to all messages.
Prevention
You can prevent this type of SQL injection using PreparedStatement. With PreparedStatement, user input is always treated as parameters and never as part of the actual SQL statement. That means when you use PreparedStatement, SQL knows the value for id is "36 OR 1=1," in contrast to the raw query, that thinks id is "36" and "OR 1=1" are other parts of the where clause.
PreparedStatement uses ? as a placeholder for parameters. For example, here's our initial query for reading data from the messages table, but with the addition of the ? placeholder:
SELECT * FROM messages WHERE id=?;Another method to prevent this type of attack is validation. Always check user input to validate that it conforms to the expected range or type. For instance, in our example, the system expects an integer value for id. Fortunately, Kotlin has the toIntOrNull function. This function converts a numeric string to the equivalent integer value, or returns null if the value is not numeric. That is, "11" becomes 11 and "ABCD" returns null.
fun String.toIntOrNull(): Int?Hence, checking that the value provided is an integer before deciding the next action can prevent the attack.
2. Native SQL Option in ORM
Ktorm is an object-relational mapper (ORM) framework for Kotlin. It’s based on pure Java database connectivity (JDBC). One benefit of using an ORM is that it has built-in mechanisms for preventing SQL injection. However, in order to allow for more flexibility, the Ktorm ORM supports native SQL.
You can run raw SQL queries in Ktorm using the database.useConnection() method. Then, perform database operations using an existing connection and JDBC.
In this example, we'll look at some Kotlin code for updating the profile for a specific user.
val database = Database.connect("jdbc:mysql://127.0.0.1:3306/who_dey?useLegacyDatetimeCode=false&serverTimezone=UTC", user = "root", password = "")
val newPassword = "123456"
val users = database.useConnection { connection ->
val sql = "UPDATE users SET password=$newPassword WHERE email=$email" val stmt = connection.createStatement() if (stmt.execute(sql)) {
println("Password rest!")
}
}
The above code sample is vulnerable to SQL injection. An attacker could update the profile for all users just by setting the value for email to the following:
123456 OR 2>1 Just like in our previous example, 2 is always greater than 1. Hence, the statement will always return true. As a result, all rows in the users table will have their value for password set to 123456. This means the hacker could log in to any account using 123456 as a password.
Prevention
The creators of Ktorm don't recommend using the native SQL feature. However, if you must use it, do so with the addition of PreparedStatements. Here is a version of the code for updating password, but this time, using PreparedStatement and ? as a placeholder for parameters.
val newPassword = "123456"
val users = database.useConnection { connection ->
val sql = """
UPDATE users SET password=? WHERE email=?
""" connection.prepareStatement(sql).use { statement ->
statement.setString(1, newPassword)
statement.setString(2, email)
statement.execute()
} }In addition to PreparedStatement, validate and sanitize user input before using it in an SQL query.
Conclusion
An attacker can execute a wide variety of dangerous SQL queries using a single weak point in an application. In this post, we covered three common examples. However, the good news is, if you follow best practices and validate user input, you can reduce the chance of leaving vulnerabilities in your application.
Consider using ORM and PreparedStatements rather than raw SQL queries. But keep in mind that using an ORM doesn't always mean you’re 100% safe against SQL injection. Also, I recommend you check out Ktorm, as it offers many benefits, including a strong typed domain specific language (DSL).
This post was written by Pius Aboyi. Pius is a mobile and web developer with over 4 years of experience building for the Android platform. He writes code in Java, Kotlin, and PHP. He loves writing about tech and creating how-to tutorials for developers.
