CODEWITHSUNDEEP
mysqljsonnode.jsinsert
pitu
pitu

Reputation: 820

json into mysql using node.js

i trying to insert json created in node.js into mysql, but there is a error in syntax, i am not able to rectify the error, any help will be appreciated

my code

flowController.on('2', function (_selfid,_participantId,_groupid,_allMemberContent) 
        {
    var allMemberDetailSQL= "SELECT spFunAllMemberNotificationDetails("+ _selfid + "," + _participantId +") as groupparticipants";
    console.log("allMemberDetailSQL"+allMemberDetailSQL);

client.query(allMemberDetailSQL,function(detailERROR,detailResult)
        {
            if (detailERROR)
                console.log("detailERROR "+ detailERROR);
            else 
            {

                var detailstr='';
                detailstr = JSON.stringify(detailResult);
                console.log('detailResult :'+ detailstr);
                console.log("detailResult "+detailResult[0].groupparticipants);
                var otherArray = [detailResult[0].groupparticipants];


                var _allMemberDetail = JSON.stringify({
                    selfid: _selfid,
                    groupid: _groupid, 
                    anArray: otherArray
                  });

                console.log("_allMemberDetail " +_allMemberDetail);

                var allMemberDetail = "'"+_allMemberDetail+"'";
                console.log("allMemberDetail "+allMemberDetail);
                client.query("INSERT INTO cmNotification (notificationSenderId, notificationReceiverId)"+"VALUES('"+_selfid+"','"+ _allMemberDetail+ "');", function(err, rows)
                         {
                            console.log("error insert "+err);
                            console.log("rows insert"+rows);
                            //connection.release();

                          });
            }

        });

});

console output

allMemberDetailSQLSELECT spFunAllMemberNotificationDetails(20,16) as groupparticipants
detailResult :[{"groupparticipants":"userid:'15',firstname:'pitu15',lastname:'',isfriend:'1',profilepicurl:''"}]
detailResult userid:'15',firstname:'pitu15',lastname:'',isfriend:'1',profilepicurl:''
_allMemberDetail {"selfid":"20","groupid":"15","anArray":["userid:'15',firstname:'pitu15',lastname:'',isfriend:'1',profilepicurl:''"]}
allMemberDetail '{"selfid":"20","groupid":"15","anArray":["userid:'15',firstname:'pitu15',lastname:'',isfriend:'1',profilepicurl:''"]}'
detailResult :[{"groupparticipants":"userid:'16',firstname:'pitu16',lastname:'',isfriend:'0',profilepicurl:''"}]
detailResult userid:'16',firstname:'pitu16',lastname:'',isfriend:'0',profilepicurl:''
_allMemberDetail {"selfid":"20","groupid":"15","anArray":["userid:'16',firstname:'pitu16',lastname:'',isfriend:'0',profilepicurl:''"]}
allMemberDetail '{"selfid":"20","groupid":"15","anArray":["userid:'16',firstname:'pitu16',lastname:'',isfriend:'0',profilepicurl:''"]}'
error insert 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 '15',firstname:'pitu15',lastname:'',isfriend:'1',profilepicurl:''"]}')' at line 1
rows insertundefined
error insert 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 '16',firstname:'pitu16',lastname:'',isfriend:'0',profilepicurl:''"]}')' at line 1
rows insertundefined

Upvotes: 3

Views: 2145

Answers (1)

Andrey Sidorov
Andrey Sidorov

Reputation: 25446

Use built in parameters escaping to prevent sql injection attack. "INSERT INTO ... SET ?" also makes life easier:

client.query("INSERT INTO cmNotification SET ?",  {notificationSenderId: _selfid, notificationReceiverId: _allMemberDetail}, function(err, rows) {
  // ...
});

Upvotes: 4

Related Questions