MySQL node not escaping delimiter

[lathanar]

I’ve encountered a problem where I need to hunt through a field holding HTML with a LIKE clause searching for the semicolon character but N8N will not escape the ; and splits my SQL into multiple statements. I’ve also tried setting the node for a SELECT with a LIKE option and the value being %;% and it treats the SQL the same way. I’ve tried out other delimiters with the same result. Is there a way to get N8N to respect escaping the delimiter?

{
  "meta": {
    "instanceId": "b9f40a4c315801a43ae6743defcc4ea618f095b53920095ed118d28fa52f8e7d"
  },
  "nodes": [
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT m.custom_ship_time  FROM zuri.models m WHERE m.custom_ship_time LIKE '%\;%' LIMIT 10;",
        "options": {
          "detailedOutput": true
        }
      },
      "id": "2292d263-8eb3-4f6b-afca-9220cf1acec1",
      "name": "MySQL",
      "type": "n8n-nodes-base.mySql",
      "typeVersion": 2.1,
      "position": [
        1780,
        820
      ],
      "credentials": {
        "mySql": {
          "id": "rABdjCa45ZXKwuB5",
          "name": "MySQL - zuri schema - Production"
        }
      }
    }
  ],
  "connections": {}
}

Results:

[{
“sql”: “SELECT m.custom_ship_time FROM zuri.models m WHERE m.custom_ship_time LIKE ‘%",
“data”: {“stuff”}},
{
“sql”: "%’ LIMIT 10”,
“data”: {“custom_ship_time”: “stuff”
}}]

Also, if I do not set Output Query Execution Details (detailedOutput) to true the node only returns a success message, not any of the results.

[MutedJam]

Hi @lathanar, I am sorry you’re having trouble.

I tried reproducing this, but while the query returned in the sql field looks pretty rubbish (and I’ll definitely add this to our bug tracker for a fix) am getting the kind of result I’d expect using the latest version of the MySQL node.

My data looks like so, having one row with a semicolon and one without:

The query only returns the one with the semicolon as expected:

This was using the latest version of n8n 1.6.1 and using a fresh MySQL node.

Can you confirm how exactly your data looks like and which result you’d expect in the data column?

[Mulen]

Same thing happening to me.

n8n version 1.4.1

[lathanar]

Look at your results, the sql statement ends at the escape character. Add multiple rows in your sample data with a semicolon as a return result and you will see what I am talking about.

[MutedJam]

@MutedJam

the query returned in the sql field looks pretty rubbish

Yep, I noticed that part and have already added this to our bug tracker for a closer look and fix.

I did however not notice any problem with the actual results in the data column. Even with multiple rows containing a semicolon I am getting the expected response:

So it’d be great if you could provide example data using which this can be reproduced.

[lathanar]

Now turn off Output Query Execution Details and run your node to look at the results.

[MutedJam]

I’ve also seen this and documented it in the bug ticket.

However, with this option enabled I am getting the expected result count. Are you seeing the very same behaviour?

E.g. the problem only occurring in the sql field (but not the results in the data field) when enabling “Output Query Execution Details”, or when disabling “Output Query Execution Details”?

Or is there any other case?

[lathanar]

Yes, it gives the correct result back, it’s just the behavior is off. You can also reproduce the same results with setting the node to just be a select with a like filter

[MutedJam]

Thanks so much for confirming @lathanar! In this case I’ve actually understood the bug completely (but for some reason thought there was more to it).

We’ll post an update on this thread once this has been fixed.

[lathanar]

Just for anyone else having the issue that runs across this thread, I do have a workaround for it until the behavior is fixed. Replacing the semicolon with the char() value works.

SELECT m.custom_ship_time FROM zuri.models m WHERE m.custom_ship_time LIKE CONCAT(‘%’,char(59),‘%’) LIMIT 10;

[TomSpidge]

@lathanar

char(59)

I was just deciding whether to look up the char code for a semicolon and see if that helped, or to report the issue here first, and apparently I can do both together. Thanks!

[Mulen]

Is this fixed yet?

I try something like this query below

SELECT `term_id`, REPLACE(`name`, '&', '&') FROM `wpfc_terms`;

Although on my IDE it is working, n8n returns

[
{
"success": 
true
}
]

I have to enable Output Query Execution Details to show the data.

To make it work i had to write the query as

SELECT `term_id`, REPLACE(`name`, CONCAT(CHAR(38), "amp", CHAR(59)), CHAR(38)) AS `name` FROM `wpfc_terms`;

Thanks TomSpidge

[MutedJam]

Hi folks, I am afraid the issue reported in this thread is still waiting for a closer look by the engineering team.

[jan]

New version [email protected] got released which includes the GitHub PR 9207.