json into mysql using node.js

Question

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

can u log the entire query in console console.log("Qry :INSERT INTO cmNotification (notificationSenderId, notificationReceiverId)"+"VALUES('"+_selfid+"','"+ _allMemberDetail+ "'););" — Abhik Chakraborty
– Abhik Chakraborty, Commented Apr 10, 2014 at 9:36
var sql="INSERT INTO cmNotification (notificationSenderId, notificationReceiverId)"+"VALUES("+_selfid+","+ allMemberDetail+ ");" — pitu
– pitu, Commented Apr 10, 2014 at 9:53
no it does not tell anything.. if possible try to get the query which is getting executed. — Abhik Chakraborty
– Abhik Chakraborty, Commented Apr 10, 2014 at 10:03

Andrey Sidorov · Accepted Answer · 2014-04-10 12:10:20Z

4

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) {
  // ...
});

answered Apr 10, 2014 at 12:10

Andrey Sidorov

25.6k5 gold badges65 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

json into mysql using node.js

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related