How to troubleshoot MySQL query with parameters?

Questions
1

Hello -

I am running N8N self hosted version 1.56.2 with MySQL node version 2.4 (Latest) and I am having issues with the MySQL Execute node. When I run the node below, I am getting null values passed in the middle of the query. Any reason why it is replacing $19 with null? If I run the update in MySQL directly it seems to work OK.

Thank you

image
image2138×898 172 KB

2

It looks like your topic is missing some important information. Could you provide the following if applicable.

  • n8n version:
  • Database (default: SQLite):
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app):
  • Operating system:
3

Hello @n8n_rookie ,

Thanks for bringing to our attention your issue.

I believe the issue you are facing is because $ may be used to represent parameters passed into. So $19 is being tried to be replaced and as it does not exist it may result as null.

Can you try to escape it? If you try to escape it with backtick `, any luck on doing it?

I was checking the code from GIt n8n/packages/nodes-base/nodes/MySql/v2/actions/common.descriptions.ts at 7fd0c71bdc0e3d15902d100368ffe777ccf81adb · n8n-io/n8n · GitHub

Or use the backslash: https://medium.com/@onlinemsr/dollar-sign-in-javascript-a-comprehensive-guide-daa47a90d2a5#:~:text=Escaping%20the%20dollar%20sign%20in,it%20using%20a%20backslash%20(%24).&text=You%20can%20use%20a%20backslash,but%20it%20is%20not%20mandatory.

This is where I am basing my assumptions to check and validate.

I hope this points to the right way. Let us know if this helps.

Cheers!

1 Like
4

Wow GREAT CATCH - I will try that out. Curious is that a bug or something standard? I haven’t seen an backtick in SQL like that before except when a table name has spaces

5

Hey @n8n_rookie ,

Thanks for replying back.

Try not only with backtick but also backslash . We would need to check further to understand if this is a bug or if the Node expect the users to escape characters when needed.

We look forward to hearing from you.

Flavio

6

Unfortunately still no luck. I am doing the swap in the MySQL node by instead of using:

{{ $json.message.content }}

I am using (assuming $ value should be something like $754.39):

{{ $json.message.content.replaceAll(‘$’, ‘$$’) }} ---- This one will put NULL.39 instead of $754.39

{{ $json.message.content.replaceAll(‘$’, '$') }} ---- This one will put NULL.39 …

{{ $json.message.content.replaceAll(‘$’, ‘$’) }} ---- This one will put NULL.39 …

image
image1302×742 36.5 KB

Again, I appreciate any help! I realize there is a sloppy workaround available (I could put a placeholder and than replace the placeholder text later on) but I really think N8N can probably handle this a better way

7

@n8n_rookie ,

Thanks for coming back and for sharing the detailed steps you have tried.

I was actually thinking in a different way(I should have provided details, my bad).
Can you try to add the escape like my example below:

{{ $json.message.content.replace(/\$/g, '\\$') }}

I tested this in a javascript online editor and it replaced “Iphone only $19.88” for “Iphone only \$19.88”.

This is what I think that could help.

Another option, instead of escape the $ character would be to use Parameterized Queries(But I am not sure we can use it via n8n by heart, need to be checked). That would mean to use something like:

INSERT INTO your_table (column_name) VALUES (?)

and then pass the string "“Iphone only $19.88” as value.

8

Hmmm so odd, even with:

{{ $json.message.content.replace(/$/g, ‘\$’) }}

I am still getting:

NULL.39

To make sure we are on the right path, I tried:

{{ $json.message.content.replace(/$/g, ‘TESTTESTTEST’) }}

and this does indeed put TESTTESTTEST754.39 (which is expected!)

9

Hello @n8n_rookie ,

Thanks for the quick attempted.
I quickly compared and I see that in the previous comment my suggestion was missing one extra backslash. CAN you try:

{{ $json.message.content.replace(/$/g, ‘\\$’) }}

Adding the image of my test for reference here:

Screenshot 2024-09-06 at 15.06.40
Screenshot 2024-09-06 at 15.06.401990×866 88.6 KB

10 
Error: invalid syntax at Expression.renderExpression

hmmm… I need to keep messing with this something seems off

11

@n8n_rookie ,

So I am assuming you tried with the 2 backslash as per my last suggestion and now you got this different error.

Can you share a simplified workflow INPUT ==> MySQL that the issue happens? So we can have extra pair of eyes to check if something is missing?

I will see if I can reproduce something based on the image already shared. But having your workflow would be more precise. Don’t give up!

Flavio