Unable to inject SQL query

General
,
1

Hi,
I want to perform an SQL query against a database (MariaDB).
As soon as I configure a plugin (node-red-mysql-r2 for example), I cannot deploy my flow if I have an SQL query in my inject node (deploy failed: forbidden). I didn't see anything about these errors in the logs (even in trace mode).

image
image559×546 15.1 KB

image

When I delete the plugin configuration (Global Configuration Nodes) or if I delete msg.topic from the ijnect node, I can deploy the flow again.

If I make a very basic request, like "SELECT * FROM USERS;" then I can deploy my flow.

Version: 3.0.2
Environment: Docker

I really don't understand what's going on, do you have an idea ?

2

Almost certainly it would be better to use node-red-node-mysql. not least because the maintainers are also the authors of Node-red.

You have to setup Mariadb to allow connections from other machines. Maybe this is also true for connections from other Docker containers on the same machine.

3

I just used node-red-node-mysql, but the problem is exactly the same.
As soon as I create my inject node with my SQL query, it is impossible to save the flow

I confirm that I can connect to the database from another machine and that the request returns a valid result :

image

4

What exactly happens when you try to deploy?

5

Also repeat the exercise but using the standard node. Export the the nodes and paste the export here. Then we can try it.

Configure the inject to not inject automatically. Does it still fail to deploy, or is it when you click the inject that it fails?

6

Here the code:

[
    {
        "id": "fcb9da7f7a9651c5",
        "type": "tab",
        "label": "SQL",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "80fc3bf2aa84e7d1",
        "type": "inject",
        "z": "fcb9da7f7a9651c5",
        "name": "",
        "props": [],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "x": 590,
        "y": 220,
        "wires": [
            [
                "3f3657dccbad3463"
            ]
        ]
    },
    {
        "id": "3f3657dccbad3463",
        "type": "mysql",
        "z": "fcb9da7f7a9651c5",
        "mydb": "bc82281c113049b7",
        "name": "",
        "x": 800,
        "y": 220,
        "wires": [
            [
                "9b778df266405d0c"
            ]
        ]
    },
    {
        "id": "9b778df266405d0c",
        "type": "debug",
        "z": "fcb9da7f7a9651c5",
        "name": "debug 10",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 1000,
        "y": 200,
        "wires": []
    },
    {
        "id": "bc82281c113049b7",
        "type": "MySQLdatabase",
        "name": "",
        "host": "mariadb.local",
        "port": "3306",
        "db": "homeassistant",
        "tz": "",
        "charset": "UTF8",
        "credentials": {}
    }
]

Try to add msg.topic into the inject node with these value:

SELECT table_schema "homeassistant", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

.. and then deploy the flow. if it's like me, you're going to have this error pop-up that will appear:
image

Even if i remove the link between the inject and the sql node, i can't deploy the flow:
image

7

Your inject node is empty. Are you saying that it deploys ok if you have no inject node, but when you add an empty inject node not connected to anything, that it will not deploy?

8

Also tell us what hardware/os you are running on and how you installed node-red. Is this a home assistant install?

9

It's not quite that. I can deploy an inject node without any problem as long as I don't have my SQL query in it, like in the json file i sent you
But if I have my SQL query declared in it, I cannot deploy the flow, even if my inject node is not linked to my SQL node (or even if it is linked to nothing at all).
If I delete my SQL node and delete its parameters, then I can deploy my inject node with my SQL query and deploy the entire flow.

Nodered is deployed in docker mode (on an Intel NUC). My entire HomeAssistant installation is installed on this same machine in docker mode.

10

The flow you posted has not got anything in the inject node

11

Here is the flow with SQL query into inject node:

[
    {
        "id": "a46cf7a2ad40f4ad",
        "type": "tab",
        "label": "SQL",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "56ef87dcda28e55e",
        "type": "inject",
        "z": "a46cf7a2ad40f4ad",
        "name": "",
        "props": [
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "SELECT table_schema \"homeassistant\", sum( data_length + index_length ) / 1024 / 1024 \"Data Base Size in MB\" FROM information_schema.TABLES GROUP BY table_schema;",
        "x": 610,
        "y": 80,
        "wires": [
            [
                "ee3e426232523c73"
            ]
        ]
    },
    {
        "id": "ee3e426232523c73",
        "type": "mysql",
        "z": "a46cf7a2ad40f4ad",
        "mydb": "bc82281c113049b7",
        "name": "",
        "x": 780,
        "y": 80,
        "wires": [
            [
                "78fb4048fd11c22b"
            ]
        ]
    },
    {
        "id": "78fb4048fd11c22b",
        "type": "debug",
        "z": "a46cf7a2ad40f4ad",
        "name": "debug",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 950,
        "y": 80,
        "wires": []
    },
    {
        "id": "bc82281c113049b7",
        "type": "MySQLdatabase",
        "name": "",
        "host": "mariadb.local",
        "port": "3306",
        "db": "homeassistant",
        "tz": "",
        "charset": "UTF8",
        "credentials": {
            "password": "xxxxxxx"
        }
    }
]
12

That deploys fine for me. I think this must be a Home Assistant issue, as that type of popup is not something I have ever seen. I don't use HA and very few here do.

Probably you should ask on a Home Assistant forum.

13

I found the solution, it's my WAF (owasp/modsecurity) which blocked the flows.
Thanks for your help

14

Why only when you added the inject node?

15

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