node.js - cannot use backtick when using nodejs 7.3.0

Question

I'm trying to run a simple website, and encountered an following backtick error

  `INSERT INTO questions(qid, uid, question, difficulty, cid) VALUES(${qid},${uid},${question},${difficulty},${cid})`,
   ^^^^^^
SyntaxError: Unexpected identifier
    at Object.exports.runInThisContext (vm.js:78:16)
    at Module._compile (module.js:543:28)
    at Object.Module._extensions..js (module.js:580:10)
    at Module.load (module.js:488:32)
    at tryModuleLoad (module.js:447:12)
    at Function.Module._load (module.js:439:3)
    at Module.runMain (module.js:605:10)
    at run (bootstrap_node.js:420:7)
    at startup (bootstrap_node.js:139:9)
    at bootstrap_node.js:535:3

and here's the code

app.put('/problems', function(req, res) {
  pool.getConnection(function(err, connection) {
    var p_list = new Array(4);
    var qid = mysql.escape(req.body.qid);
    var uid = mysql.escape(req.body.uid);
    var question = mysql.escape(req.body.question);
    var difficulty = mysql.escape(req.body.difficulty);
    var cid = mysql.escape(req.body.cid);
    var choices = req.body.choices;
    var answer = mysql.escape(req.body.answer);
    var explanation = mysql.escape(req.body.explanation);
    var qid_choice = ``;
    choices.forEach( choice => {
      choice = mysql.escape(choice);
      qid_choice += "("+qid+", "+choice+"),";
    } );
    qid_choice = qid_choice.slice(0,-1);

    var queries = [
      `INSERT INTO questions(qid, uid, question, difficulty, cid) VALUES(${qid},${uid},${question},${difficulty},${cid})`,
      `INSERT INTO questionInfo(qid) VALUES(${qid})`,
      `INSERT INTO choices(qid, choice) VALUES ${qid_choice}`,
      `INSERT INTO solutions(qid, answer, explanation) VALUES(${qid},${answer},${explanation})`
    ];
    for (let i=0; i<4; i++) {
      p_list[i] = new Promise(function(resolve, reject) {
        connection.query(
          queries[i],
          err => {
            if (err) reject(err);
            else resolve();
          }
        );
      });
    }

    Promise.all(p_list).then(function() {
      connection.release();
      console.log(`[200] ${req.method} to ${req.url}`);
      res.end();
    }, function(err) {
      connection.release();
      console.log(`[500] ${req.method} to ${req.url} because ${err}`);
    })
  });
});

I'm using node version 7.3.0

I have no idea why this error occurred... It's too frustrating

Thank you for reading :)

Answer 1

SQL Injection Alert

Your entire code is a one big SQL injection vulnerability waiting be exploited. It's pretty rare to have exploitable SQL injection vulnerability this days but here you have it in every parameter.

Never do this

connection.query(
    `INSERT INTO questionInfo(qid) VALUES(${qid})`,
    err => {
        // ...
    }
);

or:

connection.query(
    'INSERT INTO questionInfo(qid) VALUES(' + qid + ')',
    err => {
        // ...
    }
);

Always do this

connection.query(
    'INSERT INTO questionInfo(qid) VALUES(?)',
    qid,
    err => {
        // ...
    }
);

Your problem

Looking at your problem it seems that either you have unbalanced backticks or you found a bug in Node. It's hard to tell anything more because instead of posting a minimal example that reproduces your problem, you posted an incomplete part of your route handler that cannot be even run without the parts that you removed.

But you should be grateful that you got the problem with backticks because without it you would never even know how insecure your code is. I can't even remember when I last saw a code with SQL injection vulnerability. It's been years since I last referred someone to this comic strip:

Please read:

• https://en.wikipedia.org/wiki/SQL_injection
• http://www.beyondsecurity.com/about-sql-injection.html
• http://projects.webappsec.org/w/page/13246963/SQL%20Injection
• http://bobby-tables.com/

And remember to never use backticks to insert unsanitized data to any string, especially SQL.