Trying to pass variables into a prepared statement in JavaScript Node JS

Question

function addEmployee() {
  inquirer
    .prompt([
      {
        name: "firstname",
        type: "input",
        message: "Enter their first name ",
      },
      {
        name: "lastname",
        type: "input",
        message: "Enter their last name ",
      },
      {
        name: "role",
        type: "list",
        message: "What is their role? ",
        choices: selectRole(),
      },
      {
        name: "choice",
        type: "rawlist",
        message: "Whats their managers name?",
        choices: selectManager(),
      },
    ])
    .then(function (val) {
      var roleId = selectRole().indexOf(val.role) + 1;
      var managerId = selectManager().indexOf(val.choice) + 1;
      db.query(
        "INSERT INTO employee (first_name, last_name, role_id, manager_id) values ('?', '?', ?, ?)?",
        {
          first_name: val.firstName,
          last_name: val.lastName,
          manager_id: managerId,
          role_id: roleId,
        },
        function (err) {
          if (err) throw err;
          console.table(val);
          startPrompt();
        }
      );
    });
}

Cannot get the INSERT into employees query statement to work. I am using the mysql2 npm package to interface with a Mysql database in Node JS. Trying to pass in the answers from the inquirer questions into the prepared statement to insert into the employees table. How do I pass in variables for the prepared statement?

What do you mean with "can't make it works" ? Can you [edit] your question ? — Elikill58, Dec 05 '21 at 20:33
pass as array, `db.query('... VALUES (?, ?, ...)', [val.firstName, val.lastName, ...])` — Lawrence Cherone, Dec 05 '21 at 20:36
code: 'ER_PARSE_ERROR', errno: 1064, sqlState: '42000', 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", sql: "INSERT INTO employee (first_name, last_name, role_id, manager_id) values ('NULL', 'NULL', 0, 0)?" — smasherCoder, Dec 05 '21 at 20:44
that would be because its `val.firstname` not `val.firstName` — Lawrence Cherone, Dec 05 '21 at 20:51
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", sql: "INSERT INTO employee (first_name, last_name, role_id, manager_id) values ('peter', 'pan', 1, 1)?" — smasherCoder, Dec 05 '21 at 20:59

score 1 · Answer 1 · answered Dec 05 '21 at 20:57

Pass as array, check order of placeholders match array order and case matters on variables.

...
db.query(`
   INSERT INTO employee (
     first_name, 
     last_name, 
     role_id, 
     manager_id
   ) 
   VALUES (?, ?, ?, ?)
`, [val.firstname, val.lastname, roleId, managerId]);
...

Trying to pass variables into a prepared statement in JavaScript Node JS

1 Answers1

Linked