22

I am using node-postgres for a production application and I am wondering if there is anything I should be concerned about? Is the data sanitized automatically by node-postgres?

I couldn't find anything about it on the github page: https://github.com/brianc/node-postgres

Luke Schlangen
  • 3,722
  • 4
  • 34
  • 69

3 Answers3

35

Absolutely! The parameterized query support in node-postgres is first class. All escaping is done by the postgresql server ensuring proper behavior across dialects, encodings, etc... For example, this will not inject sql:

client.query("INSERT INTO user(name) VALUES($1)", ["'; DROP TABLE user;"], function (err, result) {
  // ...
});

This is from their documentation.

adriaan
  • 1,088
  • 1
  • 12
  • 29
Tuan Anh Tran
  • 6,807
  • 6
  • 37
  • 54
19

It basically depends on how you execute your queries as @vitaly-t described

Suppose you will define query in a string and execute as follows:

var query = `SELECT * FROM table where username='${username}' and password='${password}`;
        
pool.query(query, (error, results) => {
});

This case if i would pass username=' 'or 1=1; -- and password=' 'or 1=1; --

Then it will return all records from the table (means SQL injection works)

But if I would execute the following query

pool.query('SELECT * FROM table where username=$1 and password=$2', [username, password], (error, results) => {
});

Then SQL injection will never work because pg will sanitize the data.

So it's depends on how you execute the queries.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ankit
  • 1,094
  • 11
  • 23
  • 2
    Shouldn't that be `query = "SELECT * FROM table where username='${username}' and password='${password}'"` ? PS: could not add the ` – Matteo Dec 04 '20 at 11:48
7

It depends on how you execute your queries:

Formatting via Prepared Statements is executed by the server, which in turn sanitizes your query from any SQL injection. But it has other restrictions, like you cannot execute more than one query at a time, and you cannot provide sanitizied entity names when needed.

Client-side query formatting, like the one implemented by pg-promise, sanitizes values, plus offers flexibility in formatting entity names and multiple queries.

vitaly-t
  • 24,279
  • 15
  • 116
  • 138
  • Stupid question - can a formatted string query be considered a prepared statement if passed to `.query`? where are docs on this? – Vincent Buscarello May 15 '20 at 21:27
  • or would it have to be this syntax? ```var queryText = 'INSERT INTO users(password_hash, email) VALUES($1, $2) RETURNING id' client.query(queryText, ['841l14yah', 'test@te.st'``` – Vincent Buscarello May 15 '20 at 21:27
  • got that here https://github.com/brianc/node-postgres/wiki/FAQ – Vincent Buscarello May 15 '20 at 21:28
  • 1
    @VincentBuscarello A query is executed as [Prepared Statement](https://vitaly-t.github.io/pg-promise/PreparedStatement.html) when it is passed into query method as `{name, text, values}`. – vitaly-t May 15 '20 at 22:03