python.django.security.injection.sql.sql-injection-using-db-cursor-execute.sql-injection-db-cursor-execute

Community Favorite
profile photo of semgrepsemgrep
Author
12,588
Download Count*

User-controlled data from a request is passed to 'execute()'. This could lead to a SQL injection and therefore protected information could be leaked. 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

Defintion

rules:
  - id: sql-injection-db-cursor-execute
    message: User-controlled data from a request is passed to 'execute()'. This
      could lead to a SQL injection and therefore protected information could be
      leaked. 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: $CURSOR.execute(..., $S.format(..., request.$W.get(...), ...), ...)
          - pattern: $CURSOR.execute(..., $S % request.$W.get(...), ...)
          - pattern: $CURSOR.execute(..., f"...{request.$W.get(...)}...", ...)
          - pattern: $CURSOR.execute(..., request.$W.get(...), ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $CURSOR.execute(..., $DATA, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $DATA
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $CURSOR.execute(..., $STR.format(..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $STR.format(..., $DATA, ...)
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $CURSOR.execute(..., $STR % $DATA, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $STR % $DATA
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $CURSOR.execute(..., f"...{$DATA}...", ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = f"...{$DATA}..."
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $CURSOR.execute(..., $STR + $DATA, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $STR + $DATA
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: $A = $CURSOR.execute(..., request.$W.get(...), ...)
          - pattern: return $CURSOR.execute(..., request.$W.get(...), ...)
          - pattern: $CURSOR.execute(..., $S.format(..., request.$W(...), ...), ...)
          - pattern: $CURSOR.execute(..., $S % request.$W(...), ...)
          - pattern: $CURSOR.execute(..., f"...{request.$W(...)}...", ...)
          - pattern: $CURSOR.execute(..., request.$W(...), ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $CURSOR.execute(..., $DATA, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $DATA
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $CURSOR.execute(..., $STR.format(..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $STR.format(..., $DATA, ...)
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $CURSOR.execute(..., $STR % $DATA, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $STR % $DATA
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $CURSOR.execute(..., f"...{$DATA}...", ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = f"...{$DATA}..."
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $CURSOR.execute(..., $STR + $DATA, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $STR + $DATA
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: $A = $CURSOR.execute(..., request.$W(...), ...)
          - pattern: return $CURSOR.execute(..., request.$W(...), ...)
          - pattern: $CURSOR.execute(..., $S.format(..., request.$W[...], ...), ...)
          - pattern: $CURSOR.execute(..., $S % request.$W[...], ...)
          - pattern: $CURSOR.execute(..., f"...{request.$W[...]}...", ...)
          - pattern: $CURSOR.execute(..., request.$W[...], ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $CURSOR.execute(..., $DATA, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $DATA
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $CURSOR.execute(..., $STR.format(..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $STR.format(..., $DATA, ...)
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $CURSOR.execute(..., $STR % $DATA, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $STR % $DATA
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $CURSOR.execute(..., f"...{$DATA}...", ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = f"...{$DATA}..."
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $CURSOR.execute(..., $STR + $DATA, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $STR + $DATA
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: $A = $CURSOR.execute(..., request.$W[...], ...)
          - pattern: return $CURSOR.execute(..., request.$W[...], ...)
          - pattern: $CURSOR.execute(..., $S.format(..., request.$W, ...), ...)
          - pattern: $CURSOR.execute(..., $S % request.$W, ...)
          - pattern: $CURSOR.execute(..., f"...{request.$W}...", ...)
          - pattern: $CURSOR.execute(..., request.$W, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $CURSOR.execute(..., $DATA, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $DATA
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $CURSOR.execute(..., $STR.format(..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $STR.format(..., $DATA, ...)
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $CURSOR.execute(..., $STR % $DATA, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $STR % $DATA
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $CURSOR.execute(..., f"...{$DATA}...", ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = f"...{$DATA}..."
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $CURSOR.execute(..., $STR + $DATA, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $STR + $DATA
              ...
              $CURSOR.execute(..., $INTERM, ...)
          - pattern: $A = $CURSOR.execute(..., request.$W, ...)
          - pattern: return $CURSOR.execute(..., request.$W, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $CURSOR.execute($STR % (..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $CURSOR.execute($STR % (..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $CURSOR.execute($STR % (..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W
              ...
              $CURSOR.execute($STR % (..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $STR % (..., $DATA, ...)
              ...
              $CURSOR.execute($INTERM, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $STR % (..., $DATA, ...)
              ...
              $CURSOR.execute($INTERM, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $STR % (..., $DATA, ...)
              ...
              $CURSOR.execute($INTERM, ...)
          - pattern: |-
              $DATA = request.$W
              ...
              $INTERM = $STR % (..., $DATA, ...)
              ...
              $CURSOR.execute($INTERM, ...)

Examples

sql-injection-using-db-cursor-execute.py

from django.db import connection

##### True Positives #########
def fetch_name_0(request):
  with connection.cursor() as cursor:
      # ruleid: sql-injection-db-cursor-execute
      cursor.execute(f"SELECT foo FROM bar WHERE baz = {request.data.get('baz')}")
      # ruleid: sql-injection-db-cursor-execute
      cursor.execute("SELECT foo FROM bar WHERE baz = %s" % request.data.get('baz'))
      # ruleid: sql-injection-db-cursor-execute
      cursor.execute("SELECT foo FROM bar WHERE baz = %s".format(request.data.get('baz')))
      row = cursor.fetchone()
  return row

def fetch_name_1(request):
  # ruleid: sql-injection-db-cursor-execute
  baz = request.data.get("baz")
  with connection.cursor() as cursor:
      cursor.execute(f"UPDATE bar SET foo = 1 WHERE baz = {baz}")
      cursor.execute(f"SELECT foo FROM bar WHERE baz = {baz}")
      row = cursor.fetchone()
  return row

def fetch_name_2(request):
  # ruleid: sql-injection-db-cursor-execute
  baz = request.data.get("baz")
  with connection.cursor() as cursor:
      cursor.execute("SELECT foo FROM bar WHERE baz = %s" % baz)
      row = cursor.fetchone()
  return row

def fetch_name_3(request):
  # ruleid: sql-injection-db-cursor-execute
  baz = request.data.get("baz")
  with connection.cursor() as cursor:
      cursor.execute("SELECT foo FROM bar WHERE baz = %s".format(baz))
      row = cursor.fetchone()
  return row

def upload(request, project_id):

    if request.method == 'POST':

        proj = Project.objects.get(pk=project_id)
        form = ProjectFileForm(request.POST, request.FILES)

        if form.is_valid():
            # Dependent on feature in develop
            # todoruleid: sql-injection-db-cursor-execute
            name = request.POST.get('name', False)
            upload_path = store_uploaded_file(name, request.FILES['file'])

            other_name = "{}".format(name)
            curs = connection.cursor()
            curs.execute(
                "insert into taskManager_file ('name','path','project_id') values ('%s','%s',%s)" %
                (other_name, upload_path, project_id))


##### True Negatives #########
def fetch_name_4(request):
  # using param list is ok
  baz = request.data.get("baz")
  with connection.cursor() as cursor:
      cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [baz])
      cursor.execute("SELECT foo FROM bar WHERE baz = %s", [baz])
      row = cursor.fetchone()

  return row