I'm reviewing the security of my communication with my database and to prevent an SQL injection attack I would like to avoid launching queries where the parameters are directly written in full text in the query like this:
async function getDataTension(req, res) {
const table_name = req.params.tag_id;
return db.any('SELECT adc_v, received_on, id FROM '+ table_name +' WHERE id = (SELECT MAX(id) FROM '+ table_name +')')
.then(rows => {
// console.log(rows);
res.json(rows)
})
.catch(error => {
console.log(error)
});
}
I would like to do like this:
return db.any('SELECT adc_v, received_on, id FROM $1 WHERE id = (SELECT MAX(id) FROM $1)', table_name)
Except that the query returns a syntax error:
syntax error on or near "'my table name' "
I'm using node.js, pg.promise
edit : I can't parameterize the name of a table, so I will filter with regex the input (avoid capital letters and space, and as the name of my tables have the same base in syntax filter on this side too)
