Dashboard and MS SQL database - number and text input nodes

[Naz]

Hi I am very new to node-red. Im testing something with MS SQL.

I made a dashboard with numeric and text input. (User_ID,First_Name,Last_Name) .

I want it to insert the information into MS SQL.

So I was wondering what is the easiest way to do this ? Can I somehow use the name of my inputs and tell it that's my value when I do a insert?

What I have been trying is connecting the input nodes to a button called Done. When I press the button its sends it to a function node that's processing it and is connected to MS SQL node. Haven't got it to work yet.

Using inject nodes works great so I know my database is working but the point is I want the data from the input from dashboard.

My function looks like this:

msg.topic="INSERT INTO [Test_03].[dbo].[User_Name] (User_ID,First_Name,Last_Name) VALUES ('" + msg.payload.User_ID + "','" + msg.payload.First_Name + "','" + msg.payload.Last_Name + "')";
return msg;

[Steve-Mcl]

Hi Naz.

Firstly, one thing to realise is that messages NEVER arrive at the same time. So where you have user_id, first_name and last_name linked to a done button - this is never going to work.

Secondly, you risk sql injection by concatinating strings to generate SQL Queries.

To simplify user input and to avoid SQL Injection, use the ui_form - it has sends all values entered in one msg. Also, use node-red-contrib-mssql-plus as it allows you to use parameters (that negate SQL injection)

Example...

---

@Naz

I am very new to node-red.

I recommend every new user watches this playlist: Node-RED Essentials. The videos are done by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in about 1 hour. A small investment for a lot of gain.

[Steve-Mcl]

If you dont want to use the ui_form then you need to store the individual values entered and recover them when the done button is pressed e.g....