SQLite transactions without SQL injection vulnerability?

[vrilcode]

I want to have multiple SQL statements within a transaction in SQLite. As far as I see in the description of the SQLite node, I can have prepared statements, which protects against SQL injections, but I can't use these prepared statements to build a or within a transaction.
On the other hand I can have a "batch" SQL query, which allows me to have arbitrary SQL statements (including transactions), which I would generate with a Mustache template. But this variant can't escape all given values against SQL injection threats.

Is there any solution for that?

[jbudd]

Hi @vrilcode, welcome to the forum.

@vrilcode

I can't use these prepared statements to build a or within a transaction.

This very simple SELECT statement works for me

select * from vessels 
where mmsi between $mmsi1 and $mmsi2
or name = $name

msg.params = { $mmsi1: "235002446", $mmsi2: "235056506", $name: "NORANAH" }

Can you show us a relatively simple example of a statement with transactions that does not work?

[vrilcode]

@jbudd Thank you!

I forgot to mention, that I want to insert multiple rows within that transaction. In raw SQL it would look something like that:

BEGIN TRANSACTION;
 INSERT INTO TABLE x VALUES (...);

 INSERT INTO TABLE y VALUES (...);
 INSERT INTO TABLE y VALUES (...);
 :
 .
COMMIT;

I could generate such a batch of SQL statements with a templating node, but that would lead to SQL injection issues for the values. I wonder, if there is any solution for that with a prepared statement or another injection-safe solution.

[Steve-Mcl]

Have you tried combining the methods?

e.g.

msg.topic

"
BEGIN TRANSACTION;
 INSERT INTO TABLE x VALUES  ($name, $surname);
 INSERT INTO TABLE y VALUES  ($id, $value);
COMMIT;
"

msg.params

msg.params = {
    $id:1,
    $value:123,
    $name:"John",
    $surname:"Doe"
}

[jbudd]

How would you setup the SQLite node for that example @Steve-Mcl - "via msg.topic" or "prepared statement"?

[jbudd]

For me, this simple insert works as long as there is no transaction.

But changing the prepared statement to

BEGIN TRANSACTION;
INSERT INTO table1 (id, description) VALUES ($id, $description);
COMMIT;

results in Error: SQLITE_RANGE: column index out of range

I hesitate to offer this as a work-around but at first sight it's working to commit or rollback both inserts

[{"id":"a2efb037919b24b4","type":"template","z":"27c79c7c9cdd6344","name":"SQL via python","field":"payload","fieldType":"msg","format":"handlebars","syntax":"plain","template":"# The entire python script wrapped in single quotes\n# So NO SINGLE QUOTES in values or names :(\npython -c '\nimport sqlite3\n\n# Connect to the database\nconn = sqlite3.connect(\"mytest.db\")\n# Create a cursor object\ncursor = conn.cursor()\n\n# Create a parameterized query for insertion\nquery = \"INSERT INTO table2 (id, name, description) VALUES (?, ?, ?)\";\n# User data to insert\nuser_data = (5, \"John Smith\", \"Historian\");\n# Execute the query with the user data\ncursor.execute(query, user_data);\n\n# another parameterized query for insertion\nquery = \"INSERT INTO table1 (id, description) VALUES (?, ?)\";\nuser_data = (5, \"Historian\");\ncursor.execute(query, user_data);\n\n# Commit the changes\nconn.commit();\n'   // closing single quote","output":"str","x":1020,"y":440,"wires":[["4bb712f48228efa9","93642ce1173bfe83"]]},{"id":"c420499d359b1df5","type":"inject","z":"27c79c7c9cdd6344","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":980,"y":380,"wires":[["a2efb037919b24b4"]]},{"id":"93642ce1173bfe83","type":"exec","z":"27c79c7c9cdd6344","command":"","addpay":"payload","append":"","useSpawn":"false","timer":"","winHide":false,"oldrc":false,"name":"","x":1210,"y":440,"wires":[["15d27e6a29753e41"],["15d27e6a29753e41"],[]]},{"id":"4bb712f48228efa9","type":"debug","z":"27c79c7c9cdd6344","name":"python script","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1190,"y":380,"wires":[]},{"id":"15d27e6a29753e41","type":"debug","z":"27c79c7c9cdd6344","name":"exec output","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1370,"y":440,"wires":[]}]

You can pass values into that python script as message properties eg msg.name = "Bill Stickers" and using mustache template format

[vrilcode]

Thanks for recommendations, but it's not about inserting two rows into different tables. I have to insert an arbitrary number of rows into table "y" (data comes in an array):

BEGIN TRANSACTION;
 INSERT INTO TABLE x VALUES (...);

 INSERT INTO TABLE y VALUES (...);
 INSERT INTO TABLE y VALUES (...);
 INSERT INTO TABLE y VALUES (...);
 INSERT INTO TABLE y VALUES (...);
 INSERT INTO TABLE y VALUES (...);
 :
 .
COMMIT;

If I wouldn't need the surrounding transaction, I would split the array data into single message payloads and commit single prepared statements through SQLite node. At the moment I see only three possibilities:

1. Avoid the transaction and accept side effects of not having a surrounding transaction for "all or nothing". In case of failure I have to "clean up" in database.
2. Use a template node to generate the SQL code and accept that value data (which is coming from outside) could theoretically be used to make an SQL injection.
3. Solve it with a function node or hand over the database handling to some custom JavaScript code outside of Node-RED (via REST API).

I'm leaning towards path no. 3.

[vrilcode]

@jbudd

I hesitate to offer this as a work-around but at first sight it's working to commit or rollback both inserts

Yes, that's what I will probably do. Solve the problem outside of Node-RED, just with another approach. It's a bit sad, that it's not working directly in Node-RED, but every system has its limits.