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

Community Favorite

semgrep

Author

50,947

Download Count*

License

Possible JDBC injection detected. Use the parameterized query feature available in queryForObject instead of concatenating or formatting strings: 'jdbc.queryForObject("select * from table where name = ?", Integer.class, parameterName);'

Run Locally

Run in CI

Semgrep CI docs

Defintion

Edit Rule View Source

Open in Playground

rules:
  - id: jdbc-sql-formatted-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_SPRING_JDBC
      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"
      category: security
      technology:
        - jdbc
      references:
        - https://owasp.org/Top10/A03_2021-Injection
      cwe2022-top25: true
      cwe2021-top25: true
      subcategory:
        - audit
      likelihood: LOW
      impact: HIGH
      confidence: LOW
      license: Commons Clause License Condition v1.0[LGPL-2.1-only]
      vulnerability_class:
        - SQL Injection
    message: "Possible JDBC injection detected. Use the parameterized query feature
      available in queryForObject instead of concatenating or formatting
      strings: 'jdbc.queryForObject(\"select * from table where name = ?\",
      Integer.class, parameterName);'"
    patterns:
      - pattern-inside: |
          $JDBC = new JdbcTemplate(...);
          ...
      - pattern-either:
          - pattern: $JDBC.queryForObject($STR + $VAR, ...);
          - pattern: $JDBC.queryForObject(String.format(...), ...);
          - pattern: |
              String $Q = $STR + $VAR;
              ...
              $JDBC.queryForObject($Q, ...);
          - pattern: |
              String $Q = String.format(...);
              ...
              $JDBC.queryForObject($Q, ...);
          - pattern: |
              StringBuilder $Q = new StringBuilder(...);
              ...
              $Q.append($STR + $VAR);
              ...
              $JDBC.queryForObject($Q, ...);
          - pattern: $JDBC.queryForList($STR + $VAR);
          - pattern: $JDBC.queryForList(String.format(...));
          - pattern: |
              String $Q = $STR + $VAR;
              ...
              $JDBC.queryForList($Q);
          - pattern: |
              String $Q = String.format(...);
              ...
              $JDBC.queryForList($Q);
          - pattern: |
              StringBuilder $Q = new StringBuilder(...);
              ...
              $Q.append($STR + $VAR);
              ...
              $JDBC.queryForList($Q, ...);
          - pattern: $JDBC.update($STR + $VAR);
          - pattern: $JDBC.update(String.format(...));
          - pattern: |
              String $Q = $STR + $VAR;
              ...
              $JDBC.update($Q);
          - pattern: |
              String $Q = String.format(...);
              ...
              $JDBC.update($Q);
          - pattern: |
              StringBuilder $Q = new StringBuilder(...);
              ...
              $Q.append($STR + $VAR);
              ...
              $JDBC.update($Q, ...);
          - pattern: $JDBC.execute($STR + $VAR);
          - pattern: $JDBC.execute(String.format(...));
          - pattern: |
              String $Q = $STR + $VAR;
              ...
              $JDBC.execute($Q);
          - pattern: |
              String $Q = String.format(...);
              ...
              $JDBC.execute($Q);
          - pattern: |
              StringBuilder $Q = new StringBuilder(...);
              ...
              $Q.append($STR + $VAR);
              ...
              $JDBC.execute($Q, ...);
          - pattern: $JDBC.insert($STR + $VAR);
          - pattern: $JDBC.insert(String.format(...));
          - pattern: |
              String $Q = $STR + $VAR;
              ...
              $JDBC.insert($Q);
          - pattern: |
              String $Q = String.format(...);
              ...
              $JDBC.insert($Q);
          - pattern: |
              StringBuilder $Q = new StringBuilder(...);
              ...
              $Q.append($STR + $VAR);
              ...
              $JDBC.insert($Q, ...);
    severity: WARNING
    languages:
      - java

Examples

jdbc-sql-formatted-string.java

import java.lang.Runtime;

import org.springframework.jdbc.core.JdbcTemplate;

class TestClass {

    public TestClass() {
        System.out.println("Hello");
    }

    public void unsafe_jdbc_queryForObject_1(String paramName) {
        JdbcTemplate jdbc = new JdbcTemplate();
        System.out.println("Hello");
        // ruleid:jdbc-sql-formatted-string
        int count = jdbc.queryForObject("select count(*) from Users where name = '"+paramName+"'", Integer.class);
    }

    public void unsafe_jdbc_queryForObject_2(String paramName) {
        JdbcTemplate jdbc = new JdbcTemplate();
        System.out.println("Hello");
        // ruleid:jdbc-sql-formatted-string
        String query = "select count(*) from Users where name = '"+paramName+"'";
        int count = jdbc.queryForObject(query, Integer.class);
    }

    public void unsafe_jdbc_queryForObject_3(String paramName) {
        JdbcTemplate jdbc = new JdbcTemplate();
        System.out.println("Hello");
        // ruleid:jdbc-sql-formatted-string
        StringBuilder query = new StringBuilder("select count(*) from Users");
        query.append( "where name = '"+paramName+"'");
        int count = jdbc.queryForObject(query, Integer.class);
    }

    public void unsafe_jdbc_queryForList_1(String paramName) {
        JdbcTemplate jdbc = new JdbcTemplate();
        System.out.println("Hello");
        List<Users> users = new ArrayList<>();
        // ruleid:jdbc-sql-formatted-string
        String query = "select count(*) from Users where name = '"+paramName+"'";
        List<Map<String, Object>> rows =  jdbc.queryForList(query);
    }

    public void unsafe_jdbc_queryForList_2(String paramName) {
        JdbcTemplate jdbc = new JdbcTemplate();
        System.out.println("Hello");
        List<Users> users = new ArrayList<>();
        // ruleid:jdbc-sql-formatted-string
        List<Map<String, Object>> rows =  jdbc.queryForList("select count(*) from Users where name = '"+paramName+"'");
    }

    public void unsafe_jdbc_update(String paramName, String paramSalary) {
        JdbcTemplate jdbc = new JdbcTemplate();
        System.out.println("Hello");
        // ruleid:jdbc-sql-formatted-string
        String updateQuery = "update Users set salary = '"+paramSalary+"' where name = '"+paramName+"'";
        jdbc.update(updateQuery);
    }
    public void safe(String paramName) {
        JdbcTemplate jdbc = new JdbcTemplate();
        // ok:jdbc-sql-formatted-string
        int count = jdbc.queryForObject("select count(*) from Users where name = ?", Integer.class, paramName);
    }
}

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