Passing table name as a parameter

[hawkaa]

Hi there,

Thanks for a great library. I just swapped from aiopg and I'm impressed with asyncpg so far.

I'm trying to pass a table name as an input parameter, like this:

await conn.fetchrow("select count(*) as count from $1", table)

And getting:

asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "$1"

In psycopg2 you solve it with a special Identifier class as described in this post: https://stackoverflow.com/questions/13793399/passing-table-name-as-a-parameter-in-psycopg2

My thoughts so far:

1. Export and document the sanitizer methods such that one can write code like this: conn.fetchrow(f"select count(*) from {asyncpg.sanitize(table)}). I've seen this earlier, but I forgot which library.
2. Or support the $... syntax in table names also, and either make it work directly with strings, or an Identifier type as used by psycopg
3. Remember to update documentation and/or FAQ.

Let me know what you think!
Håkon

[elprans]

asyncpg does not do query argument interpolation in any form. The query you write gets passed directly to PostgreSQL. This is unlike psycopg2 which has extensive query rewriting/interpolation mechanisms.

[hawkaa]

Okay. That is fine. I know its kind of a niche request. I'm multiplexing requests into different tables and need to make sure the requests cant inject arbitrary SQL to my queries. I solved it with properly validating and restricting the input from my service with regex.

Not sure if we should keep this ticket open or not. It would be a nice feature, but I understand if it doesn't fit.

[elprans]

unlike psycopg2 which has extensive query rewriting/interpolation mechanisms.

This is outside of scope and can be solved by a wrapper library.

[PhazonicRidley]

Do you have any recommendations for said wrapper library?

[pythonweb2]

@PhazonicRidley I have been looking into this, and using the build in postgres function quote_ident() should protect from SQL injection, similar to what the original post had mentioned.

https://www.postgresql.org/docs/11/functions-string.html