scala.lang.security.audit.tainted-sql-string.tainted-sql-string

semgrep

Author

unknown

Download Count*

License

User data flows into this manually-constructed SQL string. User data can be safely inserted into SQL strings using prepared statements or an object-relational mapper (ORM). Manually-constructed SQL strings is a possible indicator of SQL injection, which could let an attacker steal or manipulate data from the database. Instead, use prepared statements (connection.PreparedStatement) or a safe library.

Run Locally

Run in CI

Semgrep CI docs

Defintion

Edit Rule View Source

Open in Playground

rules:
  - id: tainted-sql-string
    languages:
      - scala
    severity: ERROR
    mode: taint
    message: User data flows into this manually-constructed SQL string. User data
      can be safely inserted into SQL strings using prepared statements or an
      object-relational mapper (ORM). Manually-constructed SQL strings is a
      possible indicator of SQL injection, which could let an attacker steal or
      manipulate data from the database. Instead, use prepared statements
      (`connection.PreparedStatement`) or a safe library.
    metadata:
      cwe:
        - "CWE-89: Improper Neutralization of Special Elements used in an SQL
          Command ('SQL Injection')"
      owasp:
        - A01:2017 - Injection
        - A03:2021 - Injection
      references:
        - https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html
      category: security
      technology:
        - scala
      confidence: MEDIUM
      license: Commons Clause License Condition v1.0[LGPL-2.1-only]
      cwe2022-top25: true
      cwe2021-top25: true
      subcategory:
        - vuln
      likelihood: MEDIUM
      impact: HIGH
      vulnerability_class:
        - SQL Injection
    pattern-sources:
      - patterns:
          - pattern: $PARAM
          - pattern-either:
              - pattern-inside: |
                  def $CTRL(..., $PARAM: $TYPE, ...) = {
                    ...
                  }
              - pattern-inside: |
                  def $CTRL(..., $PARAM: $TYPE, ...) = $A {
                    ...
                  }
              - pattern-inside: |
                  def $CTRL(..., $PARAM: $TYPE, ...) = $A(...) {
                    ...
                  }
    pattern-sinks:
      - patterns:
          - pattern-either:
              - patterns:
                  - pattern-either:
                      - pattern: |
                          "$SQLSTR" + ...
                      - pattern: |
                          "$SQLSTR".format(...)
                      - patterns:
                          - pattern-inside: |
                              $SB = new StringBuilder("$SQLSTR");
                              ...
                          - pattern: $SB.append(...)
                      - patterns:
                          - pattern-inside: |
                              $VAR = "$SQLSTR"
                              ...
                          - pattern: $VAR += ...
                  - metavariable-regex:
                      metavariable: $SQLSTR
                      regex: (?i)(select|delete|insert|create|update|alter|drop)\b
              - patterns:
                  - pattern-either:
                      - pattern: s"..."
                      - pattern: f"..."
                  - pattern-regex: >
                      .*\b(?i)(select|delete|insert|create|update|alter|drop)\b.*
          - pattern-not-inside: println(...)
    pattern-sanitizers:
      - pattern-either:
          - patterns:
              - pattern-either:
                  - pattern: $LOGGER.$METHOD(...)
                  - pattern: $LOGGER(...)
              - metavariable-regex:
                  metavariable: $LOGGER
                  regex: (i?)log.*
          - patterns:
              - pattern: $LOGGER.$METHOD(...)
              - metavariable-regex:
                  metavariable: $METHOD
                  regex: (i?)(trace|info|warn|warning|warnToError|error|debug)

Examples

tainted-sql-string.scala

package com.test.test

import java.sql.{Connection, ResultSet, DriverManager}
import org.slf4j.LoggerFactory

object Smth {

  def call1(name: String) = {
    // ruleid: tainted-sql-string
    val sql = "SELECT * FROM table WHERE name = " + name + ";"
    val conn = DriverManager.getConnection("jdbc:mysql://localhost:8080", "guest", "password")
    val stmt = conn.createStatement()
    val rs = stmt.execute(sql)
    rs
  }

  def call2(name: String) = {
    val conn = DriverManager.getConnection("jdbc:mysql://localhost:8080", "guest", "password")
    val stmt = conn.createStatement()
    // ruleid: tainted-sql-string
    val rs = stmt.execute(s"SELECT * FROM table WHERE name = $name;")
    rs
  }

  def call3(name: String) = {
    // ruleid: tainted-sql-string
    val sql = f"SELECT * FROM table WHERE name = $name%s;"
    val conn = DriverManager.getConnection("jdbc:mysql://localhost:8080", "guest", "password")
    val stmt = conn.createStatement()
    val rs = stmt.execute(sql)
    rs
  }

  def call4(name: String) = {
    // ruleid: tainted-sql-string
    val sql = s"SELECT * FROM table WHERE name = ${name};"
    val conn = DriverManager.getConnection("jdbc:mysql://localhost:8080", "guest", "password")
    val stmt = conn.createStatement()
    val rs = stmt.execute(sql)
    rs
  }

  def call5(name: String) = {
    // ruleid: tainted-sql-string
    val sql = s"SELECT * FROM table WHERE name = ${name + "smth"};"
    val conn = DriverManager.getConnection("jdbc:mysql://localhost:8080", "guest", "password")
    val stmt = conn.createStatement()
    val rs = stmt.execute(sql)
    rs
  }

  def okCall1(name: String) = {
    val foobar = getFoobarFromEnv()
    // ok: tainted-sql-string
    val sql = "SELECT * FROM table WHERE name = " + foobar + ";"
    val conn = DriverManager.getConnection("jdbc:mysql://localhost:8080", "guest", "password")
    val stmt = conn.createStatement()
    val rs = stmt.execute(sql)
    rs
  }

  def okCall2(name: String) = {
    val foobar = "Foobar"
    val conn = DriverManager.getConnection("jdbc:mysql://localhost:8080", "guest", "password")
    val stmt = conn.createStatement()
    // ok: tainted-sql-string
    val rs = stmt.execute(s"SELECT * FROM table WHERE name = $foobar;")
    rs
  }

  def okCall3(name: String) = {
    // ok: tainted-sql-string
    println("SELECT * FROM table WHERE name = " + name + ";")
    doSmth(name)
  }

  def loggingCall1(name: String) = {
    val log = LoggerFactory.getLogger(getClass.getSimpleName)
    // ok: tainted-sql-string
    log.trace("Create user" + name)
  }

  def loggingCall2(name: String) = {
    val scribe = LoggerFactory.getLogger(getClass.getSimpleName)
    // ok: tainted-sql-string
    scribe.warnToError("Create user" + name)
  }

  def loggingCall2(name: String) = {
    try {
      do_smth()
    } catch {
      case e: Exception =>
        logWarning(s"Create user $name")
    }
  }
}

Short Link: https://sg.run/ALD6