java.lang.security.audit.formatted-sql-string.formatted-sql-string

Community Favorite
profile photo of returntocorpreturntocorp
Author
73,592
Download Count*

Detected a formatted string in a SQL statement. This could lead to SQL injection if variables in the SQL statement are not properly sanitized. Use a prepared statements (java.sql.PreparedStatement) instead. You can obtain a PreparedStatement using 'connection.prepareStatement'.

Run Locally

Run in CI

Defintion

rules:
  - id: formatted-sql-string
    metadata:
      cwe:
        - "CWE-89: Improper Neutralization of Special Elements used in an SQL
          Command ('SQL Injection')"
      owasp:
        - A01:2017 - Injection
        - A03:2021 - Injection
      source-rule-url: https://find-sec-bugs.github.io/bugs.htm#SQL_INJECTION
      asvs:
        section: "V5: Validation, Sanitization and Encoding Verification Requirements"
        control_id: 5.3.5 Injection
        control_url: https://github.com/OWASP/ASVS/blob/master/4.0/en/0x13-V5-Validation-Sanitization-Encoding.md#v53-output-encoding-and-injection-prevention-requirements
        version: "4"
      references:
        - https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
        - https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html#create_ps
        - https://software-security.sans.org/developer-how-to/fix-sql-injection-in-java-using-prepared-callable-statement
      category: security
      technology:
        - java
      cwe2022-top25: true
      cwe2021-top25: true
      subcategory:
        - vuln
      likelihood: HIGH
      impact: MEDIUM
      confidence: MEDIUM
      license: Commons Clause License Condition v1.0[LGPL-2.1-only]
    message: Detected a formatted string in a SQL statement. This could lead to SQL
      injection if variables in the SQL statement are not properly sanitized.
      Use a prepared statements (java.sql.PreparedStatement) instead. You can
      obtain a PreparedStatement using 'connection.prepareStatement'.
    mode: taint
    pattern-sources:
      - patterns:
          - pattern-either:
              - pattern: |
                  (HttpServletRequest $REQ)
              - patterns:
                  - pattern-inside: |
                      $ANNOT $FUNC (..., $INPUT, ...) {
                        ...
                      }
                  - pattern-either:
                      - pattern: (String $INPUT)
                      - pattern: String.format(..., (String $INPUT), ...)
    pattern-sinks:
      - patterns:
          - pattern-not: $S.$SQLFUNC(<... "=~/.*TABLE *$/" ...>)
          - pattern-not: $S.$SQLFUNC(<... "=~/.*TABLE %s$/" ...>)
          - pattern-either:
              - pattern: (Statement $S).$SQLFUNC(...)
              - pattern: (Connection $C).createStatement(...).$SQLFUNC(...)
              - pattern: (EntityManager $EM).$SQLFUNC(...)
          - metavariable-regex:
              metavariable: $SQLFUNC
              regex: execute|executeQuery|createQuery|query
    severity: ERROR
    languages:
      - java

Examples

formatted-sql-string.java

// cf. https://www.baeldung.com/sql-injection

package sql.injection;

import com.biz.org.AccountDTO;
import com.biz.org.DB;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;

public class SqlExample {
    public void staticQuery() throws SQLException {
        Connection c = DB.getConnection();
        // ok:formatted-sql-string
        ResultSet rs = c.createStatement().executeQuery("SELECT * FROM happy_messages");
    }

    public void getAllFields(String tableName) throws SQLException {
        Connection c = DB.getConnection();
        // ruleid:formatted-sql-string
        ResultSet rs = c.createStatement().executeQuery("SELECT * FROM " + tableName);
    }

    public void findAccountsById(String id) throws SQLException {
        String sql = "SELECT * "
            + "FROM accounts WHERE id = '"
            + id
            + "'";
        Connection c = DB.getConnection();
        // ruleid:formatted-sql-string
        ResultSet rs = c.createStatement().executeQuery(sql);
    }

    public void findAccountsById(String id, String field) throws SQLException {
        String sql = "SELECT ";
        sql += field;
        sql += " FROM accounts WHERE id = '";
        sql += id;
        sql += "'";
        Connection c = DB.getConnection();
        // ruleid:formatted-sql-string
        ResultSet rs = c.createStatement().executeQuery(sql);
    }
}

public class SqlExample2 {
    public void getAllFields(String tableName) throws SQLException {
        Connection c = db.getConnection();
        // ruleid:formatted-sql-string
        ResultSet rs = c.createStatement().execute("SELECT * FROM " + tableName);
    }

    public void findAccountsById(String id) throws SQLException {
        String sql = "SELECT * "
            + "FROM accounts WHERE id = '"
            + id
            + "'";
        Connection c = db.getConnection();
        // ruleid:formatted-sql-string
        ResultSet rs = c.createStatement().execute(sql);
    }

    public List<AccountDTO> findAccountsById(String id) {
        String jql = "from Account where id = '" + id + "'";
        EntityManager em = emfactory.createEntityManager();
        // ruleid:formatted-sql-string
        TypedQuery<Account> q = em.createQuery(jql, Account.class);
        return q.getResultList()
        .stream()
        .map(this::toAccountDTO)
        .collect(Collectors.toList());
    }
}

public class SQLExample3 {
    public void getAllFields(String tableName) throws SQLException {
        Connection c = db.getConnection();
        // ruleid:formatted-sql-string
        ResultSet rs = c.createStatement().execute(String.format("SELECT * FROM %s", tableName);
    }

    public void findAccountsById(String id) throws SQLException {
        String sql = String.format("SELECT * FROM accounts WHERE id = '%s'", id);
        Connection c = db.getConnection();
        // ruleid:formatted-sql-string
        ResultSet rs = c.createStatement().execute(sql);
    }

    public List<AccountDTO> findAccountsById(String id) {
        String jql = String.format("from Account where id = '%s'", id);
        EntityManager em = emfactory.createEntityManager();
        // ruleid: formatted-sql-string
        TypedQuery<Account> q = em.createQuery(jql, Account.class);
        return q.getResultList()
        .stream()
        .map(this::toAccountDTO)
        .collect(Collectors.toList());
    }

    public void findAccountsByIdOk() throws SQLException {
        String id = "const"
        String sql = String.format("SELECT * FROM accounts WHERE id = '%s'", id);
        Connection c = db.getConnection();
        // ok:formatted-sql-string
        ResultSet rs = c.createStatement().execute(sql);
    }

}

public class tableConcatStatements {
    public void tableConcat() {
        // ok:formatted-sql-string
        stmt.execute("DROP TABLE " + tableName);
        stmt.execute(String.format("CREATE TABLE %s", tableName));
    }
}

// This whole operation has nothing to do with SQL
public class FalsePositiveCase {
    private ApiClient apiClient; // imagine an ApiClient class that contains a method named execute

    public void test(String parameter) throws ApiException {
        com.squareup.okhttp.Call call = constructHttpCall(parameter); // Create OKHttp call using parameter from outside
        // ok: formatted-sql-string
        apiClient.execute(call);
        // ok: formatted-sql-string
        apiClient.execute(call);
        apiClient.run(call); // proof that 'execute' name is causing the false-positive
    }
}