Inserting dynamic string with astrophes to SQL Server

Hello everyone, and thanks for reading and helping in advance.
I have a flow that watches for excel files and inserts the data dynamically to an sql server database.
My problem begins when some of the strings in the excel files have astrophes, and sometimes they dont. As mentioned, its dynamic.
The astrophes are not inserted well into the SQL DB, instead of "O'brien" for example it inserts "O#39&brien".

This is the template im using to insert the data to the database-
INSERT INTO dbo.name1(Num_ID,Date,Customer_Number,Customer_Name);
VALUES ('{{Num_ID}}','{{Date}}','{{Customer_Number}}','{{Customer_Name}}');

I've tried replacing all the astrophes in the above template to double quotes (") in order to cancel the astrophe and that caused syntax errors.

I dont mind even removing the astrophes from the strings, so I added a change node in which I search for ' and replace it with blank. However, it leads to syntax errors no matter which format I use (msg.payload, msg.payload.Customer_Name, msg.Customer_Name).

I am new to this and have been struggling with this for over a month. I highly appreciate any help. Thanks.

Have you tried escaping the ' by making it two single quotes ''

[{"id":"ed11a70d.5838f8","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"O'brien","payloadType":"str","x":550,"y":1580,"wires":[["195cafcb.40d0b8"]]},{"id":"195cafcb.40d0b8","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"change","p":"payload","pt":"msg","from":"'","fromt":"str","to":"''","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":760,"y":1580,"wires":[["26818ad7.51cf7e"]]},{"id":"26818ad7.51cf7e","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1020,"y":1600,"wires":[]}]

I seem to remember reading here that to stop that you can use triple braces instead of double {{{.....}}}. It stops it converting special characters to html entities.

Tried doing that, received the following error
RequestError: Unclosed quotation mark after the character string ');'.
Cant quite figure out why because it seems right.

Ah, now the quote is getting through but it is confusing the query, you presumably need to do something to stop that. I don't know SQL server in detail. Start by adding a debug node showing what is going into the db node and post that here, and someone will probably tell you what to do, if you can't work it out. Googling for something like
escape quotes in sql server
would likely find it.

If you are using the MSSQL-PLUS node, simply use parameters. No escaping necessary & will avoid SQL injection possibilities.

E.g... Dashboard and MS SQL database - number and text input nodes - #2 by Steve-Mcl

1 Like

+1 for that suggestion.

I upgraded to MSSQL-PLUS and I can not see any difference.
I dont see the parameters or the query options at all.
What am I missing?

Did you uninstall the old version, restart node-red, refresh browser?

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