# 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 

```python
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`:

```sql
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:

```sql
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.

```python
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:

```python
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](https://realpython.com/prevent-python-sql-injection/)