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 high-performance applications
• Usage of play.api.db for database access in Play Framework
• Usage of the Play.current object for accessing the current Play application instance

Description

Non compliant code

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

class UserController extends Controller {
  def getUser = Action { request =>
    // Get the username from the request parameters
    val username = request.getQueryString("username").getOrElse("")
    
    // Get the user from the database
    DB.withConnection { conn =>
      val stmt = conn.createStatement()
      val rs = stmt.executeQuery(s"SELECT * FROM users WHERE username = '$username'")
      while (rs.next()) {
        println(rs.getString("username"))
      }
    }
  }
}
        
        

The code above is an example of a SQL injection vulnerability in a Scala backend application that uses the Play framework.

In the getUser method, the username is directly obtained from the request parameters without any data validation. This username is then directly concatenated into a SQL SELECT statement.

This approach is vulnerable to SQL injection attacks. If an attacker provides a username that includes SQL syntax, they could manipulate the SQL statement to return all users, delete data, or even drop tables. For example, a username of ' OR '1'='1 would change the SQL statement to SELECT * FROM users WHERE username = '' OR '1'='1', which would return all users.

To make matters worse, the SQL statement is executed with conn.createStatement(), which does not use any form of parameterized query or stored procedure. This means there's no mechanism in place to automatically escape special characters in the username that could be used for SQL injection.

Steps

• Use parameterized statements or prepared statements to execute SQL queries.
• Validate and sanitize user input before using it in SQL queries.
• Avoid concatenating user input directly into SQL queries.
• Implement input validation to ensure that only expected data types and formats are accepted.
• Implement proper error handling and logging to detect and respond to SQL injection attempts.

Compliant code

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

class UserController extends Controller {
  def getUser = Action { request =>
    // Get the username from the request parameters
    val username = request.getQueryString("username").getOrElse("")
    
    // Validate and sanitize the username
    if (username.matches("^[a-zA-Z0-9_]+$")) {
      // Get the user from the database using a parameterized query
      DB.withConnection { implicit conn =>
        SQL("SELECT * FROM users WHERE username = {username}")
          .on('username -> username)
          .executeQuery()
          .foreach { row =>
            println(row[String]("username"))
          }
      }
    } else {
      // Log an error and return a response indicating invalid input
      Logger.error(s"Invalid username: $username")
      BadRequest("Invalid username")
    }
  }
}
        
        

The updated code includes several changes to mitigate the risk of SQL injection:

1. Parameterized Query: Instead of concatenating the username directly into the SQL query, a parameterized query is used. This ensures that the username is properly escaped, preventing any malicious SQL code from being executed.

2. Input Validation: The username is checked against a regular expression to ensure it only contains alphanumeric characters and underscores. This is a basic form of input validation and sanitization that can help prevent SQL injection. Depending on the specific requirements of your application, you may need to use a more complex regular expression or additional validation logic.

3. Error Handling and Logging: If the username does not pass the validation check, an error is logged and a response indicating invalid input is returned. This can help detect and respond to SQL injection attempts.

Note: This code assumes that you are using the Anorm library, which is commonly used with the Play framework for database access. If you are using a different library, the exact syntax for parameterized queries may be different.

References

• 146. SQL injection