# Using mysql node but no joy with insert query

**URL:** <https://discourse.nodered.org/t/using-mysql-node-but-no-joy-with-insert-query/29234>\
**Category:** General\
**Created:** [30 June 2020 13:36 UTC](https://discourse.nodered.org/t/using-mysql-node-but-no-joy-with-insert-query/29234 "2020-06-30T13:36:23Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nodi.Rubrum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nodi.rubrum/32/107482_2.png) [@Nodi.Rubrum](https://discourse.nodered.org/u/Nodi.Rubrum)\
**Post date:** [30 June 2020 13:36 UTC](https://discourse.nodered.org/t/using-mysql-node-but-no-joy-with-insert-query/29234/1 "2020-06-30T13:36:23Z")

</div>

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:

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

```

But if I set msg.topic to:

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

And msg.payload to:

```auto
['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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/8/b81a99246f514bc578bee6d8222a3c51775f0e14.png)  
I also tried to do

```auto
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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [30 June 2020 14:44 UTC](https://discourse.nodered.org/t/using-mysql-node-but-no-joy-with-insert-query/29234/2 "2020-06-30T14:44:37Z")

</div>

> [@Nodi.Rubrum](#):
>
> node-red-node-mysql

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

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

```

or slightly neater

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

---

<div class="post-metadata">

**Author:** ![Nodi.Rubrum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nodi.rubrum/32/107482_2.png) [@Nodi.Rubrum](https://discourse.nodered.org/u/Nodi.Rubrum)\
**Post date:** [30 June 2020 15:10 UTC](https://discourse.nodered.org/t/using-mysql-node-but-no-joy-with-insert-query/29234/3 "2020-06-30T15:10:34Z")

</div>

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.

```auto
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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [30 June 2020 15:15 UTC](https://discourse.nodered.org/t/using-mysql-node-but-no-joy-with-insert-query/29234/4 "2020-06-30T15:15:27Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Nodi.Rubrum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nodi.rubrum/32/107482_2.png) [@Nodi.Rubrum](https://discourse.nodered.org/u/Nodi.Rubrum)\
**Post date:** [30 June 2020 15:24 UTC](https://discourse.nodered.org/t/using-mysql-node-but-no-joy-with-insert-query/29234/5 "2020-06-30T15:24:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [30 June 2020 16:04 UTC](https://discourse.nodered.org/t/using-mysql-node-but-no-joy-with-insert-query/29234/6 "2020-06-30T16:04:49Z")

</div>

@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.

```auto
[{"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"]]}]

```

---

<div class="post-metadata">

**Author:** ![Nodi.Rubrum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nodi.rubrum/32/107482_2.png) [@Nodi.Rubrum](https://discourse.nodered.org/u/Nodi.Rubrum)\
**Post date:** [30 June 2020 16:41 UTC](https://discourse.nodered.org/t/using-mysql-node-but-no-joy-with-insert-query/29234/7 "2020-06-30T16:41:29Z")

</div>

That is really slick. Great suggestion.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [29 August 2020 16:41 UTC](https://discourse.nodered.org/t/using-mysql-node-but-no-joy-with-insert-query/29234/8 "2020-08-29T16:41:32Z")

</div>

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