Sanitizing user inputs when the user input is a schema name in node postgres

Question

I am using https://node-postgres.com/ to write a fairly simple application, but I do have a complex constraint that has caused me issues. Namely, I need my SQL statement to select a schema based on user input

SELECT * FROM {some user value}.tableName

but when I attempt to parametrize this normally, I get a syntax error

{"errorType":"error","errorMessage":"syntax error at or near \"$1\""

Which seems to indicate that the value wasnt replaced as expected.

Is there a way to sanitize the incoming value without executing it in a query? or is there a proper way to parametrize this value in the query in a way that will execute properly?

(I would like to avoid a big library change to something like sequilize just to meet my basic needs)

Vincent Buscarello · Accepted Answer · 2020-07-28 19:22:49Z

1

Turns out there is a great way to do this using the guts of the library! it digs into the prototype, so if this becomes a more regular thing looking into something like pg-format might be a good call. But for now this works.

const sanitizedKey = db.Client.prototype.escapeIdentifier(incomingData.orgKey)
const result = await db.query(builder(`
        WITH policies_threatened_by_incident AS (
            select p.alert_id, p.tiv, p.distance
            from ${sanitizedKey}.wf_alert_policies_active p
...

shout out to the helpful contributors -> https://github.com/brianc/node-postgres/issues/2295#issuecomment-664767339

edited Jul 28, 2020 at 19:22

answered Jul 15, 2020 at 17:38

Vincent Buscarello

4352 gold badges8 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Sanitizing user inputs when the user input is a schema name in node postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related