# Writing text that contains single and double quotes to an Sqlite table

**URL:** <https://discourse.nodered.org/t/writing-text-that-contains-single-and-double-quotes-to-an-sqlite-table/95014>\
**Category:** General\
**Created:** [30 January 2025 23:34 UTC](https://discourse.nodered.org/t/writing-text-that-contains-single-and-double-quotes-to-an-sqlite-table/95014 "2025-01-30T23:34:46Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![tribbles3](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tribbles3/32/98470_2.png) [@tribbles3](https://discourse.nodered.org/u/tribbles3)\
**Post date:** [30 January 2025 23:34 UTC](https://discourse.nodered.org/t/writing-text-that-contains-single-and-double-quotes-to-an-sqlite-table/95014/1 "2025-01-30T23:34:46Z")

</div>

I need to write data to sqlite that contains single and double quotes. I was able to INSERT the data with the following code using a function node and the data is written to the table with all the quotes intact.

```auto
let id = 155;
let item = `this contains "double" and 'single' quotes`;
msg.topic = `INSERT INTO test (id, item) VALUES ($id, $item)`;
msg.payload = [id, item];

```

HOWEVER, I am unable to get UPDATE to work with the same data. I tried the following code in a function node and it works fine as long as the data does not contain quotes or double quotes, if it does, it errors out.

```auto
let id = 155;
let item = `this contains "double" and 'single' quotes`;
msg.topic = `UPDATE test SET item = '${item}' WHERE id = ${id}`;
msg.payload = [item];

```

I also tried using a mustache template.

```auto
UPDATE test SET item = '{{payload.item}}' WHERE id = {{payload.id}};

```

It writes the data without errors but gives me

```auto
some text with &#39;single quotes&#39; and &quot;double quotes&quot; inside

```

which is not what I'm looking for. That is why I started researching doing it in a function node and was able to get it to work with INSERT.

---

<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:** [30 January 2025 23:41 UTC](https://discourse.nodered.org/t/writing-text-that-contains-single-and-double-quotes-to-an-sqlite-table/95014/2 "2025-01-30T23:41:52Z")

</div>

Try:

```auto
let id = 155;
let item = `this contains "double" and 'single' quotes`;
msg.topic = `UPDATE test SET item = $item WHERE id = $id`;
msg.payload = {item, id}

```

---

<div class="post-metadata">

**Author:** ![tribbles3](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tribbles3/32/98470_2.png) [@tribbles3](https://discourse.nodered.org/u/tribbles3)\
**Post date:** [31 January 2025 00:37 UTC](https://discourse.nodered.org/t/writing-text-that-contains-single-and-double-quotes-to-an-sqlite-table/95014/3 "2025-01-31T00:37:51Z")

</div>

...edited... doing more tests.

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [31 January 2025 00:50 UTC](https://discourse.nodered.org/t/writing-text-that-contains-single-and-double-quotes-to-an-sqlite-table/95014/4 "2025-01-31T00:50:13Z")

</div>

```auto
[{"id":"163817304b4190ae","type":"function","z":"ffc4fcc1699193f0","name":"function 5","func":"let id = 155;\nlet item = `this contains \"double\" and 'single' quotes`;\nmsg.payload = {item, id}\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":540,"y":3320,"wires":[["865efe4f69dc1537"]]},{"id":"2816994119259f26","type":"inject","z":"ffc4fcc1699193f0","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":360,"y":3320,"wires":[["163817304b4190ae"]]},{"id":"18c5588914b38824","type":"debug","z":"ffc4fcc1699193f0","name":"debug 2881","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"topic","statusType":"msg","x":910,"y":3320,"wires":[]},{"id":"865efe4f69dc1537","type":"template","z":"ffc4fcc1699193f0","name":"set msg.topic","field":"topic","fieldType":"msg","format":"sql","syntax":"mustache","template":"UPDATE test SET item = `{{{payload.item}}}` WHERE id = {{payload.id}}","output":"str","x":710,"y":3320,"wires":[["18c5588914b38824"]]}]

```

does this work? not sure this is the right method

or this.. (i asked google gemini)

```auto
let id = 155;
let item = `this contains "double" and 'single' quotes`;
msg.topic = `UPDATE test SET item = '${item}' WHERE id = '${id}'`;
msg.payload = {item, id}
return msg;

```

---

<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:** [31 January 2025 01:00 UTC](https://discourse.nodered.org/t/writing-text-that-contains-single-and-double-quotes-to-an-sqlite-table/95014/5 "2025-01-31T01:00:00Z")

</div>

Try using msg.params.

A simplified version of this (no WHERE clause) works for me

```auto
let id = 155
let item = `This has 'single' and "double" quotes`
msg.params = {$item: item, $id: id}
return msg;

```

and this sqlite node config

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

---

<div class="post-metadata">

**Author:** ![tribbles3](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tribbles3/32/98470_2.png) [@tribbles3](https://discourse.nodered.org/u/tribbles3)\
**Post date:** [31 January 2025 01:29 UTC](https://discourse.nodered.org/t/writing-text-that-contains-single-and-double-quotes-to-an-sqlite-table/95014/6 "2025-01-31T01:29:48Z")

</div>

Thanks jbudd that worked. During my searching for an answer I saw someone mention using prepared statements but I passed it by planning to come back to it and never did, ugh. Well your specific example explained it perfectly.

Thanks everyone for the replies!

---

<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 February 2025 01:30 UTC](https://discourse.nodered.org/t/writing-text-that-contains-single-and-double-quotes-to-an-sqlite-table/95014/7 "2025-02-14T01:30:21Z")

</div>

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