Any Possible way to escape single quotes in MySQL Node before inserting?

Questions
1

Inserting a word with a single quote like this hello’s MySQL thinks it’s SQL injection.

The Possible way to skip this is to escape it like this Hello’s
Means adding \ before ’

This is my JSON output. Sometimes the word with a single quote coming in all three.

Selection_303

I Tried this code with Function Node.

let word = $node["Set"].json["0"]["word"];
let definition = $node["Set"].json["0"]["definition"];
let pronunciation = $node["Set"].json["0"]["pronunciation"];
items[0].json.word = word.replace(/'/g, "\\'");
items[1].json.pronunciation = pronunciation.replace(/'/g, "\\'");
items[2].json.definition = definition.replace(/'/g, "\\'");
return items;
2

No there is sadly no special option for that on the MySQL-Node. So what you are doing looks good. You can improve that a little bit by doing this:

items.forEach(item => {
  for (const key of Object.keys(item.json)) {
    item.json[key] = item.json[key].replace(/'/g, "\\'");
  }
});
return items;

That will replace it for all keys no matter how they are named.

4

Getting error bro.

Selection_306
Selection_306910×363 59.4 KB

5

This fails because the value of the property item.json[key] is not a string.

You can try to convert all values to string this way

(item.json[key] + “”)

and apply replace later.

Hope this helps.

6

Exactly what @Miquel_Colomer said. Your example data showed only strings that is why I wrote code that handles strings. It will not work if there are objects, arrays, numbers, booleans, undefined or nulls.

1 Like