PostgreSQL – How to Safely Populate pg_notify Payload

csvjsonpostgresql

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

Best Answer

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;

Related Solutions

PostgreSQL – How to Safely Copy xlog Files

The best (and recommended) way to do it is to configure the database server to copy them via the archive_command configuration variable. See also the chapter Setting up WAL Archiving.

If you are hell bent on copying them manually then another way might be to look at the contents of the archive_status subdirectory. When you see a file in this directory with the suffix .ready it means that the corresponding WAL file is completed and should be safe to copy. However I don't think this would happen unless WAL archiving was turned on in the first place - if it is not turned on then postgres will just delete the WAL files soon after they are written (which will not give you much chance to copy them!)

MySQL – Using LOAD INFILE to Populate Certain Columns

Check this example:

# cat /tmp/db.txt
id,col1,col2
1,23,"dsafsdf"
2,-1,"ghfdhg"
7,9,"strsdrt"

# mysql test
mysql> CREATE TABLE `test` (
       `id` int(11) NOT NULL,
       `mycol1` int(11) DEFAULT NULL,
       `mycol2` varchar(20) DEFAULT NULL,
       PRIMARY KEY (`id`);

mysql> LOAD DATA INFILE '/tmp/db.txt'
       INTO TABLE test FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"'
       IGNORE 1 LINES (id, mycol1, mycol2);

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+--------+---------+
| id | mycol1 | mycol2  |
+----+--------+---------+
|  1 |     23 | dsafsdf |
|  2 |     -1 | ghfdhg  |
|  7 |      9 | strsdrt |
+----+--------+---------+
3 rows in set (0.00 sec)

If you want to specify certain columns:

mysql> TRUNCATE test;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test;
Empty set (0.00 sec)

mysql> LOAD DATA INFILE '/tmp/db.txt'
       INTO TABLE test FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY '"'
       IGNORE 1 LINES (id, @ignore, mycol2);

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+--------+---------+
| id | mycol1 | mycol2  |
+----+--------+---------+
|  1 |   NULL | dsafsdf |
|  2 |   NULL | ghfdhg  |
|  7 |   NULL | strsdrt |
+----+--------+---------+
3 rows in set (0.00 sec)

And you can even set them with your own expressions:

mysql> TRUNCATE test;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE '/tmp/db.txt'
       INTO TABLE test FIELDS TERMINATED BY00 ','
       OPTIONALLY ENCLOSED by '"' IGNORE 1 LINES (id, @var1, mycol2)
       SET mycol1 = @var1 + 1;

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+--------+---------+
| id | mycol1 | mycol2  |
+----+--------+---------+
|  1 |     24 | dsafsdf |
|  2 |      0 | ghfdhg  |
|  7 |     10 | strsdrt |
+----+--------+---------+
3 rows in set (0.00 sec)

However, please understand that a primary key cannot be null nor duplicated, so you cannot set it to NULL unless it gets a default value, for example, because it is an auto_increment column.

Best Answer

Related Solutions

PostgreSQL – How to Safely Copy xlog Files

MySQL – Using LOAD INFILE to Populate Certain Columns

Related Question