Skip to main content

SQL injection - Code

Need​

Implementation of proper input validation and the use of parameterized statements or stored procedures to prevent SQL injection attacks.

Context​

  • Usage of Scala for building scalable and functional applications
  • Usage of Anorm for type-safe database access in Scala
  • Usage of play.api.db.DB for database operations in Play Framework
  • Usage of the Play.current object in the Play framework for accessing the current application instance

Description​

Non compliant code​

import anorm._
import play.api.db.DB
import play.api.Play.current

def getUser(username: String, password: String): User = {
  DB.withConnection { implicit connection =>
    SQL("SELECT * FROM users WHERE username = " + username + " AND password = " + password)
      .as(User.parser.singleOpt)
  }
}

In the above Scala code, the getUser function is vulnerable to SQL Injection. The function takes a username and password as parameters and directly interpolates them into a SQL query string.

The query is intended to select a user from the users table where the username and password match the provided parameters. However, because the parameters are directly concatenated into the query string, an attacker could potentially manipulate the SQL query by providing specially crafted input.

For example, if an attacker provides "admin'; --" as the username and any random string as the password, the SQL query becomes:

SELECT * FROM users WHERE username = 'admin'; -- AND password = 'random_string'

The -- in SQL starts a comment, causing the rest of the query to be ignored. This would effectively log in the attacker as the 'admin' user, regardless of the password provided.

This vulnerability can lead to unauthorized access, data leakage, data loss, or even denial of service in severe cases.

Steps​

  • Use parameterized statements or prepared statements to construct SQL queries.
  • Validate and sanitize user input before using it in SQL queries.
  • Avoid concatenating user input directly into SQL queries.
  • Use an ORM (Object-Relational Mapping) framework to handle database interactions.
  • Implement proper authentication and authorization mechanisms to prevent unauthorized access to sensitive data.

Compliant code​

import anorm._
import play.api.db.DB
import play.api.Play.current

def getUser(username: String, password: String): User = {
  DB.withConnection { implicit connection =>
    SQL("SELECT * FROM users WHERE username = {username} AND password = {password}")
      .on('username -> username, 'password -> password)
      .as(User.parser.singleOpt)
  }
}

The original code was vulnerable to SQL Injection attacks because it was directly concatenating user input into the SQL query. This could allow an attacker to manipulate the SQL query by providing specially crafted input.

The fixed code uses parameterized queries to prevent SQL Injection. In a parameterized query, placeholders are used instead of directly embedding user input in the query. The user input is then supplied as parameters that replace the placeholders in the query. This ensures that the user input is properly escaped, and cannot be used to inject malicious SQL code.

In the fixed code, {username} and {password} are placeholders in the SQL query. The actual values of username and password are supplied with the .on('username -> username, 'password -> password) method. This ensures that the username and password values are properly escaped and cannot be used to manipulate the SQL query.

This fix also assumes that the username and password values are validated and sanitized before the getUser method is called. It's important to always validate and sanitize user input to prevent other types of attacks.

In addition to using parameterized queries, it's also recommended to use an ORM (Object-Relational Mapping) framework to handle database interactions, and to implement proper authentication and authorization mechanisms to prevent unauthorized access to sensitive data.

References​

Last updated on