Javascript UPDATE variables

Question

updateUser(request, respond) {
    var userObject = new User(request.params.id, request.body.firstName, request.body.lastName, request.body.username, request.body.email, request.body.password, request.body.rememberMe, request.body.profilePic, request.body.mobileNumber, request.body.gender, request.body.birthday, request.body.address, request.body.postalCode, request.body.language);

    //what is this    
    var sql = "UPDATE resreview.users SET firstName = ?, lastName = ?, username = ?, email = ?, password = ?, rememberMe = ?, profilePic = ?, mobileNumber = ?, gender = ?, birthday = ?, address = ?, postalCode = ?, language = ? WHERE userId = ?";

    var values = [
        userObject.getFirstName(),
        userObject.getLastName(),
        userObject.getUsername(),
        userObject.getEmail(),
        userObject.getPassword(),
        userObject.getRememberMe(),
        userObject.getProfilePic(),
        userObject.getMobileNumber(),
        userObject.getGender(),
        userObject.getBirthday(),
        userObject.getAddress(),
        userObject.getPostalCode(),
        userObject.getLanguage()];

    db.query(sql, values, function (error, result) {
        if (error) {
            throw error;
        }
        else {
            respond.json(result);
        }
    });
}

When I try to run it in command prompt, I keep getting this error

Error: ER_PARSE_ERROR: 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 '?' at line 1

I am just following this format

UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];

Can somebody please explain to me what the problem is?

Check your userId.

kumaran
– kumaran

2019-12-27 08:13:41 +00:00
Commented Dec 27, 2019 at 8:13 — kumaran
– kumaran, Commented Dec 27, 2019 at 8:13

Maximilian Fixl · Accepted Answer · 2019-12-27 08:56:16Z

1

Apart from the fact that I don't know the db class, check numbers of parameters and values. You have less entries in values as placeholders in your sql string. That means, your placeholder ? in the where clause wont be replaced.

Add the `userId` to the `values` array

 var sql = "UPDATE resreview.users SET firstName = ?, lastName = ?, username = ?, email = ?, password = ?, rememberMe = ?, profilePic = ?, mobileNumber = ?, gender = ?, birthday = ?, address = ?, postalCode = ?, language = ? WHERE userId = ?";

//added userObject.getUserId()
var values = [
    userObject.getFirstName(),
    userObject.getLastName(),
    userObject.getUsername(),
    userObject.getEmail(),
    userObject.getPassword(),
    userObject.getRememberMe(),
    userObject.getProfilePic(),
    userObject.getMobileNumber(),
    userObject.getGender(),
    userObject.getBirthday(),
    userObject.getAddress(),
    userObject.getPostalCode(),
    userObject.getLanguage(),
    userObject.getUserId()
];

Or add the `userId` into the sql string

//make sure to escape the `userId` if it's not an internal value and check if you've implemented an db.escape() method - otherwise you can use replace() with a regex pattern
var sql = "UPDATE resreview.users SET firstName = ?, lastName = ?, username = ?, email = ?, password = ?, rememberMe = ?, profilePic = ?, mobileNumber = ?, gender = ?, birthday = ?, address = ?, postalCode = ?, language = ? WHERE userId = " + db.escape(userObject.getUserId());

answered Dec 27, 2019 at 8:56

Maximilian Fixl

7186 silver badges26 bronze badges

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

2 Comments

CBCH Over a year ago

thanks a lot, didn't realise that I never included the userId in my 'values' array

Maximilian Fixl Over a year ago

👍 I always counting placeholders and array entries. Double check myself.

Collectives™ on Stack Overflow

Javascript UPDATE variables

1 Answer 1

Add the `userId` to the `values` array

Or add the `userId` into the sql string

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Add the userId to the values array

Or add the userId into the sql string

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related

Add the `userId` to the `values` array

Or add the `userId` into the sql string