java.lang.security.audit.sqli.jdbc-sqli.jdbc-sqli

profile photo of semgrepsemgrep
Author
649
Download Count*
LGPL Commons Clause
License

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

Semgrep CI docs

Defintion

Edit RuleView Source
Open in Playground 
rules:
  - id: jdbc-sqli
    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'.
    languages:
      - java
    severity: WARNING
    patterns:
      - pattern-either:
          - patterns:
              - pattern-either:
                  - pattern-inside: |
                      String $SQL = $X + $Y;
                      ...
                  - pattern-inside: |
                      String $SQL = String.format(...);
                      ...
                  - pattern-inside: |
                      $VAL $FUNC(...,String $SQL,...) {
                        ...
                      }
              - pattern-not-inside: |
                  String $SQL = "..." + "...";
                  ...
              - pattern: $S.$METHOD($SQL,...)
          - pattern: |
              $S.$METHOD(String.format(...),...);
          - pattern: |
              $S.$METHOD($X + $Y,...);
      - pattern-either:
          - pattern-inside: |
              java.sql.Statement $S = ...;
              ...
          - pattern-inside: |
              $TYPE $FUNC(...,java.sql.Statement $S,...) {
                ...
              }
      - pattern-not: |
          $S.$METHOD("..." + "...",...);
      - metavariable-regex:
          metavariable: $METHOD
          regex: ^(executeQuery|execute|executeUpdate|executeLargeUpdate|addBatch|nativeSQL)$
    metadata:
      cwe:
        - "CWE-89: Improper Neutralization of Special Elements used in an SQL
          Command ('SQL Injection')"
      category: security
      technology:
        - jdbc
      owasp:
        - A01:2017 - Injection
        - A03:2021 - Injection
      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

Examples

jdbc-sqli.java

package testcode.sqli;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Jdbc {

    Connection con;

    public void query1(String input) throws SQLException {
        Statement stmt = con.createStatement();
        // ruleid: jdbc-sqli
        ResultSet rs = stmt.executeQuery("select * from Users where name = '"+input+"'");
    }

    public void query2(String input) throws SQLException {
        Statement stmt = con.createStatement();
        String sql = "select * from Users where name = '" + input + "'";
        // ruleid: jdbc-sqli
        ResultSet rs = stmt.executeQuery(sql);
    }

    public void query3(String input) throws SQLException {
        Statement stmt = con.createStatement();
        // ruleid: jdbc-sqli
        ResultSet rs = stmt.executeQuery(String.format("select * from Users where name = '%s'",input));
        // ok: jdbc-sqli
        ResultSet rs2 = stmt.executeQuery("select * from Users where name = '123'");
    }

    public void query4(String input) throws SQLException {
        Statement stmt = con.createStatement();
        String sql = "select * from Users where name = '%s'";
        // ruleid: jdbc-sqli
        ResultSet rs = stmt.executeQuery(String.format(sql,input));
    }

    public void executeQuerySamples(String sql) throws SQLException {
        Statement stmt = con.createStatement();
        // ruleid: jdbc-sqli
        stmt.executeQuery(sql);
        // ruleid: jdbc-sqli
        stmt.execute(sql);
        // ruleid: jdbc-sqli
        stmt.execute(sql, Statement.RETURN_GENERATED_KEYS);
        // ruleid: jdbc-sqli
        stmt.execute(sql, new int[]{1, 2, 3});
        // ruleid: jdbc-sqli
        stmt.execute(sql, new String[]{"firstname", "middlename", "lastname"});
    }

    public void executeUpdateSamples(String sql) throws SQLException {
        Statement stmt = con.createStatement();
        // ok: jdbc-sqli
        stmt.executeUpdate("select * from Users where name = '123'");
        // ruleid: jdbc-sqli
        stmt.executeUpdate(sql);
        // ruleid: jdbc-sqli
        stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        // ruleid: jdbc-sqli
        stmt.executeUpdate(sql, new int[]{1, 2, 3});
        // ruleid: jdbc-sqli
        stmt.executeUpdate(sql, new String[]{"firstname", "middlename", "lastname"});
    }


    public void executeExecuteLargeUpdateSamples(String sql) throws SQLException {
        Statement stmt = con.createStatement();
        // ruleid: jdbc-sqli
        stmt.executeLargeUpdate(sql);
        // ruleid: jdbc-sqli
        stmt.executeLargeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        // ruleid: jdbc-sqli
        stmt.executeLargeUpdate(sql, new int[]{1, 2, 3});
        // ruleid: jdbc-sqli
        stmt.executeLargeUpdate(sql, new String[]{"firstname", "middlename", "lastname"});
    }

    public void otherSamples(String sql) throws SQLException {
        con.nativeSQL(sql);
        Statement stmt = con.createStatement();
        // ruleid: jdbc-sqli
        stmt.addBatch(sql);
        String sqlString = "select * from Users where name = '123'";
        // ok: jdbc-sqli
        stmt.addBatch(sqlString);
    }

}