SQLAlchemy + SQL Injection

I'm inclined to agree with @Tendrid's answer.

If you write this:

session.query(MyClass).filter("foo={}".format(getArgs['val']))

... you are creating an injection vulnerability.

SqlAlchemy's approach is to use bound parameters to avoid these injection attacks. The way you're meant to use filter() is to write:

session.query(MyClass).filter(MyClass.foo == getArgs['va'])

As SqlAlchemy has overloaded python's operators like == to escape the SQL correctly (and avoid injection). See here

There is a warning about this buried in the documentation of SqlAlchemy here that says:

Always use bound parameters

As mentioned at the beginning of this section, textual SQL is not the usual way we work with SQLAlchemy. However, when using textual SQL, a Python literal value, even non-strings like integers or dates, should never be stringified into SQL string directly; a parameter should always be used. This is most famously known as how to avoid SQL injection attacks when the data is untrusted. However it also allows the SQLAlchemy dialects and/or DBAPI to correctly handle the incoming input for the backend. Outside of plain textual SQL use cases, SQLAlchemy’s Core Expression API otherwise ensures that Python literal values are passed as bound parameters where appropriate.

And there's a section in the glossary on bound parameters here

It says:

Bound parameters are the primary means in which data is passed to the DBAPI database driver. While the operation to be invoked is based on the SQL statement string, the data values themselves are passed separately, where the driver contains logic that will safely process these strings and pass them to the backend database server, which may either involve formatting the parameters into the SQL string itself, or passing them to the database using separate protocols.

The specific system by which the database driver does this should not matter to the caller; the point is that on the outside, data should always be passed separately and not as part of the SQL string itself. This is integral both to having adequate security against SQL injections as well as allowing the driver to have the best performance.

Basically that means this:

session.query(MyClass).filter("foo={}".format(getArgs['val']))

... is broken because you're passing the data to the filter() together with the SQL statement foo=<data>.

You're meant to always keep the statement and data separate, i.e.:

session.query(MyClass).filter(MyClass.foo == getArgs['va'])

or

session.query(MyClass).filter_by(foo=getArgs['va'])

As then SqlAlchemy can work it's magic and do the escaping with bound parameters.