3

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()
5

Rather than worrying about how to escape it. Just use JSON as a transport layer. Here we use row_to_json

CREATE FUNCTION on_sign_up() RETURNS trigger as $$
  DECLARE
  BEGIN
    PERFORM pg_notify('sign_ups', row_to_json(NEW)::text );
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;
11
  • 2
    Exactly what I came here to say. Use a json payload. Jul 14 '17 at 1:14
  • 1
    Life goal accomplished, do exactly what @CraigRinger does somewhere, with something pertaining to PostgreSQL. Jul 14 '17 at 1:33
  • 1
    @user2152081 updated, my bad. Use row_to_json Jul 14 '17 at 2:51
  • 3
    Just cast row_to_json(...)::text Jul 14 '17 at 4:14
  • 2
    @CraigRinger - that works for me, and definitely looks tidier. Thanks for the tip - obviously I'm not so familiar with Postgres... Jul 14 '17 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.