Replace ' with anything else for SQL

[smitterer]

Hi,

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?

Thanks

[Steve-Mcl]

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.

[smitterer]

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)

Thanks

[smitterer]

I now tried removing all ' inside of csvs with:

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

return msg;

and

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.

Thanks

[Colin]

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

[smitterer]

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.

[Colin]

@smitterer

msg.payload is an array

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 Array.map()