# Replace ' with anything else for SQL

**URL:** <https://discourse.nodered.org/t/replace-with-anything-else-for-sql/64797>\
**Category:** General\
**Created:** [7 July 2022 12:37 UTC](https://discourse.nodered.org/t/replace-with-anything-else-for-sql/64797 "2022-07-07T12:37:06Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![smitterer](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smitterer/32/59179_2.png) [@smitterer](https://discourse.nodered.org/u/smitterer)\
**Post date:** [7 July 2022 12:37 UTC](https://discourse.nodered.org/t/replace-with-anything-else-for-sql/64797/1 "2022-07-07T12:37:06Z")

</div>

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:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/1/f19f370b9259cbf6efab395973221d8e01468c6d.png)

I also tried:

```auto
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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 July 2022 12:40 UTC](https://discourse.nodered.org/t/replace-with-anything-else-for-sql/64797/2 "2022-07-07T12:40:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![smitterer](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smitterer/32/59179_2.png) [@smitterer](https://discourse.nodered.org/u/smitterer)\
**Post date:** [7 July 2022 19:34 UTC](https://discourse.nodered.org/t/replace-with-anything-else-for-sql/64797/3 "2022-07-07T19:34:00Z")

</div>

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](https://flows.nodered.org/flow/687918dd5cb66a3bfc2a661e15ef4237) but I had to change the postgresql node to mysql: [node-red-contrib-stackhero-mysql (node) - Node-RED](https://flows.nodered.org/node/node-red-contrib-stackhero-mysql)  
Currently msg.topic is msg.payload from the sql (of the referenced flow)

Thanks

---

<div class="post-metadata">

**Author:** ![smitterer](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smitterer/32/59179_2.png) [@smitterer](https://discourse.nodered.org/u/smitterer)\
**Post date:** [14 July 2022 20:17 UTC](https://discourse.nodered.org/t/replace-with-anything-else-for-sql/64797/4 "2022-07-14T20:17:47Z")

</div>

I now tried removing all ' inside of csvs with:

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

return msg;

```

and

```auto
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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [14 July 2022 20:31 UTC](https://discourse.nodered.org/t/replace-with-anything-else-for-sql/64797/5 "2022-07-14T20:31:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![smitterer](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smitterer/32/59179_2.png) [@smitterer](https://discourse.nodered.org/u/smitterer)\
**Post date:** [14 July 2022 20:59 UTC](https://discourse.nodered.org/t/replace-with-anything-else-for-sql/64797/6 "2022-07-14T20:59:21Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/9/99753f784b3abe6aa22ddc1d2b0b0d5ed0facfd6.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [15 July 2022 06:16 UTC](https://discourse.nodered.org/t/replace-with-anything-else-for-sql/64797/7 "2022-07-15T06:16:53Z")

</div>

> [@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()`

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [13 September 2022 06:16 UTC](https://discourse.nodered.org/t/replace-with-anything-else-for-sql/64797/8 "2022-09-13T06:16:58Z")

</div>

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