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!!!

score 22 · Accepted Answer · answered Sep 30 '19 at 20:37

22

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 '19 at 20:37

Bergi

630,263
148
957
1,375

Is it still possible to inject code with parameterized queries? Just confused how this technique prevents injections. – Djaenike Oct 01 '19 at 10:00
6

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. – Bergi Oct 01 '19 at 10:05
@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 – Rizwan Patel Aug 24 '22 at 20:45
@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](https://stackoverflow.com/a/72652930/1048572). – Bergi Aug 24 '22 at 22:06
How do you use parametrized queries if you want to insert multiple rows? If found the npm package pg-format to help, but I think that just generates a large query and not taking injection into account. – Tomas Jansson Sep 07 '22 at 23:00
@TomasJansson You can dynamically generate a `VALUES` clause with as many parameters as you need (similar to [this](https://stackoverflow.com/a/72652930/1048572)). The alternative is to pass an array value per column and `UNNEST` them, or if your data is JSON-compatible use `json_populate_recordset`. See also [How do I properly insert multiple rows with node-postgres?](https://stackoverflow.com/q/34990186/1048572) – Bergi Sep 08 '22 at 00:03
1

@TomasJansson Btw, pg-format *does* take injections into account, and properly escapes all data when building the query string – Bergi Sep 08 '22 at 00:04
You are completely right @Bergi. I missed that part in the docs. So all good. – Tomas Jansson Sep 16 '22 at 22:00

dankobgd · Answer 2 · 2019-09-30T20:56:51.957

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 '19 at 20:56

answered Sep 30 '19 at 20:45

dankobgd

367
3
9
31

I like this but i'm new to node-postgres. Using your codes, how would you return the result in the callback? – Grogu May 29 '21 at 15:15
@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: https://stackoverflow.com/questions/14220321/how-to-return-the-response-from-an-asynchronous-call, i think this is what you are asking – dankobgd May 29 '21 at 15:23
How to use %% with $1 I'm getting error, i want to search using ILIKE %$1% – rohitcoder May 11 '22 at 12:24

Prevent SQL Injection with Nodejs and Postgres

2 Answers2

Linked

Related