Using mysql node but no joy with insert query

General
1

Here is the question, how do you use msg.payload to provided values for INSERT SQL statement?

I am using a node-red-node-mysql node. If I create a complete SQL INSERT statement, put it in msg.topic it works. For example:

INSERT table(`Me`, `You`) VALUES (`Good`, `Bad`);

But if I set msg.topic to:

INSERT table(Me, You) VALUES (?,?);

And msg.payload to:

['Good','Bad']

The INSERT fails. Parse error, that the SQL query has a syntax issue.
The issue is that the node documentation says this possible, and I see several google examples saying this is possible, but I can't get it to work.
image
I also tried to do

INSERT table(`Me`, `You`) VALUES (msg.payload[0], msg.payload[1]);

But I am sure something is not right with the above. I get an odd message about msg.payload[0] is not understood. Any suggestions welcome, thanks.

2

Can you put a debug node showing what is going into the sql node please, set it to show complete message and show us that and the configuration of the sql node. For the case where you are passing the values in the payload.
For the last case you need

msg.topic = "INSERT table(`Me`, `You`) VALUES (" + msg.payload[0] + "," + msg.payload[1] + ")";

or slightly neater

mag.topic = `INSERT table(`Me`, `You`) VALUES (${msg.payload[0]},${ msg.payload[1]})`;
1 Like
3

Actually, I decided that using a function was a better model, making it easier to debug, and got the following working. Given the using the ? mark notation was proving programmatic.

var theValues = msg.payload.split(',');
var theFields = msg.topic;

msg.topic = `INSERT INTO Ambient(`+theFields+`) VALUES (`+theValues+`);`;
msg.payload=null;

return msg;

The above of course send to the SQL node to process accordingly. I will test what you suggested as well. Thanks for the reply.

4

Make quite sure that the fields and values don't have any user inputted data in them. By doing it the way you have it would be feasible to inject sql into the query to trash your database contents. By using the ? syntax mysql knows that what is in there should be filtered and will not allow sql statements to get into the query.

5

Right once the given query is created, it is a static string, no substitution variables in the query, not avoiding potential injection. Not that any of the stuff I am doing is going to be visible to anything but my home network.

6

@Nodi.Rubrum might I suggest building your SQL in a template node. The attached flow has a change node to set msg.table to 'wp_users' (it's looking at a WordPress database) and then in the template node it puts the value in msg.table into the query. You can see the result in the debug output.

I find it is a much easier way to build SQL statements.

[{"id":"3c8991ce.43e63e","type":"change","z":"669c4b6d.12eeec","name":"","rules":[{"t":"set","p":"table","pt":"msg","to":"wp_users","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":420,"wires":[["5d58ca01.1ef7b4"]]},{"id":"a3d44765.3cb9f8","type":"debug","z":"669c4b6d.12eeec","name":"","active":true,"console":"false","complete":"true","x":750,"y":420,"wires":[]},{"id":"4b4ada0.dcab5a8","type":"inject","z":"669c4b6d.12eeec","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"","x":160,"y":420,"wires":[["3c8991ce.43e63e"]]},{"id":"5d58ca01.1ef7b4","type":"template","z":"669c4b6d.12eeec","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"select * from {{table}} ","output":"str","x":580,"y":420,"wires":[["a3d44765.3cb9f8"]]}]
1 Like
7

That is really slick. Great suggestion.

8

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