Semgrep Registry - python.sqlalchemy.security.audit.avoid-sqlalchemy-text.avoid-sqlalchemy-text

sqlalchemy.text passes the constructed SQL statement to the database mostly unchanged. This means that the usual SQL injection protections are not applied and this function is vulnerable to SQL injection if user input can reach here. Use normal SQLAlchemy operators (such as or_, and_, etc.) to construct SQL.

Defintion

Open in Playground

rules:
  - id: avoid-sqlalchemy-text
    mode: taint
    pattern-sinks:
      - pattern: |
          sqlalchemy.text(...)
    pattern-sources:
      - patterns:
          - pattern: |
              $X + $Y
          - metavariable-type:
              metavariable: $X
              type: string
      - patterns:
          - pattern: |
              $X + $Y
          - metavariable-type:
              metavariable: $Y
              type: string
      - patterns:
          - pattern: |
              f"..."
      - patterns:
          - pattern: |
              $X.format(...)
          - metavariable-type:
              metavariable: $X
              type: string
      - patterns:
          - pattern: |
              $X % $Y
          - metavariable-type:
              metavariable: $X
              type: string
    message: sqlalchemy.text passes the constructed SQL statement to the database
      mostly unchanged. This means that the usual SQL injection protections are
      not applied and this function is vulnerable to SQL injection if user input
      can reach here. Use normal SQLAlchemy operators (such as or_, and_, etc.)
      to construct SQL.
    metadata:
      owasp:
        - A01:2017 - Injection
        - A03:2021 - Injection
      cwe:
        - "CWE-89: Improper Neutralization of Special Elements used in an SQL
          Command ('SQL Injection')"
      category: security
      technology:
        - sqlalchemy
      confidence: MEDIUM
      references:
        - https://docs.sqlalchemy.org/en/14/core/tutorial.html#using-textual-sql
      cwe2022-top25: true
      cwe2021-top25: true
      subcategory:
        - audit
      likelihood: LOW
      impact: LOW
      license: Commons Clause License Condition v1.0[LGPL-2.1-only]
      vulnerability_class:
        - SQL Injection
    languages:
      - python
    severity: ERROR

from sqlalchemy import text @view_config(route_name='home_bad', renderer='my_app:templates/mytemplate.jinja2') def my_bad_home(request): try: param = request.params['foo'] query = request.dbsession.query(models.MyModel) search_non_string = text(5) # ok: avoid-sqlalchemy-text one = query.distinct(search_non_string) search_bind_params = text(":n").bindparams(n=5) # ok: avoid-sqlalchemy-text one = query.distinct(search_bind_params) search_param = text(param) # ok: avoid-sqlalchemy-text one = query.distinct(search_param) search_fixed_string = text("foo") # ok: avoid-sqlalchemy-text one = query.distinct(search_fixed_string) search_param_concat_prefix = "foo" + param # ruleid: avoid-sqlalchemy-text one = query.distinct(text(search_param_concat_prefix)) search_param_concat_suffix = param + "bar" # ruleid: avoid-sqlalchemy-text one = query.distinct(text(search_param_concat_suffix)) search_param_f_string = f"foo{param}bar" # ruleid: avoid-sqlalchemy-text one = query.distinct(text(search_param_f_string)) search_param_format = "foo{}bar".format(param) # ruleid: avoid-sqlalchemy-text one = query.distinct(text(search_param_format)) search_param_percent_format = "foo %s bar" % param # ruleid: avoid-sqlalchemy-text one = query.distinct(text(search_param_percent_format)) except SQLAlchemyError: return Response("Database error", content_type='text/plain', status=500) return {'one': one, 'project': 'my_proj'}

python.sqlalchemy.security.audit.avoid-sqlalchemy-text.avoid-sqlalchemy-text

Run Locally

Run in CI

Defintion

Examples

avoid-sqlalchemy-text.py