Unable to extract Jason data into SQL

Rama Tito
Rama Tito used Ask the Experts™
on
Hi, i am runing program on Node-Red. The data format in JASON. I am trying to pull out "payload": "Open" and insert into mysql. No errors but the data in sql state as [object OBJECT] . Did attached the database field.

test2 : msg : Object
object
topic: "test2"
payload: "Open"
qos: 0
retain: false
_topic: "test2"
_msgid: "2356ede6.954ba2"

Open in new window


var temp;

temp ={"payload":msg.payload};
msg.topic = "insert into ctesting (data1) values ('"+temp+"')";


return msg;

Open in new window

db.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
Yup. Cause temp is an object. Not a string containing JSON.

Node-RED is built on node.js, thus this

var obj = {"payload":msg.payload};
var json = JSON.stringify(obj);
var escapeSingleQuotes = json.Replace("'", "''");
msg.topic = "INSERT INTO ctesting (data1) VALUES ('" + json + "')";
return msg;

Open in new window

should work.
Rama TitoProgrammer

Author

Commented:
"TypeError: json.Replace is not a function"

json.png

I did tryout with this code: -
var obj = msg.payload;

var json = JSON.stringify(obj);

msg.topic = "INSERT INTO ctesting (data1) VALUES ('" + json + "')";
return msg;

Open in new window


its works but data in database updated with " " (double quote). Need to remove that.
Rama TitoProgrammer

Author

Commented:
I do manage to get it with following code after google on w3school

var obj = msg.payload;
var json = JSON.parse(obj);

var Device = json.Device_ID;
var Line = json.Line_ID;
var sum = json.Count;
var dt = json.Date_Now;

msg.topic = "INSERT INTO ctesting (data1,data2,data3,data4) VALUES ('" + Device + "','" + Line + "','" + sum + "', '"+ dt+"')";
return msg;

Open in new window

Rama TitoProgrammer

Author

Commented:
Hi Experts,

Its help me to kick start from where i am. Thank you
ste5anSenior Developer

Commented:
"replace" starts with an lower-case r, sorry for that typo.

But it's important, that you use it, to avoid SQL injection.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial