Error: ER_PARSE_ERROR: You have an error in your SQL syntax;

Question

I'm trying to insert values using mysql in nodejs. I had written the following code and installed MySQL support via npm,But canot to INSERT INTO the table due to this problem.

My code;

var mysql = require('mysql');

var values=randomValueHex(8);

var sql = "INSERT INTO `activationkeys`(`activationKey`, `productId`) 
VALUES ( values ,'3')";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("1 record inserted");
});

My Error on terminal:

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 ''3')'

How can i solve this problem?

Solution 1

Why are you using back quote for the column names? We do not need that in column names. You can simply create your dynamic sql query by using + operator on the column values like this:

var sql = "INSERT INTO activationkeys (activationKey, productId) VALUES ( " + values + " ,'3')";

Solution 2

Instead of

var sql = "INSERT INTO `activationkeys`(`activationKey`, `productId`) 
VALUES ( values ,'3')";

Please try this

 var sql = "INSERT INTO `activationkeys`(`activationKey`, `productId`) 
    VALUES ( " +  values + " ,'3')";

provided values is a string

Comments

[messerbill]

your SQL query is invalid. dev.mysql.com/doc/refman/5.7/en/insert.html

[Saptarshi Dey]

Do console.log(this.sql) before the error handling and see in your log what is the query being executed

[axiac]

values is a MySQL keyword. You probably want sql = "... VALUES(" + values + ", '3')" but you better use prepared statements. In its current status your code is vulnerable to SQL injection.

[messerbill]

and i always use sequelize for db handling in node: docs.sequelizejs.com - try it :)

[axiac]

"Is it an integer?" -- No! "Is it a string?" -- No! -- It is a keyword! ;-)

[GiamPy]

Why downvote? My answer is basically the same as the other ones, with more details. Enlighten me please. @axiac I meant the values JS variable, not the VALUES before the parenthesis.

[axiac]

I didn't downvote. I just liked your rhetorical questions and turned them into a funny comment (I hope).