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