search

samuelcolvin / buildpg

Query building for the postgresql prepared statements and asyncpg.
MIT License
85 stars 12 forks source link

SET query with params #34

Closed cmflynn closed 2 years ago

cmflynn commented 2 years ago

I've been trying to find a way to pass params into asyncpg SET query. Can this library support that? Heres the example I tried, which fails with asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "$1"

import asyncio

from buildpg import asyncpg

identity = "arn:aws:sts::123456:assumed-role/thing_1"

async def main():
    async with asyncpg.create_pool_b("postgresql://john:snow@127.0.0.1/my_db") as pool:
        await pool.execute_b("set session iam.identity = :ident", ident=identity)

if __name__ == "__main__":
    asyncio.run(main())
samuelcolvin commented 2 years ago

it's probably because you can't use prepared statements for SET queries, not really an issue with buildpg.

If you trust the value of identify, you can do something like 

await pool.execute_b('set session iam.identity = :ident', ident=RawDangerous(identity))

But this is no more secure from a SQL injection point of view than

await pool.execute_b(f'set session iam.identity = {identity}')