Replace ' with anything else for SQL


I'm getting an error when trying to import data from a csv into a MariaDB:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ANTHRAZIT', 'undefined', '9,16', '0,000', '0,000', '0,000', '1,000', '1' at line 1

Therefore I'm trying to remove the ' in the SQL statement (which is generated) prior to connecting to the DB using:

I also tried:

var str = msg.topic;

var res = str.replace(/[']/gi, '');

msg.topic = res;

return msg;

and various variations.

Can anyone pls tell me how to search for ' and replace it with nothing or any character which will not crash my INSERT into SQL?


Use query parameters instead of dynamic SQL Strings.

Not only does it remove the need to strip characters from your strings, it protects against SQL Injection.

Thanks for your answer.

Would you mind explaining this or let me know what I have to search for?
I really have very little knowledge about SQL, just trying to import a huge csv into a DB based on Upload large CSV file to SQL database (flow) - Node-RED but I had to change the postgresql node to mysql: node-red-contrib-stackhero-mysql (node) - Node-RED
Currently msg.topic is msg.payload from the sql (of the referenced flow)


I now tried removing all ' inside of csvs with:

msg.payload = msg.payload.replace(/'/g, '');

return msg;


var changer = msg.payload;

msg.payload = changer.replace(/'/g, '');

return msg;

Always getting "TypeError: ........replace is not a function"

Does anyone know why this is not a function / why it's not working?
I put this function in front of the sql function.


Is msg.payload a String? Previously you were working with msg.topic.

msg.payload is an array and inside of these objects are values from a csv, chunked via chunks-to-lines node of which the output is text.
After the sql function, msg.topic is set to be the same as msg.payload so it functions with the sql node.

replace() is a function that must be called on a string, not an array. If you want to call it on all the elements of an array then look at the function

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.