Uncaught SqlError when saving in the DB (MariaDB)

26 views Asked by At

MariaDB reports this error when that comes from edit/user/X HTML page

NodeJS: 21.6.2 MariaDB: 10.4 OS: Windows 11

"dependencies": {
        "body-parser": "^1.20.2",
        "ejs": "^3.1.9",
        "express": "^4.18.2",
        "mariadb": "^3.2.3",
        "path": "^0.12.7"
}

Error Code

Uncaught SqlError SqlError: (conn=114, no: 1064, SQLState: 42000) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''{\"first_name\":\"test\",\"last_name\":\"test\",\"email_address\":\"test\",\...' at line 1
sql: UPDATE user SET ? WHERE user_id = ? - parameters:[{"first_name":"test","last_name":"test","email_address":"test","telephone_number":"test","role_id":"1","house_number":"test","zip_code":"5000","town":"test","country":"test"},'2']

Formatted message of the error:

Uncaught SqlError SqlError: (conn=114, no: 1064, SQLState: 42000) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 
sql: UPDATE user SET ? WHERE user_id = ? - parameters:
[
  {
    "first_name":"test",
    "last_name":"test",
    "email_address":"test",
    "telephone_number":"test",
    "role_id":"1",
    "house_number":"test",
    "zip_code":"5000",
    "town":"test",
    "country":"test"
  },
  '2'
]

JS Code

app.post('/edit/:user_id', (req, res) => {
    const userId = req.params.user_id;
    
    // Flatten the object before updating
    const updatedUser = {
      first_name: req.body.first_name,
      last_name: req.body.last_name,
      email_address: req.body.email_address,
      telephone_number: req.body.telephone_number,
      role_id: req.body.role_id,
      house_number: req.body.house_number,
      zip_code: req.body.zip_code,
      town: req.body.town,
      country: req.body.country,
      // Add other fields as needed
    };
  
    db.pool.query('UPDATE user SET ? WHERE user_id = ?', [updatedUser, userId], (error) => {
      if (error) throw error;
  
      // After updating, fetch the updated user data and render the edit page again
      db.pool.query('SELECT * FROM user WHERE user_id = ?', [userId], (err, result) => {
        if (err) throw err;
        res.render('edit', { user: result[0] });
      });
    });
  });

What is causing this error, I cannot find a fix or proper information that explains what I am doing wrong.

0

There are 0 answers