Scala Slick Non-Literal SQL Injection Vulnerability

Critical Risk SQL Injection
ScalaSlickSQL InjectionDynamic QueriesVariable InterpolationDatabase Security

What it is

Application uses Slick with non-literal SQL constructions and dynamic query building using user input, creating SQL injection vulnerabilities through variable interpolation.

import slick.jdbc.H2Profile.api._
import scala.concurrent.Future

class DynamicQueryRepository(db: Database) {
  
  // Vulnerable: Variable interpolation in SQL
  def getUsersByField(fieldName: String, fieldValue: String): Future[Vector[String]] = {
    val query = sql"SELECT * FROM users WHERE $fieldName = '$fieldValue'".as[String]
    db.run(query)
  }
  
  // Vulnerable: Dynamic table name
  def getFromTable(tableName: String, condition: String): Future[Vector[String]] = {
    val query = sql"SELECT * FROM $tableName WHERE $condition".as[String]
    db.run(query)
  }
  
  // Vulnerable: Building ORDER BY clause
  def getUsersOrdered(sortField: String, sortOrder: String): Future[Vector[String]] = {
    val orderClause = s"$sortField $sortOrder"
    val query = sql"SELECT * FROM users ORDER BY #$orderClause".as[String]
    db.run(query)
  }
  
  // Vulnerable: Complex dynamic query
  def complexSearch(filters: Map[String, String], joins: List[String]): Future[Vector[String]] = {
    val joinClause = joins.mkString(" ")
    val whereClause = filters.map { case (field, value) =>
      s"$field = '$value'"
    }.mkString(" AND ")
    
    val query = sql"SELECT u.* FROM users u #$joinClause WHERE #$whereClause".as[String]
    db.run(query)
  }
  
  // Vulnerable: User-controlled LIMIT and OFFSET
  def getPaginatedData(limit: String, offset: String): Future[Vector[String]] = {
    val query = sql"SELECT * FROM users LIMIT $limit OFFSET $offset".as[String]
    db.run(query)
  }
}
import slick.jdbc.H2Profile.api._
import scala.concurrent.Future
import scala.util.{Try, Success, Failure}

case class User(id: Int, name: String, email: String, status: String, createdAt: java.sql.Timestamp)

class Users(tag: Tag) extends Table[User](tag, "users") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def email = column[String]("email")
  def status = column[String]("status")
  def createdAt = column[java.sql.Timestamp]("created_at")
  
  def * = (id, name, email, status, createdAt) <> (User.tupled, User.unapply)
}

class SecureQueryRepository(db: Database) {
  val users = TableQuery[Users]
  
  // Safe field mapping
  private val allowedSearchFields = Map(
    "name" -> "name",
    "email" -> "email", 
    "status" -> "status"
  )
  
  private val allowedSortFields = Map(
    "name" -> "name",
    "email" -> "email",
    "created" -> "created_at",
    "id" -> "id"
  )
  
  // Input validation helpers
  def validateFieldName(field: String): Either[String, String] = {
    allowedSearchFields.get(field.toLowerCase) match {
      case Some(validField) => Right(validField)
      case None => Left(s"Field '$field' is not allowed")
    }
  }
  
  def validateSortField(field: String): Either[String, String] = {
    allowedSortFields.get(field.toLowerCase) match {
      case Some(validField) => Right(validField)
      case None => Left(s"Sort field '$field' is not allowed")
    }
  }
  
  def validateSortOrder(order: String): Either[String, String] = {
    order.toLowerCase match {
      case "asc" => Right("asc")
      case "desc" => Right("desc")
      case _ => Left("Sort order must be 'asc' or 'desc'")
    }
  }
  
  def validatePaginationParam(param: String, max: Int): Either[String, Int] = {
    Try(param.toInt) match {
      case Success(value) if value >= 0 && value <= max => Right(value)
      case Success(_) => Left(s"Parameter must be between 0 and $max")
      case Failure(_) => Left("Parameter must be a valid integer")
    }
  }
  
  // Secure: Use type-safe queries instead of dynamic SQL
  def getUsersByField(fieldName: String, fieldValue: String): Future[Either[String, Seq[User]]] = {
    validateFieldName(fieldName) match {
      case Right(validField) =>
        if (fieldValue.length > 100) {
          Future.successful(Left("Field value too long"))
        } else {
          val query = validField match {
            case "name" => users.filter(_.name === fieldValue)
            case "email" => users.filter(_.email === fieldValue)
            case "status" => users.filter(_.status === fieldValue)
          }
          
          db.run(query.result).map(Right(_))
        }
        
      case Left(error) =>
        Future.successful(Left(error))
    }
  }
  
  // Secure: No dynamic table names - use predefined queries
  def getFromTable(tableType: String, userId: Int): Future[Either[String, Seq[User]]] = {
    tableType.toLowerCase match {
      case "users" =>
        val query = users.filter(_.id === userId)
        db.run(query.result).map(Right(_))
        
      case "active_users" =>
        val query = users.filter(u => u.id === userId && u.status === "active")
        db.run(query.result).map(Right(_))
        
      case _ =>
        Future.successful(Left("Invalid table type"))
    }
  }
  
  // Secure: Safe ordering with validation
  def getUsersOrdered(sortField: String, sortOrder: String): Future[Either[String, Seq[User]]] = {
    for {
      validField <- Future.fromTry(validateSortField(sortField).toTry)
      validOrder <- Future.fromTry(validateSortOrder(sortOrder).toTry)
    } yield {
      val baseQuery = users
      
      val orderedQuery = (validField, validOrder) match {
        case ("name", "asc") => baseQuery.sortBy(_.name.asc)
        case ("name", "desc") => baseQuery.sortBy(_.name.desc)
        case ("email", "asc") => baseQuery.sortBy(_.email.asc)
        case ("email", "desc") => baseQuery.sortBy(_.email.desc)
        case ("created_at", "asc") => baseQuery.sortBy(_.createdAt.asc)
        case ("created_at", "desc") => baseQuery.sortBy(_.createdAt.desc)
        case ("id", "asc") => baseQuery.sortBy(_.id.asc)
        case ("id", "desc") => baseQuery.sortBy(_.id.desc)
      }
      
      db.run(orderedQuery.result).map(Right(_))
    }
  }.flatten.recover {
    case ex: NoSuchElementException => Left("Validation failed")
    case _ => Left("Query failed")
  }
  
  // Secure: Type-safe complex search
  def complexSearch(
    nameOpt: Option[String],
    emailOpt: Option[String],
    statusOpt: Option[String]
  ): Future[Either[String, Seq[User]]] = {
    
    var query = users
    
    // Validate and apply filters
    nameOpt.foreach { name =>
      if (name.length <= 50) {
        query = query.filter(_.name like s"%$name%")
      } else {
        return Future.successful(Left("Name filter too long"))
      }
    }
    
    emailOpt.foreach { email =>
      if (email.length <= 100 && email.contains("@")) {
        query = query.filter(_.email like s"%$email%")
      } else {
        return Future.successful(Left("Invalid email filter"))
      }
    }
    
    statusOpt.foreach { status =>
      val allowedStatuses = Set("active", "inactive", "pending", "suspended")
      if (allowedStatuses.contains(status)) {
        query = query.filter(_.status === status)
      } else {
        return Future.successful(Left("Invalid status filter"))
      }
    }
    
    db.run(query.result).map(Right(_))
  }
  
  // Secure: Safe pagination
  def getPaginatedData(limitStr: String, offsetStr: String): Future[Either[String, Seq[User]]] = {
    for {
      limit <- Future.fromTry(validatePaginationParam(limitStr, 1000).toTry)
      offset <- Future.fromTry(validatePaginationParam(offsetStr, 100000).toTry)
    } yield {
      val query = users.drop(offset).take(limit)
      db.run(query.result).map(Right(_))
    }
  }.flatten.recover {
    case ex: NoSuchElementException => Left("Invalid pagination parameters")
    case _ => Left("Pagination query failed")
  }
  
  // Alternative: Safe search with predefined queries
  def predefinedSearch(searchType: String, searchValue: String): Future[Either[String, Seq[User]]] = {
    if (searchValue.length > 100) {
      return Future.successful(Left("Search value too long"))
    }
    
    val query = searchType.toLowerCase match {
      case "name_exact" => users.filter(_.name === searchValue)
      case "name_partial" => users.filter(_.name like s"%$searchValue%")
      case "email_domain" => users.filter(_.email like s"%@$searchValue")
      case "status" => 
        val allowedStatuses = Set("active", "inactive", "pending", "suspended")
        if (allowedStatuses.contains(searchValue)) {
          users.filter(_.status === searchValue)
        } else {
          return Future.successful(Left("Invalid status value"))
        }
      case _ => return Future.successful(Left("Invalid search type"))
    }
    
    db.run(query.result).map(Right(_))
  }
  
  // Secure: Use parameters for any necessary raw SQL
  def safeRawQuery(userId: Int): Future[Either[String, Vector[String]]] = {
    if (userId <= 0) {
      Future.successful(Left("Invalid user ID"))
    } else {
      // Safe: Use parameter binding
      val query = sql"SELECT name FROM users WHERE id = $userId AND status = 'active'".as[String]
      db.run(query).map(Right(_))
    }
  }
}

💡 Why This Fix Works

See fix suggestions for detailed explanation.

Why it happens

Slick code builds SQL dynamically: val sqlString = s"SELECT * FROM users WHERE email = '$email'"; sql"#$sqlString".as[User]. # passes raw SQL. String variable from user input. sql interpolator with # doesn't parameterize. SQL injection through sqlString.

Root causes

Using sql Interpolator with Non-Literal Strings

Slick code builds SQL dynamically: val sqlString = s"SELECT * FROM users WHERE email = '$email'"; sql"#$sqlString".as[User]. # passes raw SQL. String variable from user input. sql interpolator with # doesn't parameterize. SQL injection through sqlString.

Concatenating User Input Before sql Interpolator

Pre-built SQL: val query = "SELECT * FROM " + tableName + " WHERE id = " + userId; sql"#$query".as[Int]. tableName or userId from user. String concatenation before Slick. # operator treats as raw SQL. Circumvents parameterization safety.

Using # Operator with Variables Instead of $

Wrong interpolation operator: sql"SELECT * FROM users WHERE name = #$userName".as[User]. # for SQL fragments, $ for values. #$ treats userName as raw SQL. Should use $ alone. Operator confusion creates injection vulnerability.

Building WHERE Clauses Dynamically with String Operations

Dynamic conditions: val condition = filters.map(f => s"${f.column} = '${f.value}'").mkString(" AND "); sql"SELECT * FROM data WHERE #$condition".as[Data]. filters from user. Map creates SQL strings. # injection. Dynamic query building needs proper parameterization.

Using sql Interpolator with Computed SQL from External Sources

External SQL: val sqlFromConfig = config.getString("custom-query"); sql"#$sqlFromConfig".as[Result]. Configuration or database contains SQL. Not literal string in code. # treats as SQL. If config compromised or user-influenced, SQL injection possible.

Fixes

1

Always Use $ for Values, Never #$ with User Input

Correct parameterization: sql"SELECT * FROM users WHERE email = $email".as[User]. $ creates bind variable. Automatic parameterization. Type-safe values. Never use #$ with user input. # only for literal SQL fragments in code.

2

Use Slick Query API for Complex Dynamic Queries

Type-safe dynamic queries: var query = Users.filter(_.active === true); if (emailFilter.isDefined) query = query.filter(_.email === emailFilter.get); query.result. Compositional query building. Each filter parameterized. Compiler checked. No string building.

3

Use ActionList or DBIO.sequence for Multiple Parameterized Queries

Multiple queries safely: val queries = userIds.map(id => sql"SELECT * FROM users WHERE id = $id".as[User]); DBIO.sequence(queries). Each query parameterized with $. Batch operations type-safe. No string concatenation. Slick handles batching.

4

Validate Identifiers Against Allowlist for Dynamic Schemas

Safe dynamic identifiers: val allowedTables = Set("users", "posts", "comments"); require(allowedTables.contains(tableName)); sql"SELECT * FROM #$tableName WHERE id = $userId".as[Int]. Allowlist validation before #. Can't parameterize identifiers. Explicit validation required.

5

Use Slick's DynamicFilterDSL for User-Defined Filters

Safe filtering: import slick.collection.heterogeneous._; filters.foldLeft(query) { case (q, filter) => q.filter(row => filter.column.asColumnOf[String] === filter.value) }. DSL for dynamic filters. Type-safe composition. Parameterization maintained. Alternative to string building.

6

Scan for Unsafe sql Interpolator Usage

Find #$ patterns: grep -r '#\$' --include="*.scala". Review each usage. Ensure no user input with #. Replace with $ or query API. Static analysis for SQL injection. Code review checking interpolator usage. Automated scanning in CI/CD.

Detect This Vulnerability in Your Code

Sourcery automatically identifies scala slick non-literal sql injection vulnerability and many other security issues in your codebase.

Scan Your Code for Free Explore More Vulnerabilities