SQL200.md

SQL200

Looks for SQL injection in the SQLalchemy library.

• Use of text() function to construct parameters on non-literal input
• Use of the .suffix_with() and .prefix_with() methods on a query object with unsafe input

Examples

Use of the SQLalchemy with a text() fragment can expose the constructed query to SQL injection.

For example, this query should generate

part = f"age<{age}"  # exploitable, can override the original filter.
_x = session.query(User).filter(User.username == user).filter(text(part)).all()

With an input of age = 224:

SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname FROM users WHERE users.id = ? AND age < 224 OR 1=1

If the age argument was 224 OR 1=1, the query would bypass the filter:

SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname FROM users WHERE users.id = ? AND id<224 OR 1=1

Both the .suffix_with() and .prefix_with() methods are vulnerable to unsafe input.

suffix = " OR 1=1"  # Example exploiting suffix to add/change WHERE clause
prefix = " *,"  # Example exploiting query to get all fields
stmt = select([users.c.name]).where(users.c.id == 1).suffix_with(suffix, dialect="sqlite")
conn.execute(stmt)

stmt2 = select([addresses]).prefix_with(prefix)  # can be chained
conn.execute(stmt2)

Direct execution of vulnerable queries will be caught by SQL100:

connection.execute("SELECT email_address FROM addresses WHERE email_address = \'{}\'".format(unsafe_input))

Fixes

Replace with native SQLalchemy queries using the API instead of creating direct SQL.

See Also

RealPython.com article on SQL injection