pg-promise Dynamic Columns with CTE

Question

I am trying to UPDATE my database with an AJAX request using pg-promise on the express server.

So far everything is working, but now I want to use a CTE (WITH) to update two tables, which is working too. What I want to do now is to be able to have dynamic columns, depending on the PUT request.

For clarification:

Request

{
    "dob": "01.01.1970",
    "email": "example@example.com",
    "street": "street 1",
}

Request

{ 
    "dob": "01.01.1970",
    "email": "example@example.com",
    "phone": "0000",
    "tag": "blue",
}

I tried the following and it is working, but my questions are:

Am I using pg-promise correctly?
Is this approach still SQL injection safe (:raw)?
Is there a better way to achieve the same results?

QueryFile.sql

WITH update_table_one AS (
    UPDATE table_one SET username=${username} 
    WHERE identification_number=${identification_number}
    RETURNING identification_number
)       
UPDATE table_two SET ${fromSet:raw}
WHERE identification_number=(SELECT identification_number FROM update_table_one);

Server.js

let qf = // ...QueryFile
let helpers = pgp.helpers;
let user = req.params.user;
let body = req.body;

let dbBody = {
    username: user,
    identification_number: 'ABC123',
    fromSet: helpers.sets(body)
};

db.oneOrNone(qf, dbBody)
    .then(data => {
        res.json({ success: true, message: 'Yes!' });
    })
    .catch(error => {
        res.json({ success: false, message: 'No!' });
    });

Addition to the initial question (relates to my question in the comments):

    { 
        "dob": "01.01.1970",
        "email": "example@example.com",
        "street": "street 1",
        "phone": "0000",
        "tag": "blue",
        "group": "students",
        "password": "hash",
        "track": "one",
    }

user_1102051 · Answer

Am I using pg-promise correctly?

Almost. Your query doesn't return anything, so use of method oneOrNone is logically incorrect, it should be none instead.

Is this approach still SQL injection safe (:raw)?

Yes, because even though you are injecting raw sets, the columns are pre-escaped within the helpers automatically, based on their JavaScript type, so any injection attempt is foiled right there.

Is there a better way to achieve the same results?

Only if you know all the columns in advance, you could pre-declare them as a ColumnSet, for better performance and/or type conversion. But other than that, no.

Also keep in mind, that if you call helpers.sets(body) passing in no columns, it will return an empty string, as per the API, which in turn will result into an invalid UPDATE.

pg-promise Dynamic Columns with CTE

1 Answers