SQLAlchemy 的 ORM 或 Core 中是否内置了 SQL 注入保护? [英] Is SQL injection protection built into SQLAlchemy's ORM or Core?

问题描述

我正在开发一个 aiohttp 服务器 应用程序，我刚刚看到显然它无法使用 SQLAlchemy 的 ORM 层.所以，我想知道:如果我的应用程序只能使用 SQLAlchemy 的核心，它是否仍然可以抵御 SQL 注入攻击?

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?

我的代码如下:

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:

您仍然可以使用 execute() 或其他不会被执行的文字数据被 SQLAlchemy 转义.

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

那么，我的代码中使用了 execute() ，上面的引用是否意味着我的代码不安全?一般而言:是否只能使用 SQLAlchemy ORM 层来防止 SQL 注入，就像使用核心层一样，您最终将启动 execute()?

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()?

推荐答案

在上面的示例中，我没有看到提供给数据库查询的任何变量.由于没有用户提供的输入，因此也不可能进行 Sql 注入.

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.

即使有用户提供的值，只要您不使用 sqlalchemy 的手写 sql 语句，而是尽可能使用 orm 模型方法 (model.Sensor.__table__.select())从您的示例中可以看出，您可以抵御 Sql 注入.

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.

最后，这一切都是关于明确地告诉 sqlalchemy 应该使用哪些列和表来从中选择和插入数据，并将其与正在插入或选择的数据分开.切勿将数据字符串与查询字符串组合在一起，始终使用 sqlalchemy orm 模型对象来描述您的查询.

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.

糟糕的方法(Sql Injectable):

Bad way (Sql Injectable):

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

好方法(不是 Sql 可注入的):

Good way (Not Sql Injectable):

Session.execute(model.users.__table__.select().where(model.users.name == request.GET['name']))

这篇关于SQLAlchemy 的 ORM 或 Core 中是否内置了 SQL 注入保护?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！