How to properly prevent SQL injection?

[2br-2b]

Say that I want to insert a user-generated string (for example, a username) into a postgres table. Short of encoding strings in Base64 before I store them, what is the best way to ensure that I am properly protected from SQL injection attacks? Is the built-in variable substitution enough, or does AsyncPG offer a sanitize() function?

I saw this in #275:

asyncpg supports native PostgreSQL syntax for parameter substitution:

v = await conn.fetchrow("SELECT * FROM table WHERE id = $1", my_id)

As long as you pass your parameters like that and never build a query string from user inputs, you should be safe from SQL injection.

but since I am using user input as the parameters, I'm not sure if that was covered by "user inputs". Would this be safe:

v = await conn.fetchrow("SELECT * FROM table WHERE username = $1", user_provided_string)

[elprans]

Yes, passing data in arguments is always safe.

This, on the other hand is NOT:

v = await conn.fetchrow(f"SELECT * FROM table WHERE username = '{user_provided_string}'")

[2br-2b]

Thank you for the response earlier! Just to confirm, is this still safe is username could be any Unicode character? Obviously you'd need to sanitize before you displayed the text (especially in a website), but I just want to confirm this

[elprans]

Literally any valid Unicode text.

[2br-2b]

Got it! Thank you!