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

Is ${name} string interpolation? If yes, I would suggest to use parametrized query to avoid problems with quoting and possible SQL Injection. — Lukasz Szozda
– Lukasz Szozda, Commented Aug 14, 2018 at 17:17

JayReardon · Accepted Answer · 2018-08-21 17:47:04Z

4

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.

edited Aug 21, 2018 at 17:47

answered Aug 14, 2018 at 17:27

JayReardon

8891 gold badge10 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vitaly-t Over a year ago

The suggestion for Parameterized Queries may be misplaced. Library pg-promise itself provides several ways to deal with missing values and defaults for them, from the base method as.format, which takes such options, to the helpers namespace that also works with such defaults. Anyhow, Parameterzied Queries wouldn't be a good alternative here.

vitaly-t · Accepted Answer · 2018-08-21 19:14:23Z

2

If you just use Named Parameters correctly, then it will work:

function pg_insert(x) {
    pg.db.none('INSERT INTO table(name, lastname) VALUES(${name}, ${lastname})', x)
    .then(data => {})
    .catch(err => {
        console.log(err);
    });
}

UPDATE

I have also amended the official documentation to include the following:

WARNING: Never use ${} syntax inside ES6 template strings, as those have no knowledge of how to format values for PostgreSQL. Inside ES6 template strings you can only use one of the 4 alternatives - $(), $<>, $[] or $//.

...to make it more clear.

edited Aug 21, 2018 at 19:14

answered Aug 14, 2018 at 23:23

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

Collectives™ on Stack Overflow

Escape PostgreSQL string values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related