MSSQL node-red installation problem

General
1

Hello, just wanna ask; is the node-red-contrib-mssql still usable? I saw that its last update was more than 3 years ago, and it fails when I tried installing it. Or maybe there's another problem that keeps me from installing the node? I'm quite new to Node-RED, it will make me happy if someone could help. Thanks

2

I tried recently to load this module, and short of breaking open the original source of module and tracking down what is wrong, not likely.

This node uses the query operation against the configured database. This does allow both INSERTS and DELETES. By its very nature it allows SQL injection.

Although the newer option, node-red-node-mysql, comments on only insert and update queries, you can craft any SQL statement you want, so I use it for select queries as well. It is current, last update a few months ago, if memory serves.

3

mssql != mysql

4

Oh, nuts... I did miss read that. Yeah, MS-SQL has some differences in SQL syntax and how it replies to queries. I supported both for years... every time MS would deviate, would drive the developers nuts.

5

There are a number of issues with node-red-contrib-mssql use node-red-contrib-mssql-plus instead - most of the bugs have been weeded out and there are tonnes of improvements.

Improvements include...

  • Built in help and examples
  • Updated dependencies
  • Perform multiple queries in one go & get back multiple recordsets (depends on the queries sent)
  • Supports stored procedures
  • Use env vars in the config node for all fields (including credentials). e.g...
    • Server {{{SQL_IP}}}
    • Password {{{SQL_PW}}}
  • Use mustache in your SQL queries including msg, flow and global context. e.g...
    • SELECT TOP {{{payload.maxRows}}} * FROM [MyTable] WHERE Name = '{{{flow.name}}}' AND quantity <= {{{global.maxQty}}}
    • View the final query (mustache rendered into values) in msg.query to understanding what happened to your {{{mustache}}} parameters
  • Choose between throwing an error to the catch node or outputting an error property in msg.error
  • Additional properties are in the msg object (use a debug node with "complete msg object" set to see whats available)

plus more (hence the plus monica)

6

Using mustache to avoid potential injection? Plus not in NPM? I don't see updated module in palette install list?

7

I do

image
image701Ă—440 25.2 KB

8

Who said anything about using mustache to avoid SQL injection?

And as colin points out - it is there!

9

I forgot the sarcastic emoji? The plus... I see it now... I must have fat fingered something when I searched.

10

So yes, it is possible to cause sql injection if you hook up user settable variables to the mustache parts however, if a user has access to node-red, irregardless of mustache, they can just as easily poke a drop database into the node - and that goes for any / all xxSQL nodes.

Mustache is for convenience (no sarcasm at all there)

Containment must be made by security and sanity checks on user input.

A future improvement may permit named properties - but as of writing, it does not.

11

I am really looking forward to the future improvement. It was not fun avoiding the injection scenario.

12

Best way to avoid injection is to only use prepared statements. More efficient as well.

13

Hello everyone, thanks for all your reply. I am using Raspberry Pi, it seems my problem was with the incorrect time of my pi, thus having an error with the installation. I thought the problem was with the node itself, I am very sorry for all the trouble. Anyways, thanks for all the help, and thanks @Steve-Mcl for the information regarding the mssql and mssql-plus.

14

Parameters and execute mode now supported in mssql-plus V0.5.0

1 Like
15

Yahooooo! Yes, Yes, Yes! .... [Too much?]

1 Like
16

Not enough if you ask me :wink:

1 Like
17

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