# SQLite transactions without SQL injection vulnerability?

**URL:** <https://discourse.nodered.org/t/sqlite-transactions-without-sql-injection-vulnerability/87999>\
**Category:** General\
**Created:** [16 May 2024 03:23 UTC](https://discourse.nodered.org/t/sqlite-transactions-without-sql-injection-vulnerability/87999 "2024-05-16T03:23:47Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![vrilcode](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/vrilcode/32/99050_2.png) [@vrilcode](https://discourse.nodered.org/u/vrilcode)\
**Post date:** [16 May 2024 03:23 UTC](https://discourse.nodered.org/t/sqlite-transactions-without-sql-injection-vulnerability/87999/1 "2024-05-16T03:23:47Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [16 May 2024 08:00 UTC](https://discourse.nodered.org/t/sqlite-transactions-without-sql-injection-vulnerability/87999/2 "2024-05-16T08:00:40Z")

</div>

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

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

```

```auto
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?

---

<div class="post-metadata">

**Author:** ![vrilcode](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/vrilcode/32/99050_2.png) [@vrilcode](https://discourse.nodered.org/u/vrilcode)\
**Post date:** [16 May 2024 10:51 UTC](https://discourse.nodered.org/t/sqlite-transactions-without-sql-injection-vulnerability/87999/3 "2024-05-16T10:51:30Z")

</div>

@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:

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [16 May 2024 12:00 UTC](https://discourse.nodered.org/t/sqlite-transactions-without-sql-injection-vulnerability/87999/4 "2024-05-16T12:00:18Z")

</div>

Have you tried combining the methods?

e.g.

`msg.topic`

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

```

  

`msg.params`

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

```

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [16 May 2024 12:38 UTC](https://discourse.nodered.org/t/sqlite-transactions-without-sql-injection-vulnerability/87999/5 "2024-05-16T12:38:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [16 May 2024 12:48 UTC](https://discourse.nodered.org/t/sqlite-transactions-without-sql-injection-vulnerability/87999/6 "2024-05-16T12:48:25Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/6/b63706033caed9b9b8238c8acd540eb5c450a15c.png)

But changing the prepared statement to

```auto
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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/8/98d6bc8ef51ddd93f0776cc7d6d269bdd1110092.png)

```auto
[{"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  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/3/936bddf700d4284ff97d1cf043375550a2aa9a48.png)

---

<div class="post-metadata">

**Author:** ![vrilcode](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/vrilcode/32/99050_2.png) [@vrilcode](https://discourse.nodered.org/u/vrilcode)\
**Post date:** [16 May 2024 16:40 UTC](https://discourse.nodered.org/t/sqlite-transactions-without-sql-injection-vulnerability/87999/7 "2024-05-16T16:40:13Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![vrilcode](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/vrilcode/32/99050_2.png) [@vrilcode](https://discourse.nodered.org/u/vrilcode)\
**Post date:** [16 May 2024 16:45 UTC](https://discourse.nodered.org/t/sqlite-transactions-without-sql-injection-vulnerability/87999/8 "2024-05-16T16:45:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [14 August 2024 16:46 UTC](https://discourse.nodered.org/t/sqlite-transactions-without-sql-injection-vulnerability/87999/9 "2024-08-14T16:46:12Z")

</div>

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.