How to avoid SQL injection in JSONPATH with SQLAlchemy/SQLModel

Question

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?

score 2 · Accepted Answer · answered Jul 12 '23 at 14:52

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.

How to avoid SQL injection in JSONPATH with SQLAlchemy/SQLModel

1 Answers1