INSERT INTO fails with node-mysql

Question

I am trying to insert some data with node.js. I have written the following code and installed MySQL support via npm, but I failed to INSERT INTO the table.

Here is my code:

var mysql = require('mysql');

function BD() {
    var connection = mysql.createConnection({
        user: 'root',
        password: '',
        host: 'localhost',
        port: 3306,
        database: 'nodejs'
    });
    return connection;
}

app.post("/user/create", function(req, res) {
    var objBD = BD();

    var post = {
        username: req.body.username,
        password: req.body.password
    };

    objBD.query('INSERT INTO users VALUES ?', post, function(error) {
        if (error) {
            console.log(error.message);
        } else {
            console.log('success');    
        }
    });
});

HTML code:

<form action="/user/create" method="POST" class="form-horizontal">
     <input type="text" id="username_input" name="username">
     <input type="text" id="password_input" name="password">
     <input type="submit" name="Submit" value="Insert" class="btn">
</form>

The error message is:

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 .'username' = 'Test' , 'password' = 'test' at line 1

What is my mistake?

Answer 1

Pay attention to the documentation of node-mysql:

If you paid attention, you may have noticed that this escaping allows you to do neat things like this:

var post  = {id: 1, title: 'Hello MySQL'};
var query = connection.query('INSERT INTO posts SET ?', post, function(err, result) {
  // Neat!
});
console.log(query.sql); // INSERT INTO posts SET `id` = 1, `title` = 'Hello MySQL'

Notice that they use SET instead of VALUES. INSERT INTO ... SET x = y is a valid MySQL query, while INSERT INTO ... VALUES x = y is not.

Answer 2

I know this is an old post but thought I'd share that INSERT INTO...VALUES is a valid command (no SET) and very useful for bulk updates:

db.query('INSERT INTO myTable (field1, field2, field3) VALUES ?', [values])

where [values] is an array of arrays, each array containing those three fields and inserted as a record in the table.