Inserting multiple rows from JSON structure to MySQL

Question

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.

gnerkus · Accepted Answer · 2016-01-23 16:40:37Z

3

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.

edited Jan 23, 2016 at 16:40

answered Jan 23, 2016 at 16:29

gnerkus

12.2k7 gold badges54 silver badges76 bronze badges

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

5 Comments

Jeff Kilbride Over a year ago

I agree. I added an answer a few minutes ago, but didn't realize the docs object was already an array, so I deleted it. In any event, the stringify is the problem. +1.

corry Over a year ago

I've made these changes and there is again an 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 = 'l876s', ' at line 1

corry Over a year ago

I think that's the right solution, but I still have a problem with '' signs. Does anybody know how to eliminate this?

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 ''d3f3d', 'l876s', 'United States', '566.54.33.2', '{}', 'United ' at line 1

`

gnerkus Over a year ago

Where did the 566.54.33.2 come from?

corry Over a year ago

There are more properties in JSON structure. I just singled a few in example above.

Theronyouknow · Accepted Answer · 2016-06-02 19:55:12Z

3

You have a potential security issue. A cleaver person could steal data from your table by having the data.response.docs contain MySQL commands. 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.

answered Jun 2, 2016 at 19:55

Theronyouknow

311 bronze badge

Collectives™ on Stack Overflow

Inserting multiple rows from JSON structure to MySQL

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related