python.sqlalchemy.correctness.bad-operator-in-filter.bad-operator-in-filter

profile photo of returntocorpreturntocorp
Author
4,482
Download Count*
LGPL Commons Clause
License

Only comparison operators should be used inside SQLAlchemy filter expressions. Use == instead of is, != instead of is not, sqlalchemy.and_ instead of and, sqlalchemy.or_ instead of or, sqlalchemy.not_ instead of not, and sqlalchemy.in_ instead of in_.

Run Locally

Run in CI

Semgrep CI docs

Defintion

Edit RuleView Source
Open in Playground 
rules:
  - id: bad-operator-in-filter
    languages:
      - python
    message: Only comparison operators should be used inside SQLAlchemy filter
      expressions. Use `==` instead of `is`, `!=` instead of `is not`,
      `sqlalchemy.and_` instead of `and`, `sqlalchemy.or_` instead of `or`,
      `sqlalchemy.not_` instead of `not`, and `sqlalchemy.in_` instead of `in_`.
    metadata:
      references:
        - https://docs.sqlalchemy.org/en/13/orm/tutorial.html#common-filter-operators
      category: correctness
      technology:
        - sqlalchemy
      license: Commons Clause License Condition v1.0[LGPL-2.1-only]
    patterns:
      - pattern-inside: |
          def $ANY(...):
              ...
              $MODEL.query
      - pattern-inside: |
          $TARGET.filter(...)
      - pattern-either:
          - pattern: not $A
          - pattern: $A is $B
          - pattern: $A is not $B
          - pattern: $A and $B
          - pattern: $A or $B
          - pattern: $A in $B
          - pattern: $A not in $B
    severity: WARNING

Examples

bad-operator-in-filter.py

def test_bad_is_1():
    # ruleid:bad-operator-in-filter
    Model.query.filter(Model.id is 5).first()

def test_bad_and_1():
    # ruleid:bad-operator-in-filter
    Model.query.filter(Model.id == 5 and Model.name == 'hi').first()

def test_bad_or_1():
    # ruleid:bad-operator-in-filter
    Model.query.filter(Model.id == 5 or Model.name == 'hi').first()

def test_bad_in_1():
    # ruleid:bad-operator-in-filter
    Model.query.filter(Model.id in [1, 2, 3]).first()

def test_bad_not_1():
    # ruleid:bad-operator-in-filter
    Model.query.filter(not Model.id == 5).first()

def test_bad_not_2():
    # ruleid:bad-operator-in-filter
    Model.query.filter(Model.id is not 5).first()

def test_bad_not_3():
    # ruleid:bad-operator-in-filter
    Model.query.filter(Model.id == 5 and not Model.name == 'hi').first()

def test_bad_not_4():
    # ruleid:bad-operator-in-filter
    Model.query.filter(Model.id == 5 or not Model.name == 'hi').first()

def test_bad_not_5():
    # ruleid:bad-operator-in-filter
    Model.query.filter(Model.id not in [1, 2, 3]).first()

def test_ok_1():
    model = Model.query.first()
    # ok:bad-operator-in-filter
    return model.id is 5