Prevent SQL Injection with Nodejs and Postgres

Question

I'm developing a backend to interact with a PostgreSQL database and am looking for some help preventing SQL injection. I understand the concept of SQL injection, and have found some examples online in preventing those attacks, but not sure if prevention techniques differ between SQL providers.

This is the function I use to query data:

var pg = require("pg");

var client = new pg.Client(connectionString);
client.connect();

module.exports = async function newQuery(query) {
        var result = await client.query({
        rowMode: 'array',
        text: query
        });
        return result.rows
}

And here are some standard queries using that function (query()):

SELECT

query("SELECT profilename, profiledescription, approved FROM profiledb 
WHERE usercompany='"+ req.query.userCompany +"';").then(data => {
        res.send(data)
    })

UPDATE

query("UPDATE profiledb SET approved='Approved' WHERE id='"+ req.query.id +"';").then(data =>
    res.send(data)
  )

INSERT

query("INSERT INTO profiledb (profilename, profiledescription, approved) VALUES ('"+ 
req.query.profileTitle +"', '"+ req.query.profileBody +"', 'Pending');");

What code can I use to query the data without risking SQL injection attack.

Thanks!!!

Bergi · Accepted Answer · 2019-09-30 20:37:52Z

23

Use a parameterized query and pass your request arguments as values.

module.exports = async function newQuery(query, values) {
    var result = await client.query({
        rowMode: 'array',
        text: query,
        values
    });
    return result.rows
}

query("SELECT profilename, profiledescription, approved FROM profiledb WHERE usercompany=$1;", [req.query.userCompany]).then(data => {
    res.send(data)
});

query("UPDATE profiledb SET approved='Approved' WHERE id=$1;", [req.query.id]).then(data => {
    res.send(data)
})

query("INSERT INTO profiledb (profilename, profiledescription, approved) VALUES ($1, $2, 'Pending');", [req.query.profileTitle, req.query.profileBody]);

answered Sep 30, 2019 at 20:37

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

8 Comments

Djaenike Over a year ago

Is it still possible to inject code with parameterized queries? Just confused how this technique prevents injections.

Bergi Over a year ago

No, sqli is not possible with parameterised queries. The query text and the values are sent separately to the server, instead of interpolating them to a single query string. The SQL parser doesn't even get to see the values afaik.

Rizwan Patel Over a year ago

@Bergi i am trying to pass column name in parameterized query is there limitation in that context like above example if we pass $1 as column and $2 as value would that work

Bergi Over a year ago

@RizwanPatel Names (identifiers) cannot be parameterised, only values (literals) can. You need to escape them an build an sql string yourself, e.g. like this.

Bergi Over a year ago

@TomasJansson Btw, pg-format does take injections into account, and properly escapes all data when building the query string

|

dankobgd · Accepted Answer · 2019-09-30 20:56:51Z

7

You should use parameterized queries or prepared statements, just don't concatenate strings yourself ever. the docs of this specific library are good so i suggest you read them in more details.

queries examples: docs and client.query signature: example

Your query could be written like this:

query("SELECT profilename, profiledescription, approved FROM profiledb 
WHERE usercompany = $1", [req.query.userCompany]).then(...)

same is for updates, and inserts etc. or you can just pass an object with properties: text and values like this

const queryOpts = {
  text: "SELECT profilename, profiledescription, approved FROM profiledb WHERE usercompany = $1",
  values: [req.query.userCompany]
}
query(queryOpts).then(...)

edited Sep 30, 2019 at 20:56

answered Sep 30, 2019 at 20:45

dankobgd

4163 gold badges11 silver badges34 bronze badges

3 Comments

Grogu Over a year ago

I like this but i'm new to node-postgres. Using your codes, how would you return the result in the callback?

dankobgd Over a year ago

@Grogu result will be present in the "then" block, for example .then(result => { console.log(result.rows)}). if you are new to js, definitely check this question: stackoverflow.com/questions/14220321/…, i think this is what you are asking

rohitcoder Over a year ago

How to use %% with $1 I'm getting error, i want to search using ILIKE %$1%

Collectives™ on Stack Overflow

Prevent SQL Injection with Nodejs and Postgres

2 Answers 2

8 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related