Combining "form" and "dropdown" payload data and building a mysql query

General
1

Hello. I am trying to build an MYSQL query from user input "form" and "dropdown" nodes.

2020-07-20-105649_1920x1080_scrot
2020-07-20-105649_1920x1080_scrot1920×1080 259 KB

2020-07-20-104339_1920x1080_scrot
2020-07-20-104339_1920x1080_scrot1920×1080 81.8 KB

I am using "change" nodes and "join" node to build an array out of 3 inputs that the user needs to give : Device,Item,Serial. Then I use function node to build a query that I will be sending to mysql, however, the function node does not recognise the payload input that I give:

device = msg.payload[0]
item = msg.payload[1]
serial = msg.payload[2]

msg.topic="INSERT INTO pack_to_light (Device,Item,Serial) VALUES ('${device}','${item}',${serial})";

return msg;

Is this not correct way to assign payload array to a function?

The output that I get:


{"payload":"Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '{serial})' at line 1"

For some reason, the problem happens when I try to insert data as integer. It gives me an error even though I have declared "serial" as integer on my database

However, when i change the Serial variable to VARCHAR instead of integer and add brackets, the sql querry works, but it injects the following:

2020-07-20-110146_1920x1080_scrot
2020-07-20-110146_1920x1080_scrot1920×1080 203 KB

instead of actual payload data, it have injected {device}, {item}, ${serial}

2

To use variables in a string, change the double quotes to backticks.

ie.

msg.topic="INSERT INTO pack_to_light (Device,Item,Serial) VALUES ('${device}','${item}',${serial})";

should become:

msg.topic=`INSERT INTO pack_to_light (Device,Item,Serial) VALUES ('${device}','${item}',${serial})`;
3

Please don't open a second thread about the same topic: How to pass payload data from user "form" to function as it wastes peoples time.

I am closing this thread