Sqlite3 data insert problem

nerevar · 4 January 2022 09:11

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

nerevar · 4 January 2022 09:30

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

Colin · 4 January 2022 09:54

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?

nerevar · 4 January 2022 10:10

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?

Colin · 4 January 2022 10:21

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

nerevar · 4 January 2022 11:05

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

Colin · 4 January 2022 11:19

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.

nerevar · 4 January 2022 11:42

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

Colin · 4 January 2022 12:12

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.

nerevar · 4 January 2022 12:25

Yes, but I'm using a SQLite

flow:

sqlite insput and output:

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

E1cid · 4 January 2022 12:43

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);"

nerevar · 4 January 2022 12:53

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

E1cid · 4 January 2022 12:55

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":[]}]

nerevar · 4 January 2022 13:44

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

Colin · 4 January 2022 13:46

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);

E1cid · 4 January 2022 14:22

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

Colin · 4 January 2022 14:42

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

E1cid · 4 January 2022 14:48

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.

Colin · 4 January 2022 15:12

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

UnborN · 4 January 2022 18:46

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.

Topic		Replies	Views
Sqlite3 not installed or broken General	2	109	27 May 2023
Sqlite nodes not working after update? General	11	806	24 March 2021
SQLite Install from Manage Palette General	18	1145	14 July 2021
Can't get SQLite to install General	4	420	27 June 2021
Can't instal SQLite on node-red General	8	3332	30 December 2018

Sqlite3 data insert problem

Related topics