Node-Red SQlite- Using a Prepared Statement

SQlite-queryPassing 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.

sqlite-prepared-statment

The database node looks like this when using the topic for the database query:

sqlite-database-topic-query

And when using the prepared statement it looks like this.

sqlite-database-prepared-statement

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:

Click to rate this post!
[Total: 1 Average: 5]

Leave a Reply

Your email address will not be published. Required fields are marked *