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

General
1

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.

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.

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.

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

It writes the data without errors but gives me

some text with 'single quotes' and "double quotes" 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.

2

Try:

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}
3

...edited... doing more tests.

4 
[{"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)

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

Try using msg.params.

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

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
image608×445 16.2 KB

6

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!

7

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