php.doctrine.security.audit.doctrine-dbal-dangerous-query.doctrine-dbal-dangerous-query

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

Detected string concatenation with a non-literal variable in a Doctrine DBAL query method. This could lead to SQL injection if the variable is user-controlled and not properly sanitized. In order to prevent SQL injection, use parameterized queries or prepared statements instead.

Run Locally

Run in CI

Semgrep CI docs

Defintion

Edit RuleView Source
Open in Playground 
rules:
  - id: doctrine-dbal-dangerous-query
    languages:
      - php
    message: Detected string concatenation with a non-literal variable in a Doctrine
      DBAL query method. This could lead to SQL injection if the variable is
      user-controlled and not properly sanitized. In order to prevent SQL
      injection, use parameterized queries or prepared statements instead.
    metadata:
      category: security
      cwe:
        - "CWE-89: Improper Neutralization of Special Elements used in an SQL
          Command ('SQL Injection')"
      license: Commons Clause License Condition v1.0[LGPL-2.1-only]
      owasp:
        - A01:2017 - Injection
        - A03:2021 - Injection
      references:
        - https://www.doctrine-project.org/projects/doctrine-dbal/en/current/reference/security.html
        - https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
      technology:
        - doctrine
      cwe2022-top25: true
      cwe2021-top25: true
      subcategory:
        - audit
      likelihood: LOW
      impact: HIGH
      confidence: LOW
      vulnerability_class:
        - SQL Injection
    patterns:
      - pattern-either:
          - pattern: $CONNECTION->prepare($QUERY,...)
          - pattern: $CONNECTION->createQuery($QUERY,...)
          - pattern: $CONNECTION->executeQuery($QUERY,...)
      - pattern-either:
          - pattern-inside: |
              use Doctrine\DBAL\Connection;
              ...
          - pattern-inside: |
              $CONNECTION = $SMTH->getConnection(...);
              ...
      - pattern-not: $CONNECTION->prepare("...",...)
      - pattern-not: $CONNECTION->createQuery("...",...)
      - pattern-not: $CONNECTION->executeQuery("...",...)
    severity: WARNING

Examples

doctrine-dbal-dangerous-query.php

<?php

class ProductRepository extends ServiceEntityRepository
{
    public function test1(int $price): array
    {
        $conn = $this->getEntityManager()->getConnection();

        $sql = "SELECT * FROM product p WHERE p.price > " . $_GET['cur_price']. " ORDER BY p.price ASC";
        // ruleid: doctrine-dbal-dangerous-query
        $stmt = $conn->prepare($sql);
        $stmt->execute(['price' => $price]);

        return $stmt->fetchAllAssociative();
    }

    public function test2(): array
    {
        $conn = $this->getEntityManager()->getConnection();

        // ruleid: doctrine-dbal-dangerous-query
        $query = $conn->createQuery("SELECT u FROM User u WHERE u.username = '" . $_GET['username'] . "'");
        $data = $query->getResult();
        return $data;
    }

    public function okTest1(int $price): array
    {
        $conn = $this->getEntityManager()->getConnection();
        $sql = "SELECT * FROM users WHERE username = ?";
        // ok: doctrine-dbal-dangerous-query
        $stmt = $conn->prepare($sql);
        $stmt->bindValue(1, $_GET['username']);
        $resultSet = $stmt->executeQuery();
        return $resultSet;
    }

    public function okTest2(int $price): array
    {
        $conn = $this->foobar();
        $sql = "SELECT * FROM users WHERE username = ?";
        // ok: doctrine-dbal-dangerous-query
        $stmt = $conn->prepare($sql);
        $stmt->bindValue(1, $_GET['username']);
        $resultSet = $stmt->executeQuery();
        return $resultSet;
    }

}