Escape PostgreSQL string values

Question

I'm trying to insert over 70k values to my postgres database. However, each rows have different types of special characters. When I insert I get the usual error. for example: syntax error at or near ".5". I'm using the module pg-promise. Below the code that I'm using:

  function pg_insert(x) {
        var name = x["name"];
        var lastName = x["lasname"];

        var sql = `insert into table(name, lastname)`;
        var values = `values (${name},${lastname})`;

        pg.db
        .none(sql + values)
        .then(data => {})
        .catch(err => {
            console.log(err);
        });
    }

Any help will be appreciated.

Thank you

Answer 1

As Lukasz mentions, use a Parameterized Query:

  function pg_insert(x) {
    const name = x['name'];
    const lastName = x['lasname'];

    const sql = 'insert into table(name, lastname) values ($1, $2)';
    const values = [name, lastname];

    pg.db
    .none(sql, values)
    .then(data => {})
    .catch(err => {
      console.log(err);
    });
  }

You could also use Named Parameters:

  function pg_insert(x) {
    const sql = 'insert into table(name, lasname) values (${name}, ${lastname})';

    pg.db
    .none(sql, x)
    .then(data => {})
    .catch(err => {
      console.log(err);
    });
  }

However, as mentioned in the pg-promise Named Parameters documentation:

Keep in mind that while property values null and undefined are both formatted as null, an error is thrown when the property does not exist.

If any of the properties from your passed object x are nonexistent (e.g. x.name, x.lasname, { "name": "Joe" }(note the missing lasname property)), then the Named Parameters example code above will produce an error.

As vitaly-t (the library creator) mentions, there are helper methods in pg-promise to assist with missing data. But, if you can not guarantee the shape of the data that will be passed into the query and aren't interested in adding the code necessary to sanitize the data, then a Parameterized Query may be the easier option for failure-proofing.

It's left to the reader to determine which method make the most sense for their use-case.