semgrep-rules/python/flask/security/injection/tainted-sql-string.py at 180e65d3a388fd7ba5a73ca7705fa7a5df3ad0c5 · semgrep/semgrep-rules · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
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(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()