java.lang.security.audit.sqli.jpa-sqli.jpa-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: jpa-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: |
                      $TYPE $FUNC(...,String $SQL,...) {
                        ...
                      }
              - pattern-not-inside: |
                  String $SQL = "..." + "...";
                  ...
              - pattern: $EM.$METHOD($SQL,...)
          - pattern: |
              $EM.$METHOD(String.format(...),...);
          - pattern: |
              $EM.$METHOD($X + $Y,...);
      - pattern-either:
          - pattern-inside: |
              EntityManager $EM = ...;
              ...
          - pattern-inside: |
              $TYPE $FUNC(...,EntityManager $EM,...) {
                ...
              }
      - pattern-not: |
          $EM.$METHOD("..." + "...",...);
      - metavariable-regex:
          metavariable: $METHOD
          regex: ^(createQuery|createNativeQuery)$
    metadata:
      cwe:
        - "CWE-89: Improper Neutralization of Special Elements used in an SQL
          Command ('SQL Injection')"
      category: security
      technology:
        - jpa
      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

jpa-sqli.java

package testcode.sqli;

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;

public class JpaSql {

    public void getUserByUsername(EntityManager em, String username) {
        // ruleid:jpa-sqli
        TypedQuery<UserEntity> q = em.createQuery(
                String.format("select * from Users where name = %s", username),
                UserEntity.class);

        UserEntity res = q.getSingleResult();
    }

    public void getUserByUsernameAlt2(EntityManager em, String username) {
        // ruleid:jpa-sqli
        TypedQuery<UserEntity> q = em.createQuery(
                "select * from Users where name = '" + username + "'",
                UserEntity.class);

        UserEntity res = q.getSingleResult();
    }

    public UserEntity getFirst(EntityManager em) {
        // ok:jpa-sqli
        TypedQuery<UserEntity> q = em.createQuery(
                "select * from Users",
                UserEntity.class);
        return q.getSingleResult();
    }

    public UserEntity getFirstAlt2(EntityManager em) {
        final String sql = "select * from Users";
        // ok:jpa-sqli
        TypedQuery<UserEntity> q = (TypedQuery<UserEntity>) em.createQuery(sql);
        return q.getSingleResult();
    }

    public void getUserWithNativeQueryUnsafe(EntityManager em, String password) {
        String sql = "select * from Users where user = 'admin' and password='"+password+"'";
        // ruleid:jpa-sqli
        em.createNativeQuery(sql);
        // ruleid:jpa-sqli
        em.createNativeQuery(sql,"testcode.sqli.UserEntity");
        // ruleid:jpa-sqli
        em.createNativeQuery(sql, UserEntity.class);

    }

    public void getUserWithNativeQuerySafe(EntityManager em) {
        String sql = "select * from Users where user = 'admin'";
        // ok:jpa-sqli
        em.createNativeQuery(sql);
        // ok:jpa-sqli
        em.createNativeQuery(sql,"testcode.sqli.UserEntity");
        // ok:jpa-sqli
        em.createNativeQuery(sql, UserEntity.class);
    }
}