Howto SQL-Query with quotes

General
1

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

2

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
Screenshot 2021-03-06 at 15.47.291750×500 72.3 KB

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

1 Like
3

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

e.g...

//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 to aid writing strings with quotes inside.

4

I think, strictly, that should be

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

Thanks folks,

Colin is right:

If you look at it exactly it should be:

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

6

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.

7

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

8

"How can prepared statements protect from SQL injection attacks? - Stack Overflow" security - How can prepared statements protect from SQL injection attacks? - Stack Overflow

9

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:

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

10

Consider if the previous node does

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

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

1 Like
11

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

Niels

12

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.

13

I have still a problem here:

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

14

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.

15

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

So I have only another problem with my larger table:

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:

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:

TypeError: Cannot read property 'connect_time' of undefined

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

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

17

Is there a typo there? cd or cdr?

18

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

Niels

19

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

var value01  = msg.cdr.event ? msg.cdr.event : null;
20

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:

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

But with the same result:

TypeError: Cannot read property 'connect_time' of undefined

Niels

21

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

msg.cd.connect_time :x:
msg.cdr.connect_time :heavy_check_mark: