How to prevent SQL injection with user defined json_query path

[nimbian]

As it stands I understand it is not possible to parameterize the path of the JSON path. Is there an appropriate way to handle this to prevent SQL injection?

SELECT JSON_QUERY(data, '$."?-container"') from Disclosures where id = ?

[gordthompson]

I understand it is not possible to parameterize the path of the JSON path

Yes, it is possible. Your issue is that you cannot insert a parameter value inside of a string literal. You need to construct the entire path string and then pass that as the parameter value:

import pyodbc

cnxn = pyodbc.connect(
    "DSN=msodbcsql18_199;UID=scott;PWD=tiger^5HHH;",
    autocommit=True,
)
crsr = cnxn.cursor()

crsr.execute("DROP TABLE IF EXISTS Disclosures")
crsr.execute("CREATE TABLE Disclosures (id int, data nvarchar(max))")
crsr.execute(
    "insert into Disclosures (id, data) values (?, ?)",
    (1, '{"letter-container": ["a", "b"], "number-container": [1, 2]}')
)

for x in ["letter", "number"]:
    print(f"--> {x}")
    path = f'$."{x}-container"'
    print(f"{path=}")
    qry = "SELECT JSON_QUERY(data, ?) from Disclosures where id = ?"
    print(crsr.execute(qry, (path, 1)).fetchone())
"""console output:
--> letter
path='$."letter-container"'
('["a", "b"]',)
--> number
path='$."number-container"'
('[1, 2]',)
"""