9

I'm developing an aiohttp server application, and I just saw that apparently it isn't able to use SQLAlchemy's ORM layer. So, I was wondering: if my application will only be able to use SQLAlchemy's core, is it still protected against SQL injection attacks?

My code is the following:

async def add_sensor(db_engine, name):
    async with db_engine.acquire() as connection:
        query = model.Sensor.__table__.insert().values(name=name)
        await connection.execute(query)

A comment on the accepted answer in this related question makes me doubt:

you can still use execute() or other literal data that will NOT be escaped by SQLAlchemy.

So, with the execute() used in my code, does the above quote mean that my code is unsafe? And in general: is protection against SQL Injection only possible with the SQLAlchemy ORM layer, as with the Core layer you'll end up launching execute()?

Sander Vanden Hautte
  • 2,138
  • 3
  • 22
  • 36
  • The ORM layer exposes some of the same methods that accept literal SQL, such as `order_by()`, so check the docs when in doubt (allowing users to choose ordering is something that's fairly common). – Ilja Everilä Feb 19 '18 at 18:04

1 Answers1

9

in your example above i dont see any variable beeing supplied to the database query. Since there is no user supplied input there is also no Sql Injection possible.

Even if there would be a user supplied value as long as you dont use handwritten sql statements with sqlalchemy and instead use the orm model approach (model.Sensor.__table__.select()) as can be seen in your example you are secure against Sql Injection.

In the end its all about telling sqlalchemy explicitely what columns and tables should be used to select and insert data from/to and keeping that separate from the data that is beeing inserted or selected. Never combine the data string with the query string and always use sqlalchemy orm model objects to describe your query.

Bad way (Sql Injectable):

Session.execute("select * form users where name = %s" % request.GET['name'])

Good way (Not Sql Injectable):

Session.execute(model.users.__table__.select().where(model.users.name == request.GET['name']))
quantumbyte
  • 379
  • 1
  • 5
  • 1
    You're right, I should've put a code example that *modified* table data - I updated my question. I can't use the ORM layer, but I see what you mean from your code examples, those methods exist in the core layer too. That means my implementation is safe against SQL injection and using the core layer is just fine. Thanks! – Sander Vanden Hautte Feb 19 '18 at 19:55