1

I want to use the SQLAlchemy filter_by function, but cannot find an explicit statement that the filter_by function escapes input to protect against SQL injection. Our usage is very simple but obviosuly very sensitive.

 self.database.active_session()
        .query(Users)
        .filter_by(
            username=username,
            is_active=1
        ).first()

Where can I read more about the filter_by function, or can anyone tell me whether it escapes input?

user3454396
  • 403
  • 4
  • 11
  • 1
    SQLAlchemy delegates the quoting of values to the underlying DB-API connector package used by the engine, so the protection is as good as that offered by the connector package. For example, if your engine url is `mysql+pymysql://user:pass@host/db` then the quoting is done by pymysql. – snakecharmerb Jan 04 '22 at 18:46
  • You can dig into [sources](https://github.com/zzzeek/sqlalchemy/blob/fc5c54fcd4d868c2a4c7ac19668d72f506fe821e/lib/sqlalchemy/orm/query.py#L1670) to see how it works. – Olvin Roght Jan 04 '22 at 18:47

1 Answers1

2

Here is the output for postgresql connection

# trying to inject
query = db.query(User).filter_by(username="'user'; drop table user; --"")
print(query)

Output

SELECT "user".id AS user_id, "user".created_at AS user_created_at, "user".updated_at AS user_updated_at, "user".username AS user_username, "user".hashed_password AS user_hashed_password, "user".is_active AS user_is_active, "user".is_superuser AS user_is_superuser 
FROM "user" 
WHERE "user".username = %(username_1)s

String value passes as param to query, so you are protected from SQL injection.

Yevhen Bondar
  • 4,357
  • 1
  • 11
  • 31