Show HN: Sqlbind a Python library to compose raw SQL

Re:Show HN: Sqlbind a Python library to compose raw SQL by Retr0id on Monday February 26, 2024 @03:00PM (#39516117)

Proposed safe(r) API via decorated methods:

    QParams = sqlbind.Dialect.some_dialect

    @QParams
    def make_my_query(value1: str, value2: int):
        return f'SELECT * FROM table WHERE field1 = {value1} AND field2 > {value2}'

    data = connection.execute(*make_my_query(foo, bar))

Obviously this wouldn't work as-is, QParams would need to be modified to support this. The decorator would wrap the method, sanitising the args before they're even passed to the wrapped function.

Edit: actually, I might've misunderstood what sqlbind is doing internally, maybe this approach doesn't quite make sense

Edit 2: Like so: https://github.com/baverman/sqlbind/issues/1

[ Parent | on HN ]

Re:Show HN: Sqlbind a Python library to compose raw SQL by chrisjc on Monday February 26, 2024 @03:15PM (#39516249)

If I understand your code correctly, you are bypassing DBAPI's binding functionality and opening yourself up for SQL injection.

    QParams = sqlbind.Dialect.some_dialect

    @QParams
    def make_my_query(value1: str, value2: int):
        # SELECT * FROM table WHERE field1 = ? AND field2 > ?
        return f'SELECT * FROM table WHERE field1 = {value1} AND field2 > {value2}'

    data = connection.execute(*make_my_query(foo, bar), [foo, bar])

[ Parent | on HN ]

Re:Show HN: Sqlbind a Python library to compose raw SQL by Retr0id on Monday February 26, 2024 @03:35PM (#39516461)

Where's the injection? (you might want to take a look at the example in the issue I opened, it's more complete - I'm not bypassing the DBAPI, I'm working with it)

[ Parent | on HN ]

Re:Show HN: Sqlbind a Python library to compose raw SQL by chrisjc on Monday February 26, 2024 @03:55PM (#39516678)

Yup. Your code in github explains what you're suggesting better. My bad.

[ Parent | on HN ]