Need

Secure storage of confidential information in the database

Context

• Usage of Scala for functional and object-oriented programming
• Usage of play.api.db for database access in Play Framework
• Usage of Anorm for type-safe SQL queries in Scala

Description

Non compliant code

        import play.api.db._
import anorm._

def storeUserQuery(query: String) = {
  DB.withConnection { implicit connection =>
    SQL(
      """
        insert into user_queries(query)
        values({query})
      """
    ).on(
      'query -> query
    ).executeUpdate()
  }
}
        
        

In this Scala code snippet, we are using the Play Framework and its Anorm library to interact with the database.

The function storeUserQuery takes a user query as a string and directly stores it into the user_queries table in the database. The withConnection method is used to establish a connection with the database, and the SQL method is used to execute the SQL query. The on method is used to replace the placeholder in the SQL query with the actual user query.

The vulnerability here is that the user queries are stored directly into the database without any form of encryption. This means that if an attacker gains access to the database, they can read the user queries in plain text. This is a serious security risk, especially if the queries contain sensitive information.

Steps

• Enable encryption for the database connection
• Use parameterized queries to prevent SQL injection
• Implement access controls to restrict unauthorized access to the database
• Implement proper authentication and authorization mechanisms
• Implement secure storage mechanisms for sensitive data

Compliant code

        import play.api.db._
import anorm._
import javax.crypto.Cipher
import javax.crypto.spec.SecretKeySpec
import java.util.Base64

val secretKey = "my-secret-key" // This should be stored securely and not in plain text

def encrypt(query: String): String = {
  val cipher = Cipher.getInstance("AES/ECB/PKCS5Padding")
  val secretKeySpec = new SecretKeySpec(secretKey.getBytes, "AES")
  cipher.init(Cipher.ENCRYPT_MODE, secretKeySpec)
  Base64.getEncoder.encodeToString(cipher.doFinal(query.getBytes))
}

def storeUserQuery(query: String) = {
  val encryptedQuery = encrypt(query)
  DB.withConnection { implicit connection =>
    SQL(
      """
        insert into user_queries(query)
        values({query})
      """
    ).on(
      'query -> encryptedQuery
    ).executeUpdate()
  }
}
        
        

The updated code now includes an encryption function that uses AES encryption to encrypt the user queries before they are stored in the database. The encrypt function takes a string as input and returns the encrypted string. The encryption is done using a secret key, which should be stored securely and not in plain text.

In the storeUserQuery function, the user query is first encrypted using the encrypt function, and then the encrypted query is stored in the database. This ensures that even if an attacker gains access to the database, they will not be able to read the user queries as they are encrypted.

Please note that this is a basic implementation of encryption and there are many other factors to consider for a secure system, such as secure key management, using secure connections, implementing access controls, and more.

References

• 246. Non-encrypted confidential information - DB