# Howto SQL-Query with quotes

**URL:** <https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155>\
**Category:** General\
**Created:** [6 March 2021 13:57 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155 "2021-03-06T13:57:46Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Muschelpuster](https://avatars.discourse-cdn.com/v4/letter/m/b3f665/32.png) [@Muschelpuster](https://discourse.nodered.org/u/Muschelpuster)\
**Post date:** [6 March 2021 13:57 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/1 "2021-03-06T13:57:46Z")

</div>

As on different times discussed here often SQL needs quotes. If I want to satisfy MySQL completely I need other quotes for DB-, table or column-names as for data. ASCII 0x060 or 0x027.  
I found no function in JSONata to add a ASCII to a string. I think it will possible with the function-node but I found no example, and I’m not fit in programming JavaScript. Most tutorials have examples which working without quotes.  
I solved it now with the change-node. Here I build my SQL-query with JSONata and use placeholders for the quotes. Finally I replace the placeholders with the right values.  
I guess there is a better way, but I need help by finding it. What is best practice?

Niels

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [6 March 2021 14:44 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/2 "2021-03-06T14:44:49Z")

</div>

Not sure what you mean with the ascii values, but you could use a template node to write the query with quotes and use the mustache formatting for expanding any incoming properties.

example;

 ![Screenshot 2021-03-06 at 15.47.29](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/b/dbeae2846aed34864a27da802afb5d8320825693.jpeg)

(where the incoming tablename comes from msg.payload.tablename)

---

<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:** [6 March 2021 15:08 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/3 "2021-03-06T15:08:26Z")

</div>

> [@Muschelpuster](#):
>
> What is best practice?

The best practice is using using sql params (prepared statements) instead of building sql queries.

e.g...

```javascript
//setup params
var f1 = msg.payload.user_id;
var f2 = Date.now();
var f3 = `some text with 'single quotes' and "double quotes" inside and the payload equals ${msg.payload.user_text_input}`;

//build query
msg.topic = "INSERT INTO myTable (user_id, entry_time, comment) VALUES (?,?,?)";
msg.payload = [f1, f2, f3]; //values as params
return msg;

```

↑ NOTE: this uses [template literals](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals) to aid writing strings with quotes inside.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [6 March 2021 16:23 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/4 "2021-03-06T16:23:45Z")

</div>

> [@Steve-Mcl](#):
>
> `msg.topic = "INSERT INTO myTable (user_id, entry_time, comment) VALUES (?,?,?)";`

I think, strictly, that should be

```auto
msg.topic = "INSERT INTO `myTable` (`user_id`, `entry_time`, `comment`) VALUES (?,?,?)";

```

---

<div class="post-metadata">

**Author:** ![Muschelpuster](https://avatars.discourse-cdn.com/v4/letter/m/b3f665/32.png) [@Muschelpuster](https://discourse.nodered.org/u/Muschelpuster)\
**Post date:** [6 March 2021 17:53 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/5 "2021-03-06T17:53:15Z")

</div>

Thanks folks,

Colin is right:

> [@Colin](#):
>
> I think, strictly, that should be
> 
> ```auto
> msg.topic = "INSERT INTO `myTable` (`user_id`, `entry_time`, `comment`) VALUES (?,?,?)";
> 
> ```

If you look at it exactly it should be:

```auto
msg.topic = "INSERT INTO `myTable` (`user_id`, `entry_time`, `comment`) VALUES ('ID','TIME','COMMENT')";

```

And here is the difference in the quotes. Left from VALUES it is ` and on the right site it's '

I think the solution of bakman2 is perfect. Until today I have not seen this but it works easy and great.  
In my case I get different information quality. So in most messages are not values for all fields are send. In this solution this is also very easy. I create the SQL-string by the template and after that I replace all '' with NULL by a change-node. So 2 nodes with a very low codes are generating easy the perfect string. The next advantage for long statements is the ability to copy a well formatted string from my database (tools) and edit it easy.

Niels

---

<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:** [6 March 2021 18:15 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/6 "2021-03-06T18:15:15Z")

</div>

My bad, I thought you asked for best practices?

You have chosen the slower and potentially unsafe solution.

Building your own SQL strings is both slower and potentially open to SQL injection.

---

<div class="post-metadata">

**Author:** ![Muschelpuster](https://avatars.discourse-cdn.com/v4/letter/m/b3f665/32.png) [@Muschelpuster](https://discourse.nodered.org/u/Muschelpuster)\
**Post date:** [6 March 2021 18:43 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/7 "2021-03-06T18:43:36Z")

</div>

Thanks Steve for your comment.  
Yes I don‘t thought about this points and I can‘t judge this.  
Why SQL injection is not possible at your solution? If I get a parameter with injection code it will detected automatically?  
I think if every parameter is enclosed by quotes the injection will be written as value if the type and the size of the column accepts it...

Niels

---

<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:** [6 March 2021 18:46 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/8 "2021-03-06T18:46:20Z")

</div>

> [@Muschelpuster](#):
>
> Why SQL injection is not possible at your solution? If I get a parameter with injection code it will detected automatically

"How can prepared statements protect from SQL injection attacks? - Stack Overflow" [security - How can prepared statements protect from SQL injection attacks? - Stack Overflow](https://stackoverflow.com/questions/8263371/how-can-prepared-statements-protect-from-sql-injection-attacks)

---

<div class="post-metadata">

**Author:** ![Muschelpuster](https://avatars.discourse-cdn.com/v4/letter/m/b3f665/32.png) [@Muschelpuster](https://discourse.nodered.org/u/Muschelpuster)\
**Post date:** [6 March 2021 19:00 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/9 "2021-03-06T19:00:20Z")

</div>

Thank you Steven,

I was editing my post in same time as you posted. So, the link told me that I I'm not wrong if all parameters quoted. Maybe that is a special think of mysql that you can also quote not only string-values, but it's nice from this view.  
So I'm pretty shure that I'm safe with this:

```auto
INSERT INTO `my_table` (`my_value1`, `my_value2`) VALUES ('{{payload.value1}}' , '{{payload.value2}}');

```

The performance is another view and I will deeper dive into your solution. I think I understand now how it works.  
I create 3 querys at one time now. Is it here possible too because the msg.payload values are counting on for the 2nd and 3rd statement?

Niels

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [6 March 2021 21:28 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/10 "2021-03-06T21:28:51Z")

</div>

> [@Muschelpuster](#):
>
> So I'm pretty shure that I'm safe with this:
> 
> ```auto
> INSERT INTO `my_table` (`my_value1`, `my_value2`) VALUES ('{{payload.value1}' , '{{payload.value2}}');
> 
> ```

Consider if the previous node does

```auto
msg.payload.value2 = " '); DROP TABLE `my_table`; "

```

The ` ');` will complete your INSERT statement, then the next statement will be executed.

---

<div class="post-metadata">

**Author:** ![Muschelpuster](https://avatars.discourse-cdn.com/v4/letter/m/b3f665/32.png) [@Muschelpuster](https://discourse.nodered.org/u/Muschelpuster)\
**Post date:** [7 March 2021 10:02 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/11 "2021-03-07T10:02:55Z")

</div>

Damn it, I simply don't have enough criminal creativity for this 😉

Niels

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [7 March 2021 10:48 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/12 "2021-03-07T10:48:27Z")

</div>

The conclusion is that is is better to get into the habit of using the `?` syntax, then you should be safe (from this type of attack anyway). In fact it is often easier because the sql string itself is fixed and you just need to put the data into the payload whithout having to format them.

---

<div class="post-metadata">

**Author:** ![Muschelpuster](https://avatars.discourse-cdn.com/v4/letter/m/b3f665/32.png) [@Muschelpuster](https://discourse.nodered.org/u/Muschelpuster)\
**Post date:** [15 March 2021 16:21 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/13 "2021-03-15T16:21:36Z")

</div>

I have still a problem here:

> [@Steve-Mcl](#):
>
> ```auto
> msg.topic = "INSERT INTO myTable (user_id, entry_time, comment) VALUES (?,?,?)";
> return msg;
> 
> ```

I get a message from the mySQL-Node at the debug, that msg.payload is not a string. If I set a debug-node on this point of my flow I see a string at msg.payload.

Niels

---

<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:** [15 March 2021 16:30 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/14 "2021-03-15T16:30:13Z")

</div>

Show me what is in msg.payload. the payload should be an array with values that match the SQL (in this case 4 element array containing 4 values)  
Is your table really called `myTable`?  
Also, show me the table schema.

---

<div class="post-metadata">

**Author:** ![Muschelpuster](https://avatars.discourse-cdn.com/v4/letter/m/b3f665/32.png) [@Muschelpuster](https://discourse.nodered.org/u/Muschelpuster)\
**Post date:** [15 March 2021 17:29 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/15 "2021-03-15T17:29:00Z")

</div>

I'm a little bit irritated. This works now with my small table, which name is of course not 'myTable' 😉  
I don't know what I changed...

So I have only another problem with my larger table:

```auto
CREATE TABLE `test-daten` (
  `d_event` char(16) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_time` int(11) NOT NULL,
  `d_ts` int(16) DEFAULT NULL,
  `ref` varchar(50) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_dir` varchar(16) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_src_if` varchar(16) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_dst_if` varchar(16) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_src_cgpn` varchar(35) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_src_dgpn` varchar(35) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_src_cdpn` varchar(35) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_dst_cgpn` varchar(35) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_dst_dgpn` varchar(35) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_dst_cdpn` varchar(35) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_bcaps` varchar(32) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_cause` varchar(32) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_xcoder` varchar(32) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_rcoder` varchar(32) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_xstats` varchar(32) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_rstats` varchar(32) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_alert_time` int(11) DEFAULT NULL,
  `d_connect_time` int(11) DEFAULT NULL,
  `d_disc_time` int(11) DEFAULT NULL,
  `d_srvid` varchar(16) COLLATE latin1_german1_ci DEFAULT NULL,
  `d_start_time` int(11) DEFAULT NULL,
  `d_result` varchar(10) COLLATE latin1_german1_ci NOT NULL,
  KEY `Zeitraum` (`d_start_time`,`d_disc_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci 

```

Here is the content of my function:

```auto
var value01 = msg.cdr.event;
var value02 = msg.cdr.time;
var value03 = msg.cdr.ts;
var value05 = msg.cdr.ref;
var value06 = msg.cdr.dir;
var value07 = msg.cdr.src_if;
var value08 = msg.cdr.dst_if;
var value09 = msg.cdr.src_cgpn;
var value10 = msg.cdr.src_dgpn;
var value11 = msg.cdr.src_cdpn;
var value12 = msg.cdr.dst_cgpn;
var value13 = msg.cdr.dst_dgpn;
var value14 = msg.cdr.dst_cdpn;
var value15 = msg.cdr.bcaps;
var value16 = msg.cdr.xcoder;
var value17 = msg.cdr.rcoder;
var value18 = msg.cdr.xstats;
var value19 = msg.cdr.rstats;
var value20 = msg.cdr.alert_time;
var value21 = msg.cd.connect_time;
var value22 = msg.cdr.disc_time;
var value23 = msg.cdr.srv_id;
var value24 = msg.cdr.start_time;
var value25 = msg.cdr.result;
msg.tpoic = "INSERT INTO `test-data` (`d_event`, `d_time`, `d_ts`, `ref`, `d_dir`, `d_src_if`, `d_dst_if`, `d_src_cgpn`, `d_src_dgpn`, `d_src_cdpn`, `d_dst_cgpn`, `d_dst_dgpn`, `d_dst_cdpn`, `d_bcaps`, `d_cause`, `d_xcoder`, `d_rcoder`, `d_xstats`, `d_rstats`, `d_alert_time`, `d_connect_time`, `d_disc_time`, `d_srvid`,`d_start_time`,`d_result`) values ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
msg.payload = [value01, value02, value03, value05, value06, value07,value08, value09, value10, value11, value12, value13, value14, value15, value16, value17, value18, value19, value20, value21, value22, value23, value24, value25];
return msg;

```

The problem is here, that not all values are delivered in all cases. So I get a type-error:

```auto
TypeError: Cannot read property 'connect_time' of undefined

```

Thats right for me - but not helpful. So I tried to use the Nullish Coalescing Operator:

```auto
var value01 = msg.cdr.event ?? null;

```

But the script-window gives a warning and it does not working. I think it makes no sense or it is not possible to send null-values in this way of creating a SQL-query?  
So I guess I have to build it over a check-function?

Niels

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [15 March 2021 17:32 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/17 "2021-03-15T17:32:18Z")

</div>

> [@Muschelpuster](#):
>
> `value21 = msg.cd.connect_time`

Is there a typo there? cd or cdr?

---

<div class="post-metadata">

**Author:** ![Muschelpuster](https://avatars.discourse-cdn.com/v4/letter/m/b3f665/32.png) [@Muschelpuster](https://discourse.nodered.org/u/Muschelpuster)\
**Post date:** [15 March 2021 17:34 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/18 "2021-03-15T17:34:17Z")

</div>

Thanks Colin, this is a typo, cdr is right. I have not seen this because I fail earlier on missed values.

Niels

---

<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:** [15 March 2021 17:58 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/19 "2021-03-15T17:58:54Z")

</div>

> [@Muschelpuster](#):
>
> Thats right for me - but not helpful. So I tried to use the Nullish Coalescing Operator:
> 
> ```auto
> var value01 = msg.cdr.event ?? null;
> 
> ```
> 
> But the script-window gives a warning and it does not working

Are you using node v14? Nullish coalescing requires V14. Forget about the editor showing a warning (ace editor doesn't understand but the function code still operates. )

You could of course use the trinary operator...

```auto
var value01 = msg.cdr.event ? msg.cdr.event : null;

```

---

<div class="post-metadata">

**Author:** ![Muschelpuster](https://avatars.discourse-cdn.com/v4/letter/m/b3f665/32.png) [@Muschelpuster](https://discourse.nodered.org/u/Muschelpuster)\
**Post date:** [15 March 2021 18:40 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/20 "2021-03-15T18:40:38Z")

</div>

Thank you Steve,

I'm on node-version 12.21, but it sounds easy what you wrote. But it did not wor, so I tried it a little bit longer:

```auto
var value21 = typeof(msg.cd.connect_time) !== "undefined" ? msg.cd.connect_time : null;

```

But with the same result:

```auto
TypeError: Cannot read property 'connect_time' of undefined

```

Niels

---

<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:** [15 March 2021 18:59 UTC](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155/21 "2021-03-15T18:59:39Z")

</div>

> [@Muschelpuster](#):
>
> `TypeError: Cannot read property 'connect_time' of undefined`

Isnt that the same error @Colin pointed out?...

`msg.cd.connect_time` ❌  
`msg.cdr.connect_time` ✔

[Next page](https://discourse.nodered.org/t/howto-sql-query-with-quotes/42155.md?page=2)