-1

I am passing data from a react frontend to an express backend:

axios.post('http://localhost/api', {foo: true, bar: false});

In the backend I am updating a MySQL database like

app.post("/user", (req, res) => {
  const {foo, bar} = req.body;
  const sql = `UPDATE users SET foo = ?, bar = ?`;
  connection.query(sql, [foo, bar], (err) => {
    if (err) {
      res.send({err: err});
    } else {
      res.send({updated: true});
    }
  })
});

What if I don't know the keys of the data I'm passing? Like foo and bar could be whatever.

I need to know the keys that I am currently destructuring in const {foo, bar} = req.body. Additionally I need to have them in the column part of the UPDATE string in foo = ? and bar = ?.

I want to do this, to be able to update different values in a database with the same function.

Barmar
  • 741,623
  • 53
  • 500
  • 612
Nick Rick
  • 75
  • 6

1 Answers1

2

Loop over the keys and values and construct the SQL dynamically.

app.post("/user", (req, res) => {
  let whitelist = ["foo", "bar"];
  let checkWhitelist = Object.keys(req.body).filter((e) => whitelist.includes(e)).length > 0;
  if (checkWhitelist) {
    let assign = Object.keys(req.body).map(k => `\`${k}\` = ?`).join(', ');
    let vals = Object.values(req.body);
    const sql = `UPDATE users SET ${assign}`;
    connection.query(sql, vals, (err) => {
      if (err) {
        res.send({err: err});
      } else {
        res.send({updated: true});
      }
    })
  } else {
    res.send({err: "Error"});
  }
});

Note that this is dangerous, since you're trusting the client to send valid column names. In real life you should white-list the column names.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • One should also use back-ticks around the column names, even if they pass an allow-list. – Bill Karwin Apr 26 '21 at 15:43
  • Your approach is working. But I don't want to have insecure code. Thus do I need to check if the keys are part of a given array? @BillKarwin with back-ticks you mean ``\`${k}\`  = ?``? – Nick Rick Apr 26 '21 at 17:34
  • Yes, because even if you verify the column name against an array (which I do recommend), what if you have a legit column that conflicts with a SQL reserved keyword, or which contains whitespace or punctuation characters? Those are legal identifiers in SQL, but you need to delimit them. – Bill Karwin Apr 26 '21 at 17:46