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.

Daniel · Accepted Answer · 2014-01-24 21:45:21Z

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.

edited Jan 24, 2014 at 21:45

answered Jan 24, 2014 at 20:12

Daniel

38.9k12 gold badges95 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Over a year ago

Yeah, I read about connection pools in the documentation...could you possibly elaborate on what that entails?

Dan Over a year ago

Also, what is the differences between using SET and VALUES in the query

Daniel Over a year ago

The difference between SET and VALUES is just alternative ways to accomplish the same thing. I find SET to be a little easier to work with when inserting a single record.

Brian Glaz · Accepted Answer · 2014-01-24 20:09:05Z

0

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 (?, ?, ?)";

answered Jan 24, 2014 at 20:09

Brian Glaz

15.7k4 gold badges40 silver badges55 bronze badges

Collectives™ on Stack Overflow

Node.js with MySQL queries

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related