workspace_templates_sql_table_insert.js

/**
## /workspace/templates/sql_table_insert

The sql_table_insert query template module allows to insert multiple records provided as a request POST body into a SQL table.

The POST body must contain properties for each field. The key will be split on :: to provide the field name and the cast for values in the values array.
```
{
  "letters::varchar": [
    "a",
    "b"
  ],
  "drop table (table)": [1,2], // Potential SQL Injection
  "numbers::int": [
    1,
    2
  ]
}
```

The field name and the cast must be SQL identifiers. Properties with other keys are skipped.

The values are substituted as query params. The params are assigned with a `body_` prefix, so a body key can't overwrite a param of the template, eg. the table.

@module /workspace/templates/sql_table_insert
*/

/**
Field names and casts [body key parts] must be SQL identifiers.
*/
const IDENTIFIER = /^[A-Za-z_][A-Za-z0-9_]*$/;

export default (_) => {
  const fields = [];
  const unnests = [];

  for (const [key, value] of Object.entries(_.body)) {
    // Split key into field and type array
    const field_type = key.split('::');

    // Check for SQL Injection in the property key.
    if (
      field_type.length !== 2 ||
      field_type.some((string) => !IDENTIFIER.test(string))
    ) {
      console.warn(`Potential SQL Injection in sql_table_insert request body.`);
      continue;
    }

    const [field, cast] = field_type;

    // The param for the value substitution.
    const param = `body_${field}`;

    _[param] = value;
    fields.push(field);
    unnests.push(`unnest(%{${param}}::${cast}[]) as ${field}`);
  }

  const sql = `INSERT INTO ${_.table}
    (${fields.join(',')})
    SELECT ${unnests.join(',')}`;

  return sql;
};