CODEWITHSUNDEEP
node.jspostgresqlpgpg-promise
Blinhawk
Blinhawk

Reputation: 389

Escape PostgreSQL string values

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

Upvotes: 3

Views: 8566

Answers (2)

vitaly-t
vitaly-t

Reputation: 25840

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.

Upvotes: 2

JayReardon
JayReardon

Reputation: 869

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.

Upvotes: 4

Related Questions