# SQL injection attacks security issue mysql

**URL:** <https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017>\
**Category:** General\
**Tags:** security, mysql\
**Created:** [27 June 2024 20:04 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017 "2024-06-27T20:04:33Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![granxavi](https://avatars.discourse-cdn.com/v4/letter/g/b5a626/32.png) [@granxavi](https://discourse.nodered.org/u/granxavi)\
**Post date:** [27 June 2024 20:04 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/1 "2024-06-27T20:04:33Z")

</div>

I HAVE ALREADY TRIED PREPARED STATEMENTS and STORED PROCEDURES but nothing seems to work.

I am trying to implement a simple API where users can register on the DB. Everything works fine as expected. However, I noticed that I am vulnerable to injection attacks. For example, if I send ';delete from users;' inside the username, the table users is deleted. I hope that using multiple SQL users inside Mariadb with different privileges will solve the issue, however, before trying that I want to find a solution inside Node-red.

I will write parts of all the codes I have tried. All my codes work but they are still vulnerable. I know that I have to sanitize the input but I do not know exactly how and everything I have tried in that respect does not work. Please help.

This is a prepared statement according to MariaDB.

```auto
var query = "SET @s1 = 'INSERT INTO users(username, password_hash) VALUES(?, ?);';" +
"PREPARE stmt1 FROM @s1;" +
"SET @a = '"+ username +"';" +
"SET @b = '" + password + "';" +
"EXECUTE stmt1 USING @a, @b;" +
"DEALLOCATE PREPARE stmt1;";

```

I made a stored procedure inside the DB, and I called with this.

```auto
let query = "CALL newUser('" + username +"', '" + password +"', '" + name + "')";

```

This would be the prepared statement according to the documentation of the node node-red-node-mysql

```auto
var query = "INSERT INTO users(username, password_hash) VALUES(?, ?);";
msg.payload = [username, password];
msg.topic = query;

```

This one is according to the documentation of node node-red-contrib-mysql-config

```auto
msg.topic = 'INSERT INTO users(username, password_hash) VALUES(:user, :pass);';
msg.payload = { user: username, pass: password };

```

This is the weakest one

```auto
let query ="insert into users(username, password_hash) values('" +
username + "', '"+ password + "');"

```

---

<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:** [27 June 2024 20:18 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/2 "2024-06-27T20:18:55Z")

</div>

> [@granxavi](#):
>
> I will write parts of all the codes I have tried. All my codes work but they are still vulnerable. I know that I have to sanitize the input but I do not know exactly how and everything I have tried in that respect does not work. Please help.

So, all user input MUST ALWAYS BE SANITISED as you've already learned. For SQL injection and some other common attacks, you need to:

- Limit the input string length to something sensible. For example user ID's are probably never longer the 64 chars, maybe less, certainly not more than 255 characters. Passwords should certainly never be longer than 255 characters (funny aside: I always set my passwords to 63 random characters to test whether systems I use have decent password settings 🙂 )

- Passwords must never be stored. You need to convert them into a hash string as early as possible. This will, of course, convert any `;` or other meaningful characters to something safe since the hash string is almost certainly just HEX (I think?).

- Filter out characters that are meaningful in SQL statements. In other words, use either a change node or a function node to remove `;` from any input strings.

Do these things BEFORE you go anywhere near your SQL.

BTW, limiting input string length is often overlooked but is important to stop fuzzing attacks.

Also, make sure that you only use POST and not GET http types for inputs like this. GET would put parameters onto the URL which brings a whole host of other issues.

And, of course, security without HTTPS (TLS encryption) is no security at all.

---

<div class="post-metadata">

**Author:** ![AndrewBeasley](https://avatars.discourse-cdn.com/v4/letter/a/f17d59/32.png) [@AndrewBeasley](https://discourse.nodered.org/u/AndrewBeasley)\
**Post date:** [28 June 2024 16:56 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/3 "2024-06-28T16:56:34Z")

</div>

I've not tried it but possible [GitHub - pariazar/perfect-express-sanitizer: a complete package to control user input data to prevent Cross Site Scripting (XSS) ,Sql injection and no Sql injection attack](https://github.com/pariazar/perfect-express-sanitizer) can help?

As a bare minimum striping out none alphabetic characters help but reduces security for user passwords - as per @TotallyInformation states - hash ASAP and just store that.

---

<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:** [28 June 2024 17:26 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/4 "2024-06-28T17:26:37Z")

</div>

To support @TotallyInformation's input

> [@granxavi](#):
>
> I know that I have to sanitize the input but I do not know exactly how and everything I have tried in that respect does not work

- Run the password through a hash process (ensure its also hashed before comparison later)  
this should remove anything dodge, given you should output as a hexadeicmal string! and should be done before reaching the query  
[node-red-contrib-cryptography (node) - Node-RED](https://flows.nodered.org/node/node-red-contrib-cryptography)

- Strip any unwanted characters in the `username` (and any other field you need)

```auto
const yikesChars = [';',':','--'] /* Example */
let sanitizedUsername
[...msg.payload.username].forEach(char => { 
	if(!yikesChars.includes(char){
        sanitisedUsername += char
    }
});
msg.payload.username = sanitizedUsername

```

I'm sure there are cleaner/better ways, but I was bored (and I am S\*\*T) at regex 😄

However, I always thought using named params was already a protection method?

like you already stated?

```auto
var query = "INSERT INTO users(username, password_hash) VALUES(?, ?);";
msg.payload = [username, password];
msg.topic = query;

```

---

<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:** [28 June 2024 17:46 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/5 "2024-06-28T17:46:56Z")

</div>

> [@marcus-j-davies](#):
>
> I was bored (and I am S\*\*T) at regex

Shouldn't that be S.{2}T ? 🤪

---

<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:** [28 June 2024 17:47 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/6 "2024-06-28T17:47:57Z")

</div>

Buurrrnnn! 😂

---

<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:** [28 June 2024 19:24 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/7 "2024-06-28T19:24:05Z")

</div>

> [@marcus-j-davies](#):
>
> However, I always thought using named params was already a protection method?

It should be used as well. But practicing "Secure by Design" is always a good thing and that will still have you sanitising the inputs before anything else.

---

<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:** [28 June 2024 19:25 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/8 "2024-06-28T19:25:32Z")

</div>

I was ready for that response Julian (but in agreement) 😊

---

<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:** [28 June 2024 19:27 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/9 "2024-06-28T19:27:38Z")

</div>

Actually, there is a new UK Government initiative and framework that is being burned into all public sector processes called ["Secure by Design"](https://www.security.gov.uk/guidance/secure-by-design/) so it is rather fresh in my mind 😆

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [28 June 2024 19:38 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/10 "2024-06-28T19:38:01Z")

</div>

> [@TotallyInformation](#):
>
> UK Government initiative and framework that is being burned into all public sector processes called ["Secure by Design"](https://www.security.gov.uk/guidance/secure-by-design/)

Is that the same government that had secured the electoral role?

---

<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:** [28 June 2024 20:31 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/11 "2024-06-28T20:31:50Z")

</div>

Which is maybe why they felt the need to be a bit more explicit with public sector about security! 🙂

---

<div class="post-metadata">

**Author:** ![granxavi](https://avatars.discourse-cdn.com/v4/letter/g/b5a626/32.png) [@granxavi](https://discourse.nodered.org/u/granxavi)\
**Post date:** [29 June 2024 00:24 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/12 "2024-06-29T00:24:01Z")

</div>

> [@marcus-j-davies](#):
>
> To support @TotallyInformation's input
> 
> ```auto
> const yikesChars = [';',':','--'] /* Example */
> let sanitizedUsername
> [...msg.payload.username].forEach(char => { 
> if(!yikesChars.includes(char){
> sanitisedUsername += char
> }
> });
> msg.payload.username = sanitizedUsername
> 
> ```

This function helped a lot, thanks @marcus-j-davies I have improved it and will share it in the next comment.

> [@marcus-j-davies](#):
>
> However, I always thought using named params was already a protection method?
> 
> like you already stated?
> 
> ```auto
> var query = "INSERT INTO users(username, password_hash) VALUES(?, ?);";
> msg.payload = [username, password];
> msg.topic = query;
> 
> ```

@marcus-j-davies I wish named params worked, but nope, they are useless, at least in node-red.

---

<div class="post-metadata">

**Author:** ![granxavi](https://avatars.discourse-cdn.com/v4/letter/g/b5a626/32.png) [@granxavi](https://discourse.nodered.org/u/granxavi)\
**Post date:** [29 June 2024 00:29 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/13 "2024-06-29T00:29:41Z")

</div>

Thanks to @marcus-j-davies I figured out what I could do. The next code sanitizes the string, cleaning special characters and SQL Query words. Feel free to use it and to improve it.

```auto
const yikesChars = [';', ':', '--', `'`, '\\', '<', '>', '!', '=', '(', ')'];
function deleteWords(inputString) {
    const sqlKeywords = ['SELECT', 'FROM', 'DELETE', 'UPDATE', 'CREATE', 'INSERT', 'DROP', 'ALTER', 'TRUNCATE'];
    let normalizedInput = inputString.toLowerCase();
    sqlKeywords.forEach(keyword => {
        let regex = new RegExp(`\\b${keyword.toLowerCase()}\\b`, 'gi');
        normalizedInput = normalizedInput.replace(regex, '');
    });
    return normalizedInput;
}

function sanitizeString(inputString, charsToExclude) {
    let sanitizedString = '';
    let chars = inputString.split('');
    for (let i = 0; i < chars.length; i++) {
        let substr = chars[i] + (chars[i + 1] || '');
        if (charsToExclude.includes(substr)) {
            i++;
            continue;
        }
        if (!charsToExclude.includes(chars[i])) {
            sanitizedString += chars[i];
        }
    }
    sanitizedString = deleteWords(sanitizedString);
    return sanitizedString;
}

//using it 
username = sanitizeString(username, yikesChars);

```

---

<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:** [29 June 2024 06:45 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/14 "2024-06-29T06:45:35Z")

</div>

> [@granxavi](#):
>
> For example, if I send ';delete from users;' inside the username, the table users is deleted.

I am having trouble replicating this.  
With no stored procedure defined in the database, my code

```auto
msg.payload = {"username": "Donald Trump; delete from users;"}
msg.topic = "insert into users (username) values (:username)"
return msg;

```

gives me these records

```auto
> select * from users;
+----+----------------------------------+---------------+
| id | username | password_hash |
+----+----------------------------------+---------------+
| 1 | Joe Biden | NULL |
| 2 | Donald Trump; delete from users; | NULL |
+----+----------------------------------+---------------+
2 rows in set (0.001 sec)

```

What am I doing wrong?

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [29 June 2024 08:04 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/15 "2024-06-29T08:04:14Z")

</div>

I thought it was obvious, the only way to remove Trump seems to be by the ballot box. He seems invulnerable to any form of attack at the present time. (this comment does not express any form of support for any political side, it is simply a joke)

---

<div class="post-metadata">

**Author:** ![granxavi](https://avatars.discourse-cdn.com/v4/letter/g/b5a626/32.png) [@granxavi](https://discourse.nodered.org/u/granxavi)\
**Post date:** [30 June 2024 03:16 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/16 "2024-06-30T03:16:38Z")

</div>

> [@jbudd](#):
>
> I am having trouble replicating this.
> 
> ```auto
> msg.payload = {"username": "Donald Trump; delete from users;"}
> msg.topic = "insert into users (username) values (:username)"
> return msg;
> 
> ```
> 
> What am I doing wrong?

I send an http post with the user's header using ';delete from users;' and i take it as my username. I don't know if it can be replicated inside the node. If it were possible to replicated it inside the node then you are missing the '; initials and the ' final, but again, Idk if it can be replicated like that.

---

<div class="post-metadata">

**Author:** ![HarryPottar](https://avatars.discourse-cdn.com/v4/letter/h/bb73d2/32.png) [@HarryPottar](https://discourse.nodered.org/u/HarryPottar)\
**Post date:** [4 July 2024 13:32 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/17 "2024-07-04T13:32:19Z")

</div>

I am a bit late to the party.  
The correct answer is to use placeholders in the SQL assigned to the message topic property and pass values to the message payload; this has already been stated.

**Escape Function**  
Why not use the Node [MySQL built-in escape](https://github.com/mysqljs/mysql#escaping-query-values) function for those using custom functions?

You can load the MySQL node as a global function in settings and call in the function node.

```auto
const mysql = global.get("mysql");

```

Or dynamically load it in the function node setup tab.  
Simple go to the Setup Tab and type msql for the Module name

![Screenshot 2024-07-04 at 8.57.33 AM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/b/9b3258de6710921d90171fb6b92b8233ef5e7838.png)

Once you have loaded it once, it will then be a drop-down option in the future  
 ![Screenshot 2024-07-04 at 8.58.23 AM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/7/e711c9377c07fd6225f0fc8ae58a81e1a4048155.png)

```auto
//start transaction;
let insert = "START TRANSACTION;";

//insert part number
insert += "INSERT INTO vehicle_partnumbers (partnumber,name,ShortDescription,lastmodified) VALUES (" + mysql.escape(data.partnumber) + "," + mysql.escape(data.name) + ",";
insert += mysql.escape(data.shortdescription) + "," + mysql.escape(data.lastmodified) + ") ";
insert += "ON DUPLICATE KEY UPDATE name=" + mysql.escape(data.name) + ", ShortDescription=" + mysql.escape(data.shortdescription) + ", lastmodified=" + mysql.escape(data.lastmodified) + ",";
insert += "partnumber_id=LAST_INSERT_ID(partnumber_id);";

insert += "SET @last_id_in_vehicle_partnumbers = LAST_INSERT_ID();";

```

**Escape ID Function**  
If you can't trust an SQL identifier (database / table / column name) because it is provided by a user, you should escape it with escapeId()

```auto
mysql.escapeId(identifier)

```

Example

```auto
var sorter = 'date';
var sql = 'SELECT * FROM posts ORDER BY ' + mysql.escapeId(sorter);

```

It also supports adding qualified identifiers. It will escape both parts.

```auto
var sorter = 'date';
var sql = 'SELECT * FROM posts ORDER BY ' + mysql.escapeId('posts.' + sorter);
// -> SELECT * FROM posts ORDER BY `posts`.`date`e

```

Alternatively, you can use `??` characters as placeholders for identifiers you would like to have escaped like this:

```auto
var userId = 1;
var columns = ['username', 'email'];
var query = connection.query('SELECT ?? FROM ?? WHERE id = ?', [columns, 'users', userId], function (error, results, fields) {
  if (error) throw error;
  // ...
});

console.log(query.sql); // SELECT `username`, `email` FROM `users` WHERE id = 1

```

Use a single ? for a query value and a double ?? for identifiers.

I have used both methods.

---

<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:** [2 October 2024 13:32 UTC](https://discourse.nodered.org/t/sql-injection-attacks-security-issue-mysql/89017/18 "2024-10-02T13:32:59Z")

</div>

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