When writing SQL you always want to use placeholders rather than trying to escape text yourself and risk an SQL injection attack.
Postgres provides great functionality for this such as:
SELECT * FROM table WHERE username = $1
Today I was trying to send arbitrary text to a channel via the very powerful
NOTIFY command. However every time I tried to use placeholders I was getting errors (from python’s
asyncpg driver, which is pulling it directly from postgres error code
Eventually, looking through the docs I found this quote:
To send a notification you can also use the function
pg_notify(text, text). The function takes the channel name as the first argument and the payload as the second. The function is much easier to use than the
NOTIFYcommand if you need to work with non-constant channel names and payloads.
So, after wasting an hour trying all sorts of different quoting strategies, I was able to change
NOTIFY $1, $2 into
SELECT pg_notify($1, $2) and resolve the issue.
I’m using SQLAlchemy by the way so it looks something like:
from sqlalchemy import text await session.execute( text("SELECT pg_notify(:channel, :data)") .bindparams(channel="channel", data="my text") )