Data makes its way into my application with prepared statements and stored procedures, so I'm not too worried about injection. When a new user signs up, I want to send an activation email. Presently I'm doing this with a trigger and a notification. It looks something like this:
CREATE FUNCTION on_sign_up() RETURNS trigger as $$ DECLARE BEGIN PERFORM pg_notify('sign_ups', NEW.user_id || ',' || NEW.email || ',' || NEW.activation_code); RETURN new; END; $$ language plpgsql; CREATE TRIGGER on_sign_up_trigger AFTER INSERT ON user_account FOR EACH ROW EXECUTE PROCEDURE on_sign_up();
My problem is the notification payload. If the email contains a comma, then I can't just split the string on ','. How do I escape anything and put it into the payload? I can add my own custom CSV escaping function and invoke it, but any time I think something like that is the right answer, I'm usually missing something.
It doesn't necessarily need to be CSV, I just thought that'd be the easiest.
Note: there are other solutions - e.g. strip the "prefix" and "suffix" and then treat the rest as the email. I'm more interested for this general class of sanitization/escaping from within PostgreSQL itself.
The table I'm trying to extract is as follows:
Table "user_account" Column | Type | Modifiers -----------------+---------+---------------------------- user_id | integer | not null email | citext | not null password_hash | text | not null activated | boolean | not null default false activation_code | uuid | default uuid_generate_v4()