Syntax error in nodejs with mysql near to '?'

Question

i try to get my hand on database mysql and nodejs. During learning i encounter syntax error on the script and i have no idea how to fix the syntax.

var express = require('express');
var router = express.Router();
var db=require('../database');
router.get('/form', function(req, res, next) { 
res.render('users'); 
});
router.post('/create', function(req, res, next) {

// store all the user input data
const userDetails=req.body;

// insert user data into users table
var sql = 'INSERT INTO users SET ?';
db.query(sql, userDetails,function (err, data) { 
  if (err) throw err;
     console.log("User dat is inserted successfully "); 
});
res.redirect('/users/form');  // redirect to user form page after inserting the data
}); 
module.exports = router;

here is the error:

code: 'ER_PARSE_ERROR',
errno: 1064,
sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near '?' at line 1",
sqlState: '42000',
index: 0,
sql: 'INSERT INTO users SET ?'

for reference: i learn this code from link

Thanks for the reply @Amadan, so in my case, I have 5 column in users table. How should i write the syntax? the column consist of id,fullName, emailAddress, city, and country. — lvsilver
– lvsilver, Commented May 5, 2022 at 3:25

Tyler2P · Accepted Answer · 2022-05-05 17:40:30Z

I'm not sure what the specific implementation of the db.query function is, but you probably want something like this (I got the db schema from your other comment):

let sql = 'INSERT INTO users SET id=?, fullName=?, emailAddress=?, city=?, country=?';
db.query(sql, userDetails, function (err, data) { 
  if (err) {
     throw err;
  }
  console.log("User dat is inserted successfully "); 
});

If that doesn't work try setting the SQL variable to this instead:

let sql = 'INSERT INTO users (id, fullName, emailAddress, city, country) VALUES (?, ?, ?, ?, ?)';

I'm assuming that userDetails variable references an object with the following type: { id, fullName, emailAddress, city, country }.

SQL requires a question mark for each column on each insertion when using prepared statements. The db.query function might be trying to insert the object directly as a string, which could cause an issue because your schema has 5 columns. The JSON syntax of the stringified object might also cause the SQL parser to complain.

If that doesn't work, I'd like to look at your schema. Your function seems to send the req.body object to the database directly, which would mean the user is sending the id directly to the database. This is rather uncommon, usually the id of a row is generated by the server or an autoincrement column in the database.

I hope this works out for you, feel free to respond if you have any questions or concerns.

jitendra chauhan · Accepted Answer · 2022-05-05 04:24:13Z

0

you can't pass the data like {field1 : 1, field2 : 2, field3 : 3}. pass this 'INSERT INTO users SET field1 = ?, field2 = ?';

const userDetails=[
                    1, // this value is field1
                    2, // this value is field2
                  ];

// insert user data into users table
var sql = 'INSERT INTO users SET field1 = ?, field2 = ?';
db.query(sql, userDetails,function (err, data) { 
  if (err) throw err;
     console.log("User dat is inserted successfully "); 
});

answered May 5, 2022 at 4:24

jitendra chauhan

1591 silver badge7 bronze badges

Comments

Tyler2P · Accepted Answer · 2022-12-20 18:23:47Z

0

We assume the values are objects:

userDetails = {
  name: "Rum Haidar F.",
  dob: "1990-01-01",
  address: "Foo Bar Street"
}

Try to add [ values ]:

// insert
var sql = 'INSERT INTO users SET ?';
db.query(sql, [userDetails], function (err, data) { 
  if (err) throw err;
  console.log("User dat is inserted successfully "); 
});

For UPDATE data [ values, YOUR CONDITION ]:

// update
var sql = 'UPDATE users SET ? WHERE user_id = ? AND status = ?';
db.query(sql, [userDetails, 69, 'active'],function (err, data) { 
  if (err) throw err;
  console.log("User dat is inserted successfully "); 
});

edited Dec 20, 2022 at 18:23

Tyler2P

2,37030 gold badges26 silver badges34 bronze badges

answered Dec 19, 2022 at 8:41

Rum Haidar F

11 bronze badge

Collectives™ on Stack Overflow

Syntax error in nodejs with mysql near to '?'

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related