python.flask.security.injection.tainted-sql-string.tainted-sql-string

returntocorp

Author

unknown

Download Count*

License

Detected user input used to manually construct a SQL string. This is usually bad practice because manual construction could accidentally result in a SQL injection. An attacker could use a SQL injection to steal or modify contents of the database. Instead, use a parameterized query which is available by default in most database engines. Alternatively, consider using an object-relational mapper (ORM) such as SQLAlchemy which will protect your queries.

Run Locally

Run in CI

Semgrep CI docs

Defintion

Edit Rule View Source

Open in Playground

rules:
  - id: tainted-sql-string
    message: Detected user input used to manually construct a SQL string. This is
      usually bad practice because manual construction could accidentally result
      in a SQL injection. An attacker could use a SQL injection to steal or
      modify contents of the database. Instead, use a parameterized query which
      is available by default in most database engines. Alternatively, consider
      using an object-relational mapper (ORM) such as SQLAlchemy which will
      protect your queries.
    metadata:
      cwe:
        - "CWE-704: Incorrect Type Conversion or Cast"
      owasp:
        - A01:2017 - Injection
        - A03:2021 - Injection
      references:
        - https://docs.sqlalchemy.org/en/14/core/tutorial.html#using-textual-sql
        - https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_quick_guide.htm
        - https://docs.sqlalchemy.org/en/14/core/tutorial.html#using-more-specific-text-with-table-expression-literal-column-and-expression-column
      category: security
      technology:
        - sqlalchemy
        - flask
      subcategory:
        - vuln
      impact: MEDIUM
      likelihood: MEDIUM
      confidence: MEDIUM
      license: Commons Clause License Condition v1.0[LGPL-2.1-only]
    severity: ERROR
    languages:
      - python
    mode: taint
    pattern-sources:
      - patterns:
          - pattern-either:
              - pattern: flask.request.$ANYTHING
              - patterns:
                  - pattern-inside: |
                      @$APP.route(...)
                      def $FUNC(..., $ROUTEVAR, ...):
                        ...
                  - pattern: $ROUTEVAR
    pattern-sinks:
      - patterns:
          - pattern-either:
              - pattern: |
                  "$SQLSTR" + ...
              - pattern: |
                  "$SQLSTR" % ...
              - pattern: |
                  "$SQLSTR".format(...)
              - pattern: |
                  f"$SQLSTR{...}..."
          - metavariable-regex:
              metavariable: $SQLSTR
              regex: \s*(?i)(select|delete|insert|create|update|alter|drop)\b.*

Examples

tainted-sql-string.py

import os
import flask
import hashlib
import requests
from flask_sqlalchemy import SQLAlchemy

app = flask.Flask(__name__)
engine = SQLAlchemy()

@app.route("/insert/person")
def insert_person():
    name = flask.request.args.get("name")
    lastname = "you don't get to pick >:)"

    # String concatenation using + operator
    # ruleid: tainted-sql-string
    engine.execute("INSERT INTO person (name) VALUES ('" + name + "')")

    # ruleid: tainted-sql-string
    engine.execute("INSERT INTO person (firstname, lastname) VALUES ('" + name + "','" + lastname + "')")

    # ok: tainted-sql-string
    engine.execute("INSERT INTO person (name) VALUES ('" + lastname +"')")

    # Format strings with %
    # ruleid: tainted-sql-string
    engine.execute("INSERT INTO person (name) VALUES ('%s')" % (name))

    # ruleid: tainted-sql-string
    engine.execute("INSERT INTO person (name) VALUES ('%s')" % (flask.request.args.get("name")))

    # ok: tainted-sql-string
    engine.execute("INSERT INTO person (name) VALUES ('%s')" % (lastname))

    # Format strings with .format
    # ruleid: tainted-sql-string
    engine.execute("INSERT INTO person (name) VALUES ('{}')".format(name))

    # Format strings  using fstrings
    # ruleid: tainted-sql-string
    engine.execute(f"SELECT FROM person WHERE name='{name}'")

    # Query without concatenation
    # ok: tainted-sql-string
    engine.execute("INSERT INTO person (name) VALUES ('Frodon Sacquet')")

    # Query using prepared statement with named parameters
    # ok: tainted-sql-string
    stmt = text("INSERT INTO table (name) VALUES(:name)")
    engine.execute(stmt, name=name)

    # SQL Composition and prepared statement
    # ok: tainted-sql-string
    query = select(literal_column("users.fullname", String) + ', ' + literal_column("addresses.email_address").label("title")).where(and_(literal_column("users.id") == literal_column("addresses.user_id"), text("users.name BETWEEN 'm' AND 'z'"), text("(addresses.email_address LIKE :x OR addresses.email_address LIKE :y)"))).select_from(table('users')).select_from(table('addresses'))
    engine.execute(query, {"x":"%@aol.com", "y":name}).fetchall()

@app.route("/insert/person/path")
def insert_person(path):
    name = path
    lastname = "you don't get to pick >:)"

    # String concatenation using + operator
    # ruleid: tainted-sql-string
    engine.execute("INSERT INTO person (name) VALUES ('" + name + "')")

    # ruleid: tainted-sql-string
    engine.execute("INSERT INTO person (firstname, lastname) VALUES ('" + name + "','" + lastname + "')")

    # ok: tainted-sql-string
    engine.execute("INSERT INTO person (name) VALUES ('" + lastname +"')")

    # Format strings with %
    # ruleid: tainted-sql-string
    engine.execute("INSERT INTO person (name) VALUES ('%s')" % (name))

    # ruleid: tainted-sql-string
    engine.execute("INSERT INTO person (name) VALUES ('%s')" % (flask.request.args.get("name")))

    # ok: tainted-sql-string
    engine.execute("INSERT INTO person (name) VALUES ('%s')" % (lastname))

    # Format strings with .format
    # ruleid: tainted-sql-string
    engine.execute("INSERT INTO person (name) VALUES ('{}')".format(name))

    # Format strings  using fstrings
    # ruleid: tainted-sql-string
    engine.execute(f"SELECT FROM person WHERE name='{name}'")

    # Query without concatenation
    # ok: tainted-sql-string
    engine.execute("INSERT INTO person (name) VALUES ('Frodon Sacquet')")

    # Query using prepared statement with named parameters
    # ok: tainted-sql-string
    stmt = text("INSERT INTO table (name) VALUES(:name)")
    connection.execute(stmt, name=name)

    # SQL Composition and prepared statement
    # ok: tainted-sql-string
    query = select(literal_column("users.fullname", String) + ', ' + literal_column("addresses.email_address").label("title")).where(and_(literal_column("users.id") == literal_column("addresses.user_id"), text("users.name BETWEEN 'm' AND 'z'"), text("(addresses.email_address LIKE :x OR addresses.email_address LIKE :y)"))).select_from(table('users')).select_from(table('addresses'))
    engine.execute(query, {"x":"%@aol.com", "y":name}).fetchall()

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