Syntax error in nodejs with mysql near to '?'-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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 "); 
});