Passing database command in the topic risks a SQL injection as the payload isn’t sanitised unless you do it.
An alternative and somewhat easier method is to use a prepared statement.
When using a prepared statement to data is passed in the msg.params object and the command is created in the node itself.
The easiest way of seeing this is by showing you the two methods side by side.
The screen shot below shows the function node that prepares the data for storing data with the code for both methods shown.
The database node looks like this when using the topic for the database query:
And when using the prepared statement it looks like this.
Note: Because it wasn’t possible to show the complete query in the screen shot I put onto the image as red text.
You should note that we have just covered a database insert but we could have done a query or update using the prepared statement.
The disadvantage of the prepared statement is that we need a SQlite node for each type of database query.
The advantage is that it is more secure and avoids the headaches of data preparation in the query which can be very difficult with lots of parameters.
Related Tutorials:
- Working with JSON Data And JavaScript Objects in Node-Red
- Using the Node Red Status Node
- Node-Red MQTT Data and Topic Logger
- Using the Node-Red Function Node- Beginners Guide
- Storing Data in Node-Red Variables
- Understanding and Using The Node-Red Message Object