# Insert text from web to Mysql, and show it on website securely

**URL:** <https://discourse.nodered.org/t/insert-text-from-web-to-mysql-and-show-it-on-website-securely/93590>\
**Category:** General\
**Tags:** security, mysql\
**Created:** [30 November 2024 18:28 UTC](https://discourse.nodered.org/t/insert-text-from-web-to-mysql-and-show-it-on-website-securely/93590 "2024-11-30T18:28:46Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![hbb999](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/hbb999/32/55150_2.png) [@hbb999](https://discourse.nodered.org/u/hbb999)\
**Post date:** [30 November 2024 18:28 UTC](https://discourse.nodered.org/t/insert-text-from-web-to-mysql-and-show-it-on-website-securely/93590/1 "2024-11-30T18:28:46Z")

</div>

I send an url to our clients to give a feedback about our service: [https://mycompany.com/evaluate?user\_id=1234](https://mycompany.com/evaluate?user_id=1234)

They can write some sentences into a textarea, and I send data back with POST method.

I insert it with parameterized query:

```auto
msg.payload = [user_id, text]
insert into evaluate (user_id, text} values ( ?,? )

```

Is this method safe against MySQL injection?

Then I show the result on a website, by replacing all dangerous characters:

```auto
msg.payload = msg.payload
.replace(/&/g, "&amp;")
.replace(/</g, "&lt;")
.replace(/>/g, "&gt;")
.replace(/"/g, "&quot;")
.replace(/'/g, "&#x27;")

```

Is this safe against XSS?

All together: what is the best practice to insert a user defined text into database, and to show it on a website? All steps are done in Node-red.

[Moderator edit to format code correctly]

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [30 November 2024 18:56 UTC](https://discourse.nodered.org/t/insert-text-from-web-to-mysql-and-show-it-on-website-securely/93590/2 "2024-11-30T18:56:58Z")

</div>

Hi @hbb999

Without understanding your complete setup, its hard to provide a clear review of any security weaknesses.

BUT! I think you're on the right track, with using the SQL escape magic.

Personally - I struggle with the differences between using `?` or named parameters (`:name`). -at least with the Node RED Nodes around.

I think @dceejay and @Steve-Mcl maybe helpful here.  
I am an `Ms`SQL user - not so much with `My`SQL

**EDIT**

* * *

BUT if you do see a need to clean any input / output - use the `link call` methods, you will thank yourself later.

When you can call into them for any operation needing the same treatment

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [1 December 2024 18:11 UTC](https://discourse.nodered.org/t/insert-text-from-web-to-mysql-and-show-it-on-website-securely/93590/3 "2024-12-01T18:11:01Z")

</div>

> [@hbb999](#):
>
> Is this safe against XSS?

It should be safe. Though you may get some weird visuals. However, you should do that conversion on input and not on output so that you cannot store anything unsafe in your db.

> [@hbb999](#):
>
> Is this method safe against MySQL injection?

I think it is. Though the normal advise is to use a prepared statement which also has the advantage of being more efficient.

However, I don't recognise the `evaluate` statement and a quick search didn't throw up any ideas. I would try without that unless you know for sure what it does.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [1 December 2024 19:18 UTC](https://discourse.nodered.org/t/insert-text-from-web-to-mysql-and-show-it-on-website-securely/93590/4 "2024-12-01T19:18:36Z")

</div>

Umm evaluate is the name of the table!

There seem to be alternative formats available for the prepared query; the one you are using with an array and questionmarks, and one with an object and key names:

```auto
msg.payload = {"temperature": 1100, "color": "light yellow"}
msg.topic = "insert into temper (temperature, color) values (:temperature, :color)"

```

This seems better to me because the source of the data is explicit not implicit.

I tried and failed to make a MySQL query with SQL injection so can't tell if the two approaches are equally safe.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [1 December 2024 19:46 UTC](https://discourse.nodered.org/t/insert-text-from-web-to-mysql-and-show-it-on-website-securely/93590/5 "2024-12-01T19:46:17Z")

</div>

> [@jbudd](#):
>
> Umm evaluate is the name of the table!

🤣 Dopy dad on a Sunday!

---

<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:** [1 March 2025 19:46 UTC](https://discourse.nodered.org/t/insert-text-from-web-to-mysql-and-show-it-on-website-securely/93590/6 "2025-03-01T19:46:35Z")

</div>

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