python.django.security.injection.sql.sql-injection-using-raw.sql-injection-using-raw

Community Favorite

semgrep

Author

12,588

Download Count*

License

Data that is possible user-controlled from a python request is passed to raw(). This could lead to SQL injection and attackers gaining access to protected information. Instead, use django's QuerySets, which are built with query parameterization and therefore not vulnerable to sql injection. For example, you could use Entry.objects.filter(date=2006).

Run Locally

Run in CI

Semgrep CI docs

Defintion

Edit Rule View Source

Open in Playground

rules:
  - id: sql-injection-using-raw
    message: Data that is possible user-controlled from a python request is passed
      to `raw()`. This could lead to SQL injection and attackers gaining access
      to protected information. Instead, use django's QuerySets, which are built
      with query parameterization and therefore not vulnerable to sql injection.
      For example, you could use `Entry.objects.filter(date=2006)`.
    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/topics/security/#sql-injection-protection
      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.raw(..., $S.format(..., request.$W.get(...), ...), ...)
          - pattern: $MODEL.objects.raw(..., $S % request.$W.get(...), ...)
          - pattern: $MODEL.objects.raw(..., f"...{request.$W.get(...)}...", ...)
          - pattern: $MODEL.objects.raw(..., request.$W.get(...), ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $MODEL.objects.raw(..., $DATA, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $DATA
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $MODEL.objects.raw(..., $STR.format(..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $STR.format(..., $DATA, ...)
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $MODEL.objects.raw(..., $STR % $DATA, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $STR % $DATA
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $MODEL.objects.raw(..., f"...{$DATA}...", ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = f"...{$DATA}..."
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $MODEL.objects.raw(..., $STR + $DATA, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $STR + $DATA
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: $A = $MODEL.objects.raw(..., request.$W.get(...), ...)
          - pattern: return $MODEL.objects.raw(..., request.$W.get(...), ...)
          - pattern: $MODEL.objects.raw(..., $S.format(..., request.$W(...), ...), ...)
          - pattern: $MODEL.objects.raw(..., $S % request.$W(...), ...)
          - pattern: $MODEL.objects.raw(..., f"...{request.$W(...)}...", ...)
          - pattern: $MODEL.objects.raw(..., request.$W(...), ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $MODEL.objects.raw(..., $DATA, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $DATA
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $MODEL.objects.raw(..., $STR.format(..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $STR.format(..., $DATA, ...)
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $MODEL.objects.raw(..., $STR % $DATA, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $STR % $DATA
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $MODEL.objects.raw(..., f"...{$DATA}...", ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = f"...{$DATA}..."
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $MODEL.objects.raw(..., $STR + $DATA, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $STR + $DATA
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: $A = $MODEL.objects.raw(..., request.$W(...), ...)
          - pattern: return $MODEL.objects.raw(..., request.$W(...), ...)
          - pattern: $MODEL.objects.raw(..., $S.format(..., request.$W[...], ...), ...)
          - pattern: $MODEL.objects.raw(..., $S % request.$W[...], ...)
          - pattern: $MODEL.objects.raw(..., f"...{request.$W[...]}...", ...)
          - pattern: $MODEL.objects.raw(..., request.$W[...], ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $MODEL.objects.raw(..., $DATA, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $DATA
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $MODEL.objects.raw(..., $STR.format(..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $STR.format(..., $DATA, ...)
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $MODEL.objects.raw(..., $STR % $DATA, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $STR % $DATA
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $MODEL.objects.raw(..., f"...{$DATA}...", ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = f"...{$DATA}..."
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $MODEL.objects.raw(..., $STR + $DATA, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $STR + $DATA
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: $A = $MODEL.objects.raw(..., request.$W[...], ...)
          - pattern: return $MODEL.objects.raw(..., request.$W[...], ...)
          - pattern: $MODEL.objects.raw(..., $S.format(..., request.$W, ...), ...)
          - pattern: $MODEL.objects.raw(..., $S % request.$W, ...)
          - pattern: $MODEL.objects.raw(..., f"...{request.$W}...", ...)
          - pattern: $MODEL.objects.raw(..., request.$W, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $MODEL.objects.raw(..., $DATA, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $DATA
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $MODEL.objects.raw(..., $STR.format(..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $STR.format(..., $DATA, ...)
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $MODEL.objects.raw(..., $STR % $DATA, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $STR % $DATA
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $MODEL.objects.raw(..., f"...{$DATA}...", ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = f"...{$DATA}..."
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $MODEL.objects.raw(..., $STR + $DATA, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $STR + $DATA
              ...
              $MODEL.objects.raw(..., $INTERM, ...)
          - pattern: $A = $MODEL.objects.raw(..., request.$W, ...)
          - pattern: return $MODEL.objects.raw(..., request.$W, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $MODEL.objects.raw($STR % (..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $MODEL.objects.raw($STR % (..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $MODEL.objects.raw($STR % (..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W
              ...
              $MODEL.objects.raw($STR % (..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $STR % (..., $DATA, ...)
              ...
              $MODEL.objects.raw($INTERM, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $STR % (..., $DATA, ...)
              ...
              $MODEL.objects.raw($INTERM, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $STR % (..., $DATA, ...)
              ...
              $MODEL.objects.raw($INTERM, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $STR % (..., $DATA, ...)
              ...
              $MODEL.objects.raw($INTERM, ...)

Examples

sql-injection-using-raw.py

from django.http import HttpResponse

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

##### raw() True Positives #########
def get_user_age(request):
  # ruleid: sql-injection-using-raw
  user_name = request.get('user_name')
  user_age = Person.objects.raw('SELECT user_age FROM myapp_person where user_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-raw
  user_name = request.get('user_name')
  user_age = Person.objects.raw(f"SELECT user_age FROM myapp_person where user_name = {user_name}")
  html = "<html><body>User Age %s.</body></html>" % user_age
  return HttpResponse(html)

def get_user_age(request):
  # ruleid: sql-injection-using-raw
  user_name = request.get('user_name')
  user_age = Person.objects.raw('SELECT user_age FROM myapp_person where user_name = %s'.format(user_name))
  html = "<html><body>User Age %s.</body></html>" % user_age
  return HttpResponse(html)

def get_users(request):
  # ruleid: sql-injection-using-raw
  client_id = request.headers.get('client_id')
  users = Person.objects.raw('SELECT * FROM myapp_person where client_id = %s' % client_id)
  html = "<html><body>Users %s.</body></html>" % users
  return HttpResponse(html)

def get_users(request):
  # ruleid: sql-injection-using-raw
  client_id = request.headers.get('client_id')
  users = Person.objects.raw(f'SELECT * FROM myapp_person where client_id = {client_id}')
  html = "<html><body>Users %s.</body></html>" % users
  return HttpResponse(html)

##### raw() True Negatives #########
def get_user_age(request):
  user_name = request.get('user_name')
  # django queryset is good
  user_age = Person.objects.filter(user_name=user_name).first()
  html = "<html><body>User Age %s.</body></html>" % user_age
  return HttpResponse(html)

def get_users(request):
  client_id = request.headers.get('client_id')
  # using param list is ok
  users = Person.objects.raw('SELECT * FROM myapp_person where client_id = %s', (client_id,))
  html = "<html><body>Users %s.</body></html>" % users
  return HttpResponse(html)

Short Link: https://sg.run/l2v9