CODEWITHSUNDEEP
mysqlnode.js
André Ferraz
André Ferraz

Reputation: 1521

Nodejs inserting into database fails

I've been trying to make this script work with value binds, but seems it doesn't work with the mainTable variable.

var mainTable = "tasks";
var con = mysql.createConnection({
    host: "host",
    user: "user",
    password: "password",
    database: "database"
});

con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    var values = {
        id: "id",
        url: "url",
        assignee: "akjdh",
        status: "ahsbdu",
        type: "apsokdn",
        description: "asd",
        department: "department"
    };

    var query = con.query("INSERT INTO ? SET ? ON DUPLICATE KEY UPDATE ?", [
            mainTable,
            values,
            values
        ], function (err, result) {
        if (err) throw err;
        console.log("1 record inserted");
    });
});

I get the following error:

Error: ER_PARSE_ERROR: 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 ''tasks' SET id = 'id', url = 'url', assignee = 'akjdh', status = 'ahsbdu' at line 1

I don't understand why its failing with if I make the table value into a bind.

Upvotes: 1

Views: 44

Answers (1)

t.niese
t.niese

Reputation: 40882

Mysql identifiers have to be escaped using a backtick ` and not using '.

  • ? is used to escape value with '
  • ?? is used to escape identifiers with `

As of that it hast to be:

INSERT INTO ?? SET ? ON DUPLICATE KEY UPDATE ?

mysql: Escaping query identifiers

Alternatively, you can use ?? characters as placeholders for identifiers you would like to have escaped like this:

Upvotes: 1

Related Questions