Nodejs MySQL ER_PARSE_ERROR on VALID query

Question

I've tried using mysql lib with nodejs and a simple query like SELECT * FROM table; works, but now that I've tried to construct a real query to update my database it doesn't work.

I have used an online validating tool and it checks out.

    var mysql = require('mysql');
    var request = require('request');
    request.get('http://localhost:8080/dump/asda.dump', function (error, response, body) {
        if (!error && response.statusCode == 200) {
            var data =JSON.parse(body);
            var products = data['products'][0];
            var myquery = "INSERT INTO `products2` (";
            var midquery = ") VALUES (";
            
            for (var k in products) {
                if (typeof products[k] === 'number') var v = products[k];
                else if (typeof products[k] === 'string') var v = "\'" + products[k]+ "\'";
                else if (typeof products[k] === 'boolean') var v = products[k];
                else continue;
                myquery = myquery + "`" + k + "`,";
                midquery = midquery  + v + ",";
            }
            myquery = myquery.slice(0,-1);
            midquery = midquery.slice(0, -1);

            print(myquery + midquery + ")");

            connection.connect();
            connection.query(myquery, function (err, rows, fields) {
                if (!err) console.log(rows);
                else console.log(err);
            });
            connection.end();
        }
    });

I have tried both the version with the ticks and without the ticks an none of them work.

Possible reasons might be

• Too long query. Maybe some internal char limit exeeded?

• Unsupporded characters. I am pretty sure I have quite a few Croatian ćčćš in there. How to I support that?

• Improper ' " escaping (although it seems ok to me).

• I have 'code' as a table column. I've checked and it's not a reserved keyword in mysql but it's blued in MySQL Workbench so maybe it breaks things somehow.

What I get is:

{ [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 '' at line 1]
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  index: 0 }

Answer 1

You should try with the ticks whenever you are using the database entities inside the query like the table names or database variables. As far as your query is concerned, only myquery is getting into the function as your database statement and it doesn't contain the whole query. As a result you are getting an error because of your incomplete complete query and improper syntax in it(as it is incomplete already). Your print statement will print it right because of the concatenation that you have used. If you are able to keep the concatenated query string in a variable such as:

    var new_query=myquery + midquery + ")";

And then using it as

    connection.query(new_query, function (err, rows, fields) {
            if (!err) console.log(rows);
            else console.log(err);
        });

I think your query should work. Thank you...!