I want to inject a postgres node into a function node

General
1

I want to inject a postgres node into a function node.
For example, I injected the following query in the postgres node as shown below.
'
INSERT INTO pi_sensor(
sensor_serial_no,
topic,
payload
) VALUES (
'{{msg.sensor_serial_no}}',
'{{msg.topic}}',
'{{msg.payload}}'
)
'
I would like to put something like this in a function node and check the structure in debug.

Is it possible to write in function node?

2

You do not need a function node to do that. A template would work and use less resources.
e,g

[{"id":"745b7a4ec367a4af","type":"inject","z":"bac113592b6fd1c6","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"},{"p":"sensor_serial_no","v":"1234abc","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"test_topic","payload":"test_payload","payloadType":"str","x":120,"y":360,"wires":[["f7f2b86dc46f6310"]]},{"id":"f7f2b86dc46f6310","type":"template","z":"bac113592b6fd1c6","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO pi_sensor(\nsensor_serial_no,\ntopic,\npayload\n) VALUES (\n'{{sensor_serial_no}}',\n'{{topic}}',\n'{{payload}}'\n)","output":"str","x":320,"y":360,"wires":[["790447ce9e31f09b"]]},{"id":"790447ce9e31f09b","type":"debug","z":"bac113592b6fd1c6","name":"debug 324","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":490,"y":360,"wires":[]}]

But if you want a function node to do it, you can use a Template String

[{"id":"745b7a4ec367a4af","type":"inject","z":"bac113592b6fd1c6","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"},{"p":"sensor_serial_no","v":"1234abc","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"test_topic","payload":"test_payload","payloadType":"str","x":120,"y":360,"wires":[["dda9731b9c09bd4d"]]},{"id":"dda9731b9c09bd4d","type":"function","z":"bac113592b6fd1c6","name":"function 21","func":"msg.topic = `INSERT INTO pi_sensor(\nsensor_serial_no,\ntopic,\npayload\n) VALUES (\n'${msg.sensor_serial_no}',\n'${msg.topic}',\n'${msg.payload}'\n)`\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":310,"y":360,"wires":[["790447ce9e31f09b"]]},{"id":"790447ce9e31f09b","type":"debug","z":"bac113592b6fd1c6","name":"debug 324","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":490,"y":360,"wires":[]}]

You should also sanitize all variables used in the query , to prevent any sql injections.

3

It is generally not a good idea to generate SQL QUERY strings as you risk SQLi hacks.

The better way is to use parameters.

Your SQL would be something like:

INSERT INTO pi_sensor(
sensor_serial_no, topic, payload
) VALUES ( $sensor_serial_no, $topic, $payload )

Then use a change node to set msg.queryParameters.sensor_serial_no to the value of msg.payload.sensor_serial_no, msg.queryParameters.topic to the value of msg.topic and so on.

This way, you dont worry about quotes, or someone maliciously setting payload to " '); DROP pi_sensor;

NOTE: This assuming you are using node-red-contrib-postgresql. Other nodes will do it differently.

4

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.