java.lang.security.audit.sqli.jdo-sqli.jdo-sqli

profile photo of returntocorpreturntocorp
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: jdo-sqli
    pattern-either:
      - patterns:
          - pattern-either:
              - patterns:
                  - pattern-either:
                      - pattern-inside: |
                          String $SQL = $X + $Y;
                          ...
                      - pattern-inside: |
                          String $SQL = String.format(...);
                          ...
                      - pattern-inside: |
                          $TYPE $FUNC(...,String $SQL,...) {
                            ...
                          }
                  - pattern-not-inside: |
                      String $SQL = "..." + "...";
                      ...
                  - pattern: $Q.$METHOD($SQL,...)
              - pattern: |
                  $Q.$METHOD(String.format(...),...);
              - pattern: |
                  $Q.$METHOD($X + $Y,...);
          - pattern-either:
              - pattern-inside: |
                  javax.jdo.Query $Q = ...;
                  ...
              - pattern-inside: |
                  $TYPE $FUNC(...,javax.jdo.Query $Q,...) {
                    ...
                  }
          - pattern-not: |
              $Q.$METHOD("..." + "...",...);
          - metavariable-regex:
              metavariable: $METHOD
              regex: ^(setFilter|setGrouping)$
      - 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: $PM.newQuery(...,$SQL,...)
              - pattern: |
                  $PM.newQuery(...,String.format(...),...);
              - pattern: |
                  $PM.newQuery(...,$X + $Y,...);
          - pattern-either:
              - pattern-inside: |
                  javax.jdo.PersistenceManager $PM = ...;
                  ...
              - pattern-inside: |
                  $TYPE $FUNC(...,javax.jdo.PersistenceManager $PM,...) {
                    ...
                  }
          - pattern-not: |
              $PM.newQuery(...,"..." + "...",...);
    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
    metadata:
      cwe:
        - "CWE-89: Improper Neutralization of Special Elements used in an SQL
          Command ('SQL Injection')"
      category: security
      technology:
        - java
      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]

Examples

jdo-sqli.java

package testcode.sqli;

import javax.jdo.Extent;
import javax.jdo.JDOHelper;
import javax.jdo.PersistenceManager;
import javax.jdo.PersistenceManagerFactory;
import javax.jdo.Query;
import java.util.ArrayList;

public class JdoSqlFilter {

    private static final PersistenceManagerFactory pmfInstance =
            JDOHelper.getPersistenceManagerFactory("transactions-optional");

    public static PersistenceManager getPM() {
        return pmfInstance.getPersistenceManager();
    }

    public void testJdoUnsafeFilter(String filterValue) {
        PersistenceManager pm = getPM();
        Query q = pm.newQuery(UserEntity.class);
        // ruleid: jdo-sqli
        q.setFilter("id == "+filterValue);
    }

    public void testJdoSafeFilter(String filterValue) {
        PersistenceManager pm = getPM();
        Query q = pm.newQuery(UserEntity.class);
        // ok: jdo-sqli
        q.setFilter("id == 1");
    }

    public void testJdoSafeFilter2(String filterValue) {
        PersistenceManager pm = getPM();
        Query q = pm.newQuery(UserEntity.class);
        // ok: jdo-sqli
        q.setFilter("id == userId");
        q.declareParameters("int userId");

    }

    private static final String FIELD_TEST = "test";

    public void testJdoUnsafeGrouping(String groupByField) {
        PersistenceManager pm = getPM();
        Query q = pm.newQuery(UserEntity.class);
        // ruleid: jdo-sqli
        q.setGrouping(groupByField);
    }

    public void testJdoSafeGrouping() {
        PersistenceManager pm = getPM();
        Query q = pm.newQuery(UserEntity.class);
        // ok: jdo-sqli
        q.setGrouping(FIELD_TEST);
    }
}

public class JdoSql {

    private static final PersistenceManagerFactory pmfInstance =
            JDOHelper.getPersistenceManagerFactory("transactions-optional");


    public static PersistenceManager getPM() {
        return pmfInstance.getPersistenceManager();
    }

    public void testJdoQueries(String input) {
        PersistenceManager pm = getPM();
        // ruleid: jdo-sqli
        pm.newQuery("select * from Users where name = " + input);
        // ruleid: jdo-sqli
        pm.newQuery("sql", "select * from Products where name = " + input);

        // ok: jdo-sqli
        pm.newQuery("select * from Config");

        final String query = "select * from Config";
        // ok: jdo-sqli
        pm.newQuery(query);
        // ok: jdo-sqli
        pm.newQuery("sql", query);
    }

    public void testJdoQueriesAdditionalMethodSig(String input) {
        PersistenceManager pm = getPM();
        // ruleid: jdo-sqli
        pm.newQuery(UserEntity.class,new ArrayList(),"id == "+ input);
        // ok: jdo-sqli
        pm.newQuery(UserEntity.class,new ArrayList(),"id == 1");
        // ruleid: jdo-sqli
        pm.newQuery(UserEntity.class,"id == "+ input);
        // ok: jdo-sqli
        pm.newQuery(UserEntity.class,"id == 1");
        // ruleid: jdo-sqli
        pm.newQuery((Extent) null,"id == "+input);
        // ok: jdo-sqli
        pm.newQuery((Extent) null,"id == 1");
    }

}