如何在SQLAlchemy/SQLModel中避免JSONPATH的SQL注入。

huangapple go评论53阅读模式
英文:

How to avoid SQL injection in JSONPATH with SQLAlchemy/SQLModel

问题

我想在SQLModel查询的JSON路径部分避免SQL注入。到目前为止,我还没有找到一种安全提供JSON路径部分参数的解决方案。

假设我们有一个类似这样的数据库访问函数:

from sqlmodel import Session, func, select

def some_fn(session: Session, value: str) -> list[DbItem]:
    statement = select(DbItem).where(
        func.jsonb_path_exists(
            DbItem.json_field,
            f'$[*] ? (@.id == "stg") ? (@.val == "{value}")',
        ),
    )

    return db.exec(statement).all()

然后我们可以使用以下值调用此函数:value='" || ""=="',这将评估为真。

在这种情况下,首选的解决方案是什么?

英文:

I would like to avoid SQL injection in JSON path parts of queries with SQLModel. I haven't found a solution to provide parameters for the JSON path part in a safe way yet.

Let's say we have a database access function like this one:

from sqlmodel import Session, func, select


def some_fn(session: Session, value: str) -> list[DbItem]:
    statement = select(DbItem).where(
        func.jsonb_path_exists(
            DbItem.json_field,
            f'$[*] ? (@.id == "stg") ? (@.val == "{value}")',
        ),
    )

    return db.exec(statement).all()

Then we can call this function with the following value: value='" || ""=="' which will evaluate to true.

What is the preferred solution to avoid injection in this case?

答案1

得分: 2

这样做的规范方法是在JSONPATH表达式中使用变量,例如

jsonb_path_exists(
   a_jsonb,
   '$[*] ? (@.id == "stg") ? (@.val == $value)',
   jsonb_build_object('value', 'something')
)

您可以使用查询参数代替常量 'something',这样您就不必组成字符串文字。

英文:

The canonical way to do this is to use variables in JSONPATH expressions, like

jsonb_path_exists(
   a_jsonb,
   '$[*] ? (@.id == "stg") ? (@.val == $value)',
   jsonb_build_object('value', 'something')
)

You can use a query parameter instead of the constant 'something', then you don't have to compose a string literal.

huangapple
  • 本文由 发表于 2023年7月12日 21:39:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/76671219.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定