How to fix function with postgres query to prevent SQL injection

Question

I need help fixing a function I've written (in a node.js serverless microservice) to prevent sql injection. I'm new to security topics so any ideas or points in the right direction would be awesome, thanks!

Here's the function from RecipientAlerts.js:

  updateRecipient(email, body, callback) {
    helper.removeRecipient(this.db, email) // clears old data
      .then(() => {
        const values = Object.keys(body).map(industry => 
          body[industry].map(company => 
            `('${company}', '${industry}', '${email}')`).join(', ')).join(', ');
        const insert =`INSERT INTO recipient_list(company, industry, email_address) VALUES `;
        this.db.queries.none(insert + values)             
          .catch((error) => {
            console.log(error, 'error on insert query', callback);
          });
      })
      .then(() => {
        console.log('successfully updated', null, callback);
      })
      .catch((error) => {
        console.log(error, 'failed to update recipient', callback);
      });
  }

Here's the recipient.json:

{ 
    "pathParameters": {
        "email": "[email protected]"
    },
    "body": {
        "tech": ["Apple"],
        "hospitality": ["McDonalds", "Subway"],
        "banking": ["Citi", "HSBC"]
    }
}

The expected result (which I'm currently getting and want to stay the same) is: recipient_list table:

company       |  industry   | email_address
______________|_____________|________________
Apple         | tech        | [email protected]
--------------|-------------|---------------
McDonalds     | hospitality | [email protected]
--------------|-------------|---------------
Subway        | hospitality | [email protected]
--------------|-------------|---------------
Citi          | banking     | [email protected]
--------------|-------------|---------------
HSBC          | banking     | [email protected]

Community · Accepted Answer · 2020-06-20 09:12:55Z

Following the Multi-Row Inserts examples with pg-promise, declare a ColumnSet object once:

const cs = new pgp.helpers.ColumnSet([
    'company',
    'industry',
    {name: 'email_address', prop: 'email'}
], {table: 'recipient_list'});

Then you can change your code to this:

updateRecipient(email, body, callback)
{
    helper.removeRecipient(this.db, email) // clears old data
        .then(() => {
            const insert = pgp.helpers.insert(body, cs); // generating the INSERT query
            this.db.queries.none(insert) // executing the INSERT query
                .catch((error) => {
                    console.log(error, 'error on insert query', callback);
                });
        })
        .then(() => {
            console.log('successfully updated', null, callback);
        })
        .catch((error) => {
            console.log(error, 'failed to update recipient', callback);
        });
}

The SQL will be generated safely this way, and immune to SQL injections.

Mahdi · Accepted Answer · 2019-06-11 06:20:34Z

0

I highly recommend to use sequelize , it will handle it automatically

Here is the doc

answered Jun 11, 2019 at 6:20

Mahdi

1,4051 gold badge14 silver badges29 bronze badges

Collectives™ on Stack Overflow

How to fix function with postgres query to prevent SQL injection

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related