I'm stuck with inserting JSON data to MySQL db using NodeJS. I got this 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 ''[{\"id\":\"d3f3d\",\"code\":\"' at line 1

I'm getting JSON data from url.json using the request Node module and I'm trying to store the data in a MySQL db.

//mysql connection setup
var connection = mysql.createConnection({
    host : "localhost",
    port: "3306",
    user : "root",
    password : "root",
    database : "db",
    multipleStatements: true
});

request('url.json', function (error, response, body) {
  if (!error && response.statusCode == 200) {
    //console.log(body)
  }

    var sql = "INSERT INTO table (id, code, country_name, city) VALUES ?";

    var data = JSON.parse(body);
    var responseJson = JSON.stringify(data.response.docs);

    var query = connection.query(sql, [responseJson], function(err, result) {
        if(err) throw err;
        console.log('data inserted');
    });
    console.log(query.sql);

});

The data is logged as '' '[{\"id\":\"d3f3d\",\"code\":\"'... }]'. I think this may be the source of the error.

JSON structure looks like this:

{
    "header":
    {
        "file1":0,
        "file2":1,
        "subfiles":{
          "subfile1":"true",
          "subfile2":"true",
        }
    },

    "response":
    {
        "number":678,
        "start":0,
        "docs":[
            {
                "id":"d3f3d",
                "code":"l876s",
                "country_name":"United States",
                "city":"LA"
            },
            {
                "id":"d2f2d",
                "code":"2343g",
                "country_name":"UK",
                "city":"London"
            }
        ]
    }
}

How do I resolve this?

Thanks.

I'm stuck with inserting JSON data to MySQL db using NodeJS. I got this 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 ''[{\"id\":\"d3f3d\",\"code\":\"' at line 1

I'm getting JSON data from url.json using the request Node module and I'm trying to store the data in a MySQL db.

//mysql connection setup
var connection = mysql.createConnection({
    host : "localhost",
    port: "3306",
    user : "root",
    password : "root",
    database : "db",
    multipleStatements: true
});

request('url.json', function (error, response, body) {
  if (!error && response.statusCode == 200) {
    //console.log(body)
  }

    var sql = "INSERT INTO table (id, code, country_name, city) VALUES ?";

    var data = JSON.parse(body);
    var responseJson = JSON.stringify(data.response.docs);

    var query = connection.query(sql, [responseJson], function(err, result) {
        if(err) throw err;
        console.log('data inserted');
    });
    console.log(query.sql);

});

The data is logged as '' '[{\"id\":\"d3f3d\",\"code\":\"'... }]'. I think this may be the source of the error.

JSON structure looks like this:

{
    "header":
    {
        "file1":0,
        "file2":1,
        "subfiles":{
          "subfile1":"true",
          "subfile2":"true",
        }
    },

    "response":
    {
        "number":678,
        "start":0,
        "docs":[
            {
                "id":"d3f3d",
                "code":"l876s",
                "country_name":"United States",
                "city":"LA"
            },
            {
                "id":"d2f2d",
                "code":"2343g",
                "country_name":"UK",
                "city":"London"
            }
        ]
    }
}

How do I resolve this?

Thanks.

• javascript
• mysql
• json
• node.js

2 Answers 2

The error stems from the query object's attempt to parse the values supplied. The function expects the values array to contain multiple arrays but received a string instead. The responseJson need not be converted into a JSON string.

You'll need to convert the array of objects into an array of arrays with only the values. For example, the query expects an array of this form:

"docs":[
  [
    "d3f3d",
    "l876s",
    "United States",
    "LA"
  ],
  [
    "d2f2d",
    "2343g",
    "UK",
    "London"
  ]
]

This is a method to convert an array of objects into an array of values:

function ObjToArray(obj) {
  var arr = obj instanceof Array;

  return (arr ? obj : Object.keys(obj)).map(function(i) {
    var val = arr ? i : obj[i];
    if(typeof val === 'object')
      return ObjToArray(val);
    else
      return val;
  });
}

This is the code refactored:

var data = JSON.parse(body);
// Convert the array of objects into an array of arrays.
var responseJson = ObjToArray(data.response.docs);

// The query object expects an array of objects so you pass in 'responseJson' as is
var query = connection.query(sql, responseJson, function(err, result) {
    if(err) throw err;
    console.log('data inserted');
});

This issue explains the problem with bulk inserts for node-mysql.

You have a potential security issue. A cleaver person could steal data from your table by having the data.response.docs contain MySQL mands. Replace

var sql = "INSERT INTO table (id, code, country_name, city) VALUES ?";

with

var sql = "INSERT INTO table (id, code, country_name, city) VALUES (? ? ? ?)";

then replace

var query = connection.query(sql, [responseJson], function(err, result)

with

var query = connection.query(sql, [var1, var2, var3, var4], function(err, result)

Not a big deal from what it looks like your doing, but could be a huge deal later down the line for you.