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

General
,
1

I send an url to our clients to give a feedback about our service: 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:

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:

msg.payload = msg.payload
.replace(/&/g, "&")
.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]

2

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 MsSQL user - not so much with MySQL

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

3

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.

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.

4

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:

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.

2 Likes
5

:rofl: Dopy dad on a Sunday!