Node.js with MySQL queries

Question

I'm using MySQL for the first time, and I'm struggling to properly preparing statements and escaping query values. Here's where I'm at:

    connection.connect();
        formatDate(function(date){
            var sql = "INSERT INTO coffee_tbl (coffee_name, coffee_type, submission_date) VALUES ?";
            var inserts = [req.param('name'), req.param('type'), date];
            var queryString = mysql.format(sql, inserts);
            console.log(queryString)
            connection.query(queryString, function(err, results){ 
            if(err) serverError(res, err); 
            else{ 
            res.redirect('/view_coffee'); 
           }
        });
    });


    connection.end();

I'm using the 'mysql' node.js module by felixge.

Answer 1

You need a ? per value. Also, be sure to use a connection pool.

formatDate(function(date){
  var sql = [
    "INSERT INTO coffee_tbl SET",
    " coffee_name=?",
    ",coffee_type=?",
    ",submission_date=?"
    ].join('');
  var inserts = [req.param('name'), req.param('type'), date];
  pool.getConnection(function(err, connection) {
    if(err) return console.error(err);
    connection.query(sql, inserts, function(err, results) {
      connection.release();
      if(err) return console.error(err);
      res.redirect('/view_coffee');
    });
  });
});

To setup a connection pool:

var pool = mysql.createPool({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASS,
  database: process.env.MYSQL_NAME,
  connectionLimit: 8
});

Use environment variables for your mysql authentication information so as to not commit authentication information to a repo.

Answer 2

You only have one placeholder in your sql var, but you are trying to pass three values in your inserts var. You want to modify your sql var to have three placeholder like this:

var sql = "INSERT INTO coffee_tbl (coffee_name, coffee_type, submission_date) VALUES (?, ?, ?)";