Sqlite3 data insert problem

General
1

Upon installing the sqlite vian node-red on new buster instalation, I noticed I cannot write to the db using binded payload.
When trying to run it from the command line it says: -bash: sqlite3: command not found
So, I got back to the node-red library (node-red-node-sqlite (node) - Node-RED) and saw I need to install it using provided command via npm.
Thats what I did and I still cannot run the sqlite3 from the command line.

There were some vulnerabilities which I don't know how to fix. I cannot unistall it from the node-red either because the node is already used cca 20x in all the flows.
Any help, please?

pi@RevPi100299:~/.node-red $ npm audit

                       === npm audit security report ===

┌──────────────────────────────────────────────────────────────────────────────┐
│                                Manual Review                                 │
│            Some vulnerabilities require your attention to resolve            │
│                                                                              │
│         Visit https://go.npm.me/audit-guide for additional guidance          │
└──────────────────────────────────────────────────────────────────────────────┘
┌───────────────┬──────────────────────────────────────────────────────────────┐
│ High          │ Arbitrary File Creation/Overwrite on Windows via             │
│               │ insufficient relative path sanitization                      │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Package       │ tar                                                          │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Patched in    │ >=4.4.18                                                     │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Dependency of │ node-red-node-sqlite                                         │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Path          │ node-red-node-sqlite > sqlite3 > node-gyp > tar              │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ More info     │ https://github.com/advisories/GHSA-5955-9wpr-37jh            │
└───────────────┴──────────────────────────────────────────────────────────────┘
┌───────────────┬──────────────────────────────────────────────────────────────┐
│ High          │ Arbitrary File Creation/Overwrite via insufficient symlink   │
│               │ protection due to directory cache poisoning using symbolic   │
│               │ links                                                        │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Package       │ tar                                                          │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Patched in    │ >=4.4.18                                                     │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Dependency of │ node-red-node-sqlite                                         │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Path          │ node-red-node-sqlite > sqlite3 > node-gyp > tar              │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ More info     │ https://github.com/advisories/GHSA-qq89-hq3f-393p            │
└───────────────┴──────────────────────────────────────────────────────────────┘
┌───────────────┬──────────────────────────────────────────────────────────────┐
│ High          │ Arbitrary File Creation/Overwrite via insufficient symlink   │
│               │ protection due to directory cache poisoning using symbolic   │
│               │ links                                                        │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Package       │ tar                                                          │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Patched in    │ >=4.4.16                                                     │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Dependency of │ node-red-node-sqlite                                         │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Path          │ node-red-node-sqlite > sqlite3 > node-gyp > tar              │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ More info     │ https://github.com/advisories/GHSA-9r2w-394v-53qc            │
└───────────────┴──────────────────────────────────────────────────────────────┘
┌───────────────┬──────────────────────────────────────────────────────────────┐
│ High          │ Arbitrary File Creation/Overwrite due to insufficient        │
│               │ absolute path sanitization                                   │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Package       │ tar                                                          │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Patched in    │ >=3.2.2                                                      │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Dependency of │ node-red-node-sqlite                                         │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Path          │ node-red-node-sqlite > sqlite3 > node-gyp > tar              │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ More info     │ https://github.com/advisories/GHSA-3jfq-g458-7qm9            │
└───────────────┴──────────────────────────────────────────────────────────────┘
┌───────────────┬──────────────────────────────────────────────────────────────┐
│ High          │ Arbitrary File Creation/Overwrite via insufficient symlink   │
│               │ protection due to directory cache poisoning                  │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Package       │ tar                                                          │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Patched in    │ >=3.2.3                                                      │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Dependency of │ node-red-node-sqlite                                         │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Path          │ node-red-node-sqlite > sqlite3 > node-gyp > tar              │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ More info     │ https://github.com/advisories/GHSA-r628-mhmh-qjhw            │
└───────────────┴──────────────────────────────────────────────────────────────┘
┌───────────────┬──────────────────────────────────────────────────────────────┐
│ Moderate      │ ReDoS in Sec-Websocket-Protocol header                       │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Package       │ ws                                                           │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Patched in    │ >=7.4.6                                                      │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Dependency of │ node-red-contrib-revpi-nodes                                 │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ Path          │ node-red-contrib-revpi-nodes > ws                            │
├───────────────┼──────────────────────────────────────────────────────────────┤
│ More info     │ https://github.com/advisories/GHSA-6fc8-4gx4-v693            │
└───────────────┴──────────────────────────────────────────────────────────────┘
found 6 vulnerabilities (1 moderate, 5 high) in 240 scanned packages
  6 vulnerabilities require manual review. See the full report for details.
pi@RevPi100299:~/.node-red $ ^C
pi@RevPi100299:~/.node-red $ npm audit fix
up to date in 7.271s

20 packages are looking for funding
  run `npm fund` for details

fixed 0 of 6 vulnerabilities in 240 scanned packages
  6 vulnerabilities required manual review and could not be updated
2 
Welcome to Node-RED
===================
4 Jan 10:26:26 - [info] Node-RED version: v2.1.4
4 Jan 10:26:26 - [info] Node.js  version: v14.18.2
4 Jan 10:26:26 - [info] Linux 4.19.95-rt38-v7 arm LE
4 Jan 10:26:27 - [info] Loading palette nodes
4 Jan 10:26:31 - [info] mDashboard version 2.19.4-beta started at /mui
4 Jan 10:26:31 - [info] Settings file  : /home/pi/.node-red/settings.js
4 Jan 10:26:31 - [info] HTTP Static    : /home/pi/.node-red/static
4 Jan 10:26:31 - [info] Context store  : 'memoryOnly' [module=memory]
4 Jan 10:26:31 - [info] Context store  : 'file' [module=localfilesystem]
4 Jan 10:26:31 - [info] User directory : /home/pi/.node-red
4 Jan 10:26:31 - [warn] Projects disabled : editorTheme.projects.enabled=false
4 Jan 10:26:31 - [info] Flows file     : /home/pi/.node-red/flows.json
4 Jan 10:26:31 - [info] Server now running at http://127.0.0.1:1880/
4 Jan 10:26:32 - [warn]
---------------------------------------------------------------------
Your flow credentials file is encrypted using a system-generated key.
If the system-generated key is lost for any reason, your credentials
file will not be recoverable, you will have to delete it and re-enter
your credentials.
You should set your own key using the 'credentialSecret' option in
your settings file. Node-RED will then re-encrypt your credentials
file using your chosen key the next time you deploy a change.
---------------------------------------------------------------------
4 Jan 10:26:35 - [info] Starting flows
4 Jan 10:26:37 - [info] Started flows
4 Jan 10:26:41 - [info] [sqlitedb:73ac2ee8378b3e02] opened ODIN_petr_log.db ok
4 Jan 10:26:41 - [info] [sqlitedb:a6765b51df58e4ea] opened quilters_data.db ok
4 Jan 10:26:41 - [info] [sqlitedb:b17145b6.55f138] opened quilters_data.db ok
4 Jan 10:26:41 - [info] [sqlitedb:eca8059d.a2a5a8] opened /home/pi/ODIN_petr_log.db ok
4 Jan 10:26:41 - [error] [sqlitedb:88903112.5c2a5] failed to open /var/www/ODIN_petr_log.db
4 Jan 10:26:41 - [error] [sqlitedb:1c25415d.b8427f] failed to open /home/pi/sqlite/nodered
4 Jan 10:26:41 - [error] [function:round value] TypeError: Cannot read property 'toFixed' of undefined
4 Jan 10:26:42 - [error] [file in:maint. storage] Error: ENOENT: no such file or directory, open '/home/pi/storage_data_maintenance.json'
4 Jan 10:26:42 - [info] [mqtt-broker:9592ef48.17181] Connected to broker: mqtt://192.168.2.194:1883
4 Jan 10:26:42 - [info] [mqtt-broker:debb8505.d64ad8] Connected to broker: mqtt://192.168.2.192:1883
4 Jan 10:26:42 - [info] [mqtt-broker:a611a9a9.f3df88] Connected to broker: mqtt://192.168.2.195:1883
4 Jan 10:26:42 - [info] [mqtt-broker:b1a22c0f.9ab4c] Connected to broker: mqtt://192.168.2.193:1883
4 Jan 10:26:42 - [info] [mqtt-broker:8cbd7ede.f8fda] Connected to broker: mqtt://192.168.2.193:1883
4 Jan 10:26:42 - [error] [function:chart refresh] TypeError: Cannot read property 'toFixed' of undefined
4 Jan 10:26:42 - [info] [mqtt-broker:464b285.50965d8] Connected to broker: mqtt://localhost:1883
4 Jan 10:26:42 - [info] [mqtt-broker:1e2a8d13.55b9a3] Connected to broker: mqtt://localhost:1883
4 Jan 10:26:42 - [error] [function:export preparation] TypeError: Cannot read property 'toFixed' of undefined

I did try npm rebuild and it seems it finished OK:

make: Leaving directory '/home/pi/.node-red/node_modules/sqlite3/build'
sqlite3@5.0.2 /home/pi/.node-red/node_modules/sqlite3

pi@RevPi100299:~/.node-red $ npm list sqlite3
node-red-project@0.0.1 /home/pi/.node-red
└─┬ node-red-node-sqlite@1.0.1
  └── sqlite3@5.0.2

However, I still don't undestand why it wont run...

3

What makes you think it is a problem with the install? It has successfully opened four of your databases. It has failed to open the last two, that may be a problem with access permissions or similar. Are you intentionally opening databases in a number of different folders?

4

No, I have several old flows with old paths to the DB-s. The ones it opened are relevant.

This is the syntax I'm using to fiull up the fields (This all worked OK before the update):

msg = {}; 
msg1 = {
  topic: "INSERT INTO users_connection_data (ip, connected, date) VALUES (?, ?, ?);",
  payload: ["192", 2, 15444444444423]
};
return msg1;

So If DB-s are opened why it wont write in them? Permission? Ill try changing it to sudo chmod u+x, but why it wont open in the terminal?

6

How do you know it didn't write into the db?

7

Well, because when nothing is written except for the ID:

[{"id":1,"ip":null,"connected":null,"name":null,"date":null},{"id":2,"ip":null,"connected":null,"name":null,"date":null},{"id":3,"ip":null,"connected":null,"name":null,"date":null},{"id":4,"ip":null,"connected":null,"name":null,"date":null},{"id":5,"ip":null,"connected":null,"name":null,"date":null}]

It work OK inline, but bind not

8

What message comes out from the mysql node when you try to insert (set the debug node to output complete message).
Also add a Catch node linked to the mysql node and see if it catches anything.

Please also edit the title of this thread to something more meaningful, since it relates to being unable to insert into mysql, not an install issue.

9

This is the input to the sqlite node:

1/4/2022, 12:37:55 PMnode: 4688f27f756472b1
INSERT INTO users_connection_data (ip, connected, date) VALUES (?, ?, ?); : msg : Object
object
topic: "INSERT INTO users_connection_data (ip, connected, date) VALUES (?, ?, ?);"
payload: array[3]
_msgid: "a9b9c904d58e0053"

and this is the output of the sqlite node:

1/4/2022, 12:37:55 PMnode: 2cb86db46f40a6ed
INSERT INTO users_connection_data (ip, connected, date) VALUES (?, ?, ?); : msg : Object
object
topic: "INSERT INTO users_connection_data (ip, connected, date) VALUES (?, ?, ?);"
payload: array[0]
_msgid: "a9b9c904d58e0053"

No errors from the sqlite node, not does the catch node catches anything from this node..

10

Can you post a screenshot of those please? I don't fully understand what you have posted.

Also show us the database schema please.

[Edit] If you look at the description of the node you will see that for prepared queries the column names should be between back ticks.

11

Yes, but I'm using a SQLite

flow:

image
image806×215 16.5 KB

sqlite insput and output:
image

sqlite SELECT * FROM: (note the * was a direct insert without a binded payload)

image
image218×811 20.5 KB

12

Reading the node info it does not say that the query values are sent in msg.payload.
Have you tried sending a msg.topic with values added.

"INSERT INTO users_connection_data (ip, connected, date) VALUES ('192', 2, 1234567890);"
13

Yes, In this case it writes them into the DB:
image

14

Then that is your solution, add the values to the topic query.
e.g.

[{"id":"21147119.119cfe","type":"inject","z":"c791cbc0.84f648","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[\"192\", 2, 1234567890]","payloadType":"json","x":150,"y":340,"wires":[["f7f65daf.01eed8"]]},{"id":"f7f65daf.01eed8","type":"template","z":"c791cbc0.84f648","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"\"INSERT INTO users_connection_data (ip, connected, date) VALUES ('{{payload.0}}', {{payload.1}}, {{payload.2}});\"","output":"str","x":370,"y":340,"wires":[["611cf9ef.5e9d68"]]},{"id":"611cf9ef.5e9d68","type":"debug","z":"c791cbc0.84f648","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":600,"y":360,"wires":[]}]
1 Like
15

Nice solution - Unfortunatelly I would need to change my other 9 flows accordingly to accept this kind of input:

    topic: "INSERT INTO Mammut(\
    date, timeshift, leng, pcs, qop, qha, odo, qha_no_break, odo_no_break, util_abs, util_rel_break, util_rel_no_break, \
    hands_break, door_break, both_break, noth_break, hands_no_break, door_no_break, both_no_break, noth_no_break, \
    service_time_break, service_time_no_break, chart, chart_no_break, live_table) \
    VALUES (? ,? , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);",
    payload: [
        date_end_ts, timeshift, leng, pcs_made, qop, qha, odo, qha_no_break, odo_no_break, util_abs, 
        util_rel_break, util_rel_no_break, hands_break, door_break, both_break, noth_break, hands_no_break, 
        door_no_break, both_no_break, noth_no_break, service_time_break, service_time_no_break, 
        JSON.stringify(chart_breaks), JSON.stringify(chart_no_breaks), live_table
    ]

Everything worked OK until I did an update - It is a bit frustrating that node-red/nodejs updates break the sqlite node...

16

That is indeed a valid point.
If you look at the help text for the node it tells you how to do prepared queries

When using Normal or Prepared Statement, the query must be entered in the node config.

Pass in the parameters as an object in msg.params for Prepared Statement. Ex:
msg.params = {
    $id:1,
    $name:"John Doe"
}
Parameter object names must match parameters set up in the Prepared Statement. If you get the error SQLITE_RANGE: bind or column index out of range be sure to include $ on the parameter object key.
The SQL query for the example above could be: insert into user_table (user_id, user) VALUES ($id, $name);
1 Like
17

Colin I do not see this info in the node info at node-red flows

18

Click on the node in the flow and look in the right hand pane. Click the Book symbol at the top if necessary.

19

Should it be in the help documentation on node-red flows and github. Seems an oversite. It's not until you install the node that you get relevant info.

20

As the powers that be will no doubt say, PRs are always welcome.

2 Likes
21

I did some test with the sqlite node and it seems that another way to pass parameters, that is close to your syntax, is not with the ? but with $

msg.topic = `INSERT INTO test (name, surname) VALUES ($1, $2)`
msg.payload = ["John", "Smith"]
return msg;

Dont know if that changed with the newer version. it is possible

Do some test in one function and If it helps use Ctrl-F Find/Replace ? with $ and then add numbers next to them.

1 Like