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(_))
}
}
}