# SQL Issue inserting a record in a database

**URL:** <https://discourse.nodered.org/t/sql-issue-inserting-a-record-in-a-database/95114>\
**Category:** General\
**Created:** [4 February 2025 16:01 UTC](https://discourse.nodered.org/t/sql-issue-inserting-a-record-in-a-database/95114 "2025-02-04T16:01:34Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![BartZ](https://avatars.discourse-cdn.com/v4/letter/b/3ab097/32.png) [@BartZ](https://discourse.nodered.org/u/BartZ)\
**Post date:** [4 February 2025 16:01 UTC](https://discourse.nodered.org/t/sql-issue-inserting-a-record-in-a-database/95114/1 "2025-02-04T16:01:34Z")

</div>

Hi All,

Completly new to Node Red (latest version, just installed) but understand the working. It's a nice development environment but one can get vague error messages Like this one:  
"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 'DHT\_C\_Sensor' at line 1"

I've looked over end over and cannot find a clue. The function is quite simple

```auto
var dataArray = msg.payload.split(";");
var itype = dataArray[1];
var ihum = dataArray[2];
var itemp = dataArray[3];
var ihic = dataArray[4];
var irsrp = dataArray[5];
msg.payload = dataArray;

var msgDB = {};
msgDB.topic = "insert into DHT(Type,Hum,Temp,Hic,rsrp) values(?,?,?,?,?);"
msgDB.payload = [itype, ihum, itemp, ihic, irsrp];

```

I manually created the SQL string and executed it and there was no problem.  
The DataBase exist, the table exist, the connections are OK

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/c/1c39be62548fa6b38ee89b2740be926ba680c209.png)

Anyone could give me a hint where to look?

---

<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:** [4 February 2025 16:32 UTC](https://discourse.nodered.org/t/sql-issue-inserting-a-record-in-a-database/95114/2 "2025-02-04T16:32:46Z")

</div>

Start by setting debug 4 to Output Complete Message then look at what it contains. If you can't see the problem then show us what is there.

> [@BartZ](#):
>
> one can get vague error messages Like this one:  
> "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 'DHT\_C\_Sensor' at line 1"

I suspect that node-red is just passing on an error from the database driver, the text is probably not generated by node-red.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [4 February 2025 16:48 UTC](https://discourse.nodered.org/t/sql-issue-inserting-a-record-in-a-database/95114/3 "2025-02-04T16:48:32Z")

</div>

> [@BartZ](#):
>
> "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 'DHT\_C\_Sensor' at line 1"

Judging by your flow diagram, **msg.topic** is "DHT\_C\_Sensor", and that's the string the database is complaining about.

You construct your query as **msgDB** but you are possibly passing **msg** to the MySQL node.

---

<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:** [4 February 2025 17:00 UTC](https://discourse.nodered.org/t/sql-issue-inserting-a-record-in-a-database/95114/4 "2025-02-04T17:00:11Z")

</div>

Oh yes, good catch.

@BartZ, possibly at the end of the function you have  
`return msg`  
instead of  
`return msgDB`

However, it is good practice to modify and pass on the message passed in so you might be better with just

```auto
msg.topic = "insert into DHT(Type,Hum,Temp,Hic,rsrp) values(?,?,?,?,?);"
msg.payload = [itype, ihum, itemp, ihic, irsrp]
return msg

```

---

<div class="post-metadata">

**Author:** ![BartZ](https://avatars.discourse-cdn.com/v4/letter/b/3ab097/32.png) [@BartZ](https://discourse.nodered.org/u/BartZ)\
**Post date:** [5 February 2025 06:26 UTC](https://discourse.nodered.org/t/sql-issue-inserting-a-record-in-a-database/95114/5 "2025-02-05T06:26:00Z")

</div>

JBudd, Colin,

Thank you, it solved the problem!!  
To be quite honest, I copied (stolen/borrowed) the code. So my task for today: crash course Node Red! Really appriciate the help, thought coud do the quick route

Bart

---

<div class="post-metadata">

**Author:** ![BartZ](https://avatars.discourse-cdn.com/v4/letter/b/3ab097/32.png) [@BartZ](https://discourse.nodered.org/u/BartZ)\
**Post date:** [5 February 2025 06:33 UTC](https://discourse.nodered.org/t/sql-issue-inserting-a-record-in-a-database/95114/6 "2025-02-05T06:33:21Z")

</div>

Ps Colin,  
The debug 4 - Output Complete Message didn't give me more logical information. But good to know to set this on in case of ...

```auto
2/5/2025, 7:17:16 AMnode: debug 4
DHT_C_Sensor : msg : Object
object
topic: "DHT_C_Sensor"
payload: array[6]
0: "DHT01"
1: "C"
2: " 53.50"
3: "16.50"
4: "15.60"
5: " 123"
qos: 1
retain: false
_msgid: "ad43d54196eee62f"

2/5/2025, 7:17:16 AMnode: DHT
msg : error
"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 'DHT_C_Sensor' at line 1"

```

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [5 February 2025 07:31 UTC](https://discourse.nodered.org/t/sql-issue-inserting-a-record-in-a-database/95114/7 "2025-02-05T07:31:57Z")

</div>

Your SQL statement is a "prepared query" which is good form, it protects the database from SQL injection (I've learned that by rote, no idea how it actually works).  
I personally avoid the ?, ?, ?, ... syntax, preferring a prepared query with msg.payload as explicit key: value pairs.

So I would have written your function like this

```auto
const dataArray = msg.payload.split(";");
msg.payload = {
"itype": dataArray[1], 
"ihum": dataArray[2];
"itemp": dataArray[3],
"ihic": dataArray[4],
"irsrp": dataArray[5]
}

msg.topic = "insert into DHT (Type,Hum,Temp,Hic,rsrp) values (:itype, :ihum, :itemp, :ihic, :irsrp)"
return msg

```

Have fun with Node-red! 😀

---

<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:** [5 February 2025 09:15 UTC](https://discourse.nodered.org/t/sql-issue-inserting-a-record-in-a-database/95114/8 "2025-02-05T09:15:58Z")

</div>

> [@BartZ](#):
>
> Output Complete Message didn't give me more logical information.

Did it not show you more clearly that msg.topic is incorrect?

---

<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:** [26 February 2025 08:31 UTC](https://discourse.nodered.org/t/sql-issue-inserting-a-record-in-a-database/95114/9 "2025-02-26T08:31:22Z")

</div>

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