How to encode text with strange symbols to work with MySQL queries?

Hello,
I am enjoying a lot this first week with n8n!

Today, I’m facing a problem that I don’t know how to solve. I need to write texts to a MySQL table. These texts can be wrote in different languages, so when the text contains “srange” symbols liek these (’ " %) the MySQL query node gets an error.

For example, this text in Catalan:

M’agrada molt l’article “Som el 99%” és molt interessant! :relaxed::heart::heart:

I tried adding .replace("'","''") after the reference to the text to replace the single quotation marks ' into two quotation marks '', because that’s how it should be done on MySQL. But what about the double quotation marks? And what about the % symbol?

Should I transform it to UTF8 o ANSI or something like these? How can I do it on n8n?

Thaaaanks! :slight_smile:

Ok, searching on this forum I found there is no n8n node to “prepare” MySQL text to avoid MySQL injections. Also, there is no JavaScript function to do it. So, what I ended doing is what I read here: replace all the dangerous characters.

My expression now looks like this:

{{$node["Get comment info"].json["text"].replace(/\\/g, "\\\\").replace(/\'/g, "\\\'").replace(/\"/g, "\\\"").replace(/\n/g, "\\\n").replace(/\r/g, "\\\r").replace(/\x00/g, "\\\x00").replace(/\x1a/g, "\\\x1a");}}

And it seems is working OK! What do you think about it?

Yes. If it’s working then you can go with this approach. But recommended way is to use with Function Node. It’ll be more clear.

// Code here will run only once, no matter how many input items there are.
// More info and help: https://docs.n8n.io/nodes/n8n-nodes-base.function
var input = $node["Get comment info"].json["text"];
// Loop over inputs and add a new field called 'myNewField' to the JSON of each one
for (item of items) {
  item.json.myNewField = input.replace(/\\/g, "\\\\").replace(/\'/g, "\\\'").replace(/\"/g, "\\\"").replace(/\n/g, "\\\n").replace(/\r/g, "\\\r").replace(/\x00/g, "\\\x00").replace(/\x1a/g, "\\\x1a");
}

// You can write logs to the browser console
console.log('Done!');

return items;

Please check the code and input variable value. I’m doing it from mobile. Quiet hard to check and format everything.

4 Likes

Thank you, I’ve done it this way! Much clearer! :slight_smile:

1 Like