Inserting IPs in a PostgreSQL table

It can be tempting to just use the psql CLI, but using it with untrusted inputs can lead to SQL injection. Better be safe and use SQL prepared statements, that ensure no input can result in arbitrary SQL statements.

We'll use the Deno javascript runtime, that automatically downloads dependencies on first use.

Here we assume that we can connect to a local database which has this table:

CREATE TABLE ips(
    ip VARCHAR(45),
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

We can then run this script:

insert.js

import postgres from "npm:postgres";

// We get the first CLI argument
const ip = Deno.args[0];

// See https://www.npmjs.com/package/postgres#connection
// For connection options
const sql = postgres({
	path: "/run/postgresql/.s.PGSQL.5432"
});

// Parameters are automatically extracted and handled by the database
// so that SQL injection isn't possible.
// Any generic value will be serialized according to an inferred type,
// and replaced by a PostgreSQL protocol placeholder $1, $2, ....
// The parameters are then sent separately to the database which
// handles escaping & casting.
await sql`
	INSERT INTO ips (ip) VALUES(${ ip })
`;

await sql.end();

With the following command:

deno run -A /path/to/insert.js 1.2.3.4

Example

Here's an example reaction configuration using it:

local psql_insert = {
  psql_insert: {
    cmd: [ "deno", "run", "-A", "/path/to/insert.js", "<ip>" ],
  },
};

local banFor(time) = {
  // firewall configuration
};

{
  // ...
  streams: {
    mystream: {
      // ...
      filters: {
        myfilter: {
          // ...
          actions: psql_insert + banFor('48h'),
        },
      },
    }
  },
}