python.django.security.injection.sql.sql-injection-extra.sql-injection-using-extra-where

Community Favorite

semgrep

Author

12,588

Download Count*

License

User-controlled data from a request is passed to 'extra()'. This could lead to a SQL injection and therefore protected information could be leaked. Instead, use parameterized queries or escape the user-controlled data by using params and not using quote placeholders in the SQL string.

Run Locally

Run in CI

Semgrep CI docs

Defintion

Edit Rule View Source

Open in Playground

rules:
  - id: sql-injection-using-extra-where
    message: User-controlled data from a request is passed to 'extra()'. This could
      lead to a SQL injection and therefore protected information could be
      leaked. Instead, use parameterized queries or escape the user-controlled
      data by using `params` and not using quote placeholders in the SQL string.
    metadata:
      cwe:
        - "CWE-89: Improper Neutralization of Special Elements used in an SQL
          Command ('SQL Injection')"
      owasp:
        - A01:2017 - Injection
        - A03:2021 - Injection
      references:
        - https://docs.djangoproject.com/en/3.0/ref/models/expressions/#.objects.extra
      category: security
      technology:
        - django
      cwe2022-top25: true
      cwe2021-top25: true
      subcategory:
        - vuln
      likelihood: MEDIUM
      impact: HIGH
      confidence: MEDIUM
      license: Commons Clause License Condition v1.0[LGPL-2.1-only]
      vulnerability_class:
        - SQL Injection
    languages:
      - python
    severity: WARNING
    patterns:
      - pattern-inside: |
          def $FUNC(...):
            ...
      - pattern-either:
          - pattern: $MODEL.objects.extra(..., where=[..., $S.format(...,
              request.$W.get(...), ...), ...], ...)
          - pattern: $MODEL.objects.extra(..., where=[..., $S % request.$W.get(...), ...],
              ...)
          - pattern: $MODEL.objects.extra(..., where=[..., f"...{request.$W.get(...)}...",
              ...], ...)
          - pattern: $MODEL.objects.extra(..., where=[..., request.$W.get(...), ...], ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $MODEL.objects.extra(..., where=[..., $DATA, ...], ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $DATA
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: >
              $DATA = request.$W.get(...)

              ...

              $MODEL.objects.extra(..., where=[..., $STR.format(..., $DATA, ...), ...], ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $STR.format(..., $DATA, ...)
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $MODEL.objects.extra(..., where=[..., $STR % $DATA, ...], ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $STR % $DATA
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $MODEL.objects.extra(..., where=[..., f"...{$DATA}...", ...], ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = f"...{$DATA}..."
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $MODEL.objects.extra(..., where=[..., $STR + $DATA, ...], ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $STR + $DATA
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: $A = $MODEL.objects.extra(..., where=[..., request.$W.get(...), ...],
              ...)
          - pattern: return $MODEL.objects.extra(..., where=[..., request.$W.get(...), ...],
              ...)
          - pattern: $MODEL.objects.extra(..., where=[..., $S.format(..., request.$W(...),
              ...), ...], ...)
          - pattern: $MODEL.objects.extra(..., where=[..., $S % request.$W(...), ...], ...)
          - pattern: $MODEL.objects.extra(..., where=[..., f"...{request.$W(...)}...", ...],
              ...)
          - pattern: $MODEL.objects.extra(..., where=[..., request.$W(...), ...], ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $MODEL.objects.extra(..., where=[..., $DATA, ...], ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $DATA
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: >
              $DATA = request.$W(...)

              ...

              $MODEL.objects.extra(..., where=[..., $STR.format(..., $DATA, ...), ...], ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $STR.format(..., $DATA, ...)
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $MODEL.objects.extra(..., where=[..., $STR % $DATA, ...], ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $STR % $DATA
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $MODEL.objects.extra(..., where=[..., f"...{$DATA}...", ...], ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = f"...{$DATA}..."
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $MODEL.objects.extra(..., where=[..., $STR + $DATA, ...], ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $STR + $DATA
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: $A = $MODEL.objects.extra(..., where=[..., request.$W(...), ...], ...)
          - pattern: return $MODEL.objects.extra(..., where=[..., request.$W(...), ...],
              ...)
          - pattern: $MODEL.objects.extra(..., where=[..., $S.format(..., request.$W[...],
              ...), ...], ...)
          - pattern: $MODEL.objects.extra(..., where=[..., $S % request.$W[...], ...], ...)
          - pattern: $MODEL.objects.extra(..., where=[..., f"...{request.$W[...]}...", ...],
              ...)
          - pattern: $MODEL.objects.extra(..., where=[..., request.$W[...], ...], ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $MODEL.objects.extra(..., where=[..., $DATA, ...], ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $DATA
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: >
              $DATA = request.$W[...]

              ...

              $MODEL.objects.extra(..., where=[..., $STR.format(..., $DATA, ...), ...], ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $STR.format(..., $DATA, ...)
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $MODEL.objects.extra(..., where=[..., $STR % $DATA, ...], ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $STR % $DATA
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $MODEL.objects.extra(..., where=[..., f"...{$DATA}...", ...], ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = f"...{$DATA}..."
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $MODEL.objects.extra(..., where=[..., $STR + $DATA, ...], ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $STR + $DATA
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: $A = $MODEL.objects.extra(..., where=[..., request.$W[...], ...], ...)
          - pattern: return $MODEL.objects.extra(..., where=[..., request.$W[...], ...],
              ...)
          - pattern: $MODEL.objects.extra(..., where=[..., $S.format(..., request.$W, ...),
              ...], ...)
          - pattern: $MODEL.objects.extra(..., where=[..., $S % request.$W, ...], ...)
          - pattern: $MODEL.objects.extra(..., where=[..., f"...{request.$W}...", ...], ...)
          - pattern: $MODEL.objects.extra(..., where=[..., request.$W, ...], ...)
          - pattern: |
              $DATA = request.$W
              ...
              $MODEL.objects.extra(..., where=[..., $DATA, ...], ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $DATA
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: >
              $DATA = request.$W

              ...

              $MODEL.objects.extra(..., where=[..., $STR.format(..., $DATA, ...), ...], ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $STR.format(..., $DATA, ...)
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: |
              $DATA = request.$W
              ...
              $MODEL.objects.extra(..., where=[..., $STR % $DATA, ...], ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $STR % $DATA
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: |
              $DATA = request.$W
              ...
              $MODEL.objects.extra(..., where=[..., f"...{$DATA}...", ...], ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = f"...{$DATA}..."
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: |
              $DATA = request.$W
              ...
              $MODEL.objects.extra(..., where=[..., $STR + $DATA, ...], ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $STR + $DATA
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: $A = $MODEL.objects.extra(..., where=[..., request.$W, ...], ...)
          - pattern: return $MODEL.objects.extra(..., where=[..., request.$W, ...], ...)
          - pattern: >
              $DATA = request.$W.get(...)

              ...

              $MODEL.objects.extra(..., where=[..., $STR % (..., $DATA, ...), ...], ...)
          - pattern: >
              $DATA = request.$W[...]

              ...

              $MODEL.objects.extra(..., where=[..., $STR % (..., $DATA, ...), ...], ...)
          - pattern: >
              $DATA = request.$W(...)

              ...

              $MODEL.objects.extra(..., where=[..., $STR % (..., $DATA, ...), ...], ...)
          - pattern: >
              $DATA = request.$W

              ...

              $MODEL.objects.extra(..., where=[..., $STR % (..., $DATA, ...), ...], ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $STR % (..., $DATA, ...)
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $STR % (..., $DATA, ...)
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $STR % (..., $DATA, ...)
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $STR % (..., $DATA, ...)
              ...
              $MODEL.objects.extra(..., where=[..., $INTERM, ...], ...)

Examples

sql-injection-extra.py

from django.http import HttpResponse

class Person(models.Model):
    first_name = models.CharField(...)
    last_name = models.CharField(...)
    birth_date = models.DateField(...)

##### extra() True Positives #########
def get_user_age(request):
  # ruleid: sql-injection-using-extra-where
  user_name = request.data.get('user_name')
  user_age = Person.objects.extra(where=["name = %s" % user_name])
  html = "<html><body>User Age %s.</body></html>" % user_age
  return HttpResponse(html)

def get_user_age(request):
  # ruleid: sql-injection-using-extra-where
  user_name = request.data.get('user_name')
  user_age = Person.objects.extra(where=["name = %s" % user_name, "id not NULL"])
  html = "<html><body>User Age %s.</body></html>" % user_age
  return HttpResponse(html)

def get_user_age(request):
  # ruleid: sql-injection-using-extra-where
  path = request.path
  user_age = Person.objects.extra(where=["path = %s" % path])
  html = "<html><body>User Age %s.</body></html>" % user_age
  return HttpResponse(html)

def get_user_age(request):
  # ruleid: sql-injection-using-extra-where
  path = request.path
  user_age = Person.objects.extra(where=[f"path ={path}"])
  html = "<html><body>User Age %s.</body></html>" % user_age
  return HttpResponse(html)


##### extra() True Negative #########
def get_user_age(request):
  # no dataflow
  user_name = request.data.get('user_name')
  user_age = Person.objects.extra(where=["name = 'user_name'"])
  html = "<html><body>User Age %s.</body></html>" % user_age
  return HttpResponse(html)

Short Link: https://sg.run/0Ql5