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.