javascriptnode.jspostgresqlprepared-statementnode-postgres

How can I replace this obviously bad string interpolation with a prepared statement?


Apart from checking that the attributeId is in a list of predefined strings – possible, but in this particular case costly – is there any way to re-write this in a less 'injectable' fashion (since the use of '${attributeId}' is actually a field, and not strictly a parameter...):

const { tablename, attributeId } = req.params;
  let stmt = `SELECT DISTINCT row_data->'${attributeId}' as ${attributeId} FROM csv_data WHERE source = '${tablename}'`;
  let result;

  try {
    await pool.connect();
    result = await pool.query(stmt);
  } catch (err) {
    console.error(err);
  }

  ...

  return res.status(200).json({
    rows: result.rows.map((elt, i, array) => {
      return elt[attributeId];
    }),
    rowCount: result.rowCount,
  });

Solution

  • ;-) Actually I do want to use dynamic column names, for a very specific purpose, and strangely that wasn't production code so console.error() is fine. But thanks for your concern.

    The solution turns out to be pg-format:

    const format = require("pg-format");
    
    const stmt = format(
      "SELECT DISTINCT row_data->%L as %I FROM csv_data WHERE source = %L",
      attributeId,
      attributeId,
      tablename
    );
    

    Gives me back exactly what I was looking for, as well as handling the escaping:

    "SELECT DISTINCT row_data->'state_code' as state_code FROM csv_data WHERE source = 'EQP'"
    

    I definitely don't need the await pool.connect(); though ;-)