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

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

User-controlled data from request is passed to 'RawSQL()'. 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

Defintion

rules:
  - id: sql-injection-using-rawsql
    message: User-controlled data from request is passed to 'RawSQL()'. 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/#django.db.models.expressions.RawSQL
      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: django.db.models.expressions.RawSQL(..., $S.format(...,
              request.$W.get(...), ...), ...)
          - pattern: django.db.models.expressions.RawSQL(..., $S % request.$W.get(...), ...)
          - pattern: django.db.models.expressions.RawSQL(...,
              f"...{request.$W.get(...)}...", ...)
          - pattern: django.db.models.expressions.RawSQL(..., request.$W.get(...), ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              django.db.models.expressions.RawSQL(..., $DATA, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $DATA
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: >
              $DATA = request.$W.get(...)

              ...

              django.db.models.expressions.RawSQL(..., $STR.format(..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $STR.format(..., $DATA, ...)
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              django.db.models.expressions.RawSQL(..., $STR % $DATA, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $STR % $DATA
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              django.db.models.expressions.RawSQL(..., f"...{$DATA}...", ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = f"...{$DATA}..."
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              django.db.models.expressions.RawSQL(..., $STR + $DATA, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $STR + $DATA
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: $A = django.db.models.expressions.RawSQL(..., request.$W.get(...), ...)
          - pattern: return django.db.models.expressions.RawSQL(..., request.$W.get(...),
              ...)
          - pattern: django.db.models.expressions.RawSQL(..., $S.format(...,
              request.$W(...), ...), ...)
          - pattern: django.db.models.expressions.RawSQL(..., $S % request.$W(...), ...)
          - pattern: django.db.models.expressions.RawSQL(..., f"...{request.$W(...)}...",
              ...)
          - pattern: django.db.models.expressions.RawSQL(..., request.$W(...), ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              django.db.models.expressions.RawSQL(..., $DATA, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $DATA
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: >
              $DATA = request.$W(...)

              ...

              django.db.models.expressions.RawSQL(..., $STR.format(..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $STR.format(..., $DATA, ...)
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              django.db.models.expressions.RawSQL(..., $STR % $DATA, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $STR % $DATA
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              django.db.models.expressions.RawSQL(..., f"...{$DATA}...", ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = f"...{$DATA}..."
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              django.db.models.expressions.RawSQL(..., $STR + $DATA, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $STR + $DATA
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: $A = django.db.models.expressions.RawSQL(..., request.$W(...), ...)
          - pattern: return django.db.models.expressions.RawSQL(..., request.$W(...), ...)
          - pattern: django.db.models.expressions.RawSQL(..., $S.format(...,
              request.$W[...], ...), ...)
          - pattern: django.db.models.expressions.RawSQL(..., $S % request.$W[...], ...)
          - pattern: django.db.models.expressions.RawSQL(..., f"...{request.$W[...]}...",
              ...)
          - pattern: django.db.models.expressions.RawSQL(..., request.$W[...], ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              django.db.models.expressions.RawSQL(..., $DATA, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $DATA
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: >
              $DATA = request.$W[...]

              ...

              django.db.models.expressions.RawSQL(..., $STR.format(..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $STR.format(..., $DATA, ...)
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              django.db.models.expressions.RawSQL(..., $STR % $DATA, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $STR % $DATA
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              django.db.models.expressions.RawSQL(..., f"...{$DATA}...", ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = f"...{$DATA}..."
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              django.db.models.expressions.RawSQL(..., $STR + $DATA, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $STR + $DATA
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: $A = django.db.models.expressions.RawSQL(..., request.$W[...], ...)
          - pattern: return django.db.models.expressions.RawSQL(..., request.$W[...], ...)
          - pattern: django.db.models.expressions.RawSQL(..., $S.format(..., request.$W,
              ...), ...)
          - pattern: django.db.models.expressions.RawSQL(..., $S % request.$W, ...)
          - pattern: django.db.models.expressions.RawSQL(..., f"...{request.$W}...", ...)
          - pattern: django.db.models.expressions.RawSQL(..., request.$W, ...)
          - pattern: |
              $DATA = request.$W
              ...
              django.db.models.expressions.RawSQL(..., $DATA, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $DATA
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: >
              $DATA = request.$W

              ...

              django.db.models.expressions.RawSQL(..., $STR.format(..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $STR.format(..., $DATA, ...)
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W
              ...
              django.db.models.expressions.RawSQL(..., $STR % $DATA, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $STR % $DATA
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W
              ...
              django.db.models.expressions.RawSQL(..., f"...{$DATA}...", ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = f"...{$DATA}..."
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: |
              $DATA = request.$W
              ...
              django.db.models.expressions.RawSQL(..., $STR + $DATA, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $STR + $DATA
              ...
              django.db.models.expressions.RawSQL(..., $INTERM, ...)
          - pattern: $A = django.db.models.expressions.RawSQL(..., request.$W, ...)
          - pattern: return django.db.models.expressions.RawSQL(..., request.$W, ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              django.db.models.expressions.RawSQL($STR % (..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              django.db.models.expressions.RawSQL($STR % (..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              django.db.models.expressions.RawSQL($STR % (..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W
              ...
              django.db.models.expressions.RawSQL($STR % (..., $DATA, ...), ...)
          - pattern: |
              $DATA = request.$W.get(...)
              ...
              $INTERM = $STR % (..., $DATA, ...)
              ...
              django.db.models.expressions.RawSQL($INTERM, ...)
          - pattern: |
              $DATA = request.$W(...)
              ...
              $INTERM = $STR % (..., $DATA, ...)
              ...
              django.db.models.expressions.RawSQL($INTERM, ...)
          - pattern: |
              $DATA = request.$W[...]
              ...
              $INTERM = $STR % (..., $DATA, ...)
              ...
              django.db.models.expressions.RawSQL($INTERM, ...)
          - pattern: |
              $DATA = request.$W
              ...
              $INTERM = $STR % (..., $DATA, ...)
              ...
              django.db.models.expressions.RawSQL($INTERM, ...)

Examples

sql-injection-rawsql.py

from django.db.models.expressions import RawSQL
from django.http import HttpResponse

##### RawSQL() True Positives #########
def get_user_age(request):
  # ruleid: sql-injection-using-rawsql
  user_name = request.get('user_name')
  user_age = RawSQL('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-rawsql
  user_name = request.get('user_name')
  user_age = RawSQL(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-rawsql
  user_name = request.get('user_name')
  user_age = RawSQL('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-rawsql
  client_id = request.headers.get('client_id')
  users = RawSQL('SELECT * FROM myapp_person where client_id = %s'.format(client_id))
  html = "<html><body>Users %s.</body></html>" % users
  return HttpResponse(html)

def get_users(request):
  # ruleid: sql-injection-using-rawsql
  client_id = request.headers.get('client_id')
  users = RawSQL(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_users(request):
  client_id = request.headers.get('client_id')
  # using param list is ok
  users = RawSQL('SELECT * FROM myapp_person where client_id = %s', (client_id,))
  html = "<html><body>Users %s.</body></html>" % users
  return HttpResponse(html)