get JSON data from mysql using nodejs mysql module

Question

I have a large JSON object which i am reading from a file as text, parsing it as JSON and storing in my mysql database in blob data type. when i do SELECT * FROM *table* in mysql console i see [object Object] in the table and uploading JSON as a string give 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 '1000GenomeFreq": "", "dbsnpFreq": "",
"labValidationInfo": null, "yesFrequ' at line 1

How do i upload JSON to a MySQL database using node.js?

var id=0;
var json={};
var name,des;
io.on('connection', function(socket){
  socket.on('username', function(un){
    console.log("Username: "+un);
    var l=un.length;
    console.log(l);
    json=JSON.parse(un);
    name=json.label.name;
    des=json.label.description;
    console.log(name);
    switch(name){
      case 'VUS_I':id=3;
      break;    
      case 'VUS_II':id=4;
      break;
      case 'VUS_III':id=5;
      break;
      case 'VUS_IV':id=6;
      break;
    }   
    connection.query('insert into variant_card(data,effectID)values("'+json+'",'+id+')', function(err,result) {
      if(err) {
        console.log('adsfdas'+err);
        socket.emit('wru');
      } else {
        if(result==false){
          console.log("Error uploading file");
          socket.emit('wru');
        } else {
          console.log("File uploaded into database");
          socket.emit('done');
        }
      }
    });
    //socket.emit('pwd');
  });
});

part of a JSON file is

{
  "1000GenomeFreq":"",
  "dbsnpFreq":"",
  "labValidationInfo":null,
  "yesFrequency":1,
  "functionallyValidated":false,
  "mutationType":"changeThis",
  "conservationScores":null,
  "isDominantSegregating":true,
  "literatureKbCount":0,
  "exomeServerFreq":"",
  "rsID":"",
  "notations":[{
    "nmTranscripts":["NM_033507"],
    ...

mscdex · Accepted Answer · 2014-06-09 12:33:42Z

2

That is exactly why you should never concatenate values directly into your query (SQL injection attacks).

What you should be doing instead is using placeholders:

connection.query('insert into variant_card set ?',
                 { data: JSON.stringify(json), effectID: id },
                 function(err,result) {
  // ...
});

edited Jun 9, 2014 at 12:33

answered Jun 9, 2014 at 12:23

mscdex

107k15 gold badges201 silver badges159 bronze badges

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

1 Comment

mscdex Over a year ago

see my edit. you need to stringify it since it's no longer JSON.

Collectives™ on Stack Overflow

get JSON data from mysql using nodejs mysql module

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related