Nodejs inserting into database fails

Question

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.

t.niese · Accepted Answer · 2018-04-09 12:39:26Z

1

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:

answered Apr 9, 2018 at 12:39

t.niese

41k9 gold badges78 silver badges111 bronze badges

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

Collectives™ on Stack Overflow

Nodejs inserting into database fails

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related