Express/Node user controller to upload file on server and MySQL throw an SQL syntax error

Question

I'm creating an user controller export.update as per below code.

Once a POST request is submitted on the front end the controller should do the following:

upload file on folder updload;
insert name of the picture on the MySql database.

When I click on submit button the upload of the file is successful on the dedicated folder but no data are transfer to MySql. Instead I get back this message on the terminal:

sqlMessage: "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 'WHERE id = '203'' at line 1", sqlState: '42000', index: 0, sql: "UPDATE user SET first_name='John' ,last_name='Doe', profile_image='rocket-lab-New-Zealand.jpeg',WHERE id = '203'"

Any suggestion/direction on the right path is highly appreciated.

exports.update = (req, res) => {
    message = '';
    if (req.method == 'POST') {
        var post = req.body;
        var first_name = post.first_name;
        var last_name = post.last_name;


        if (!req.files)
            return res.status(400).send('No files were uploaded.');

        var file = req.files.profile_image;


        var profile_image = file.name;
        console.log(profile_image)
        if (file.mimetype == "image/jpeg" || file.mimetype == "image/png" || file.mimetype == "image/gif") {

            file.mv('./upload/' + file.name, function (err) {

                if (err)

                    return res.status(500).send(err);

                connection.query('UPDATE user SET first_name=? ,last_name=?, profile_image=?,WHERE id = ?', [first_name, last_name, profile_image, req.params.id], (err, rows) => {

                    if (!err) {
                        connection.query('SELECT * FROM user WHERE id = ?', [req.params.id], (err, rows) => {
                            if (!err) {
                                res.render('edit-crew', { rows, alert: `${first_name} has been updated.` });

                            } else {
                                console.log(err);
                            }
                            console.log('The data from user table:\n', rows);
                        });
                    } else {
                        console.log(err);
                    }
                    console.log('The data from user table:\n', rows);
                });
            });
        }
    }
}

typicallearner · Accepted Answer · 2021-12-11 11:59:22Z

1

UPDATE user SET first_name=? ,last_name=?, profile_image=?,WHERE id = ?

There's an extra "," right before WHERE clause.

Fix:

UPDATE user SET first_name=? ,last_name=?, profile_image=? WHERE id = ?

answered Dec 11, 2021 at 11:59

typicallearner

2562 silver badges18 bronze badges

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

1 Comment

Cesare Mannino Over a year ago

Yes!, you're right, thanks is working now!

Collectives™ on Stack Overflow

Express/Node user controller to upload file on server and MySQL throw an SQL syntax error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related