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.
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?
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
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.
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
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.
Thank you! Giving your advice (including not giving up!) got it to work!
The long term solution is to use Paramter Queries like you hinted towards originally.
Here is a VERY simple example where I am skipping not important details:
Using MySQL execute SQL, change the options to be Query parameters, include the expression that you want to include (no need to worry about special characters!) and in the Query include a parameter (IE: $1 for the first parameter) and it JUST WORKS! Magic.
I also solved this issue by using query parameters (which are safer anyway), but I wanted to point out there is definitely something weird happening when you try to escape parameters ($1, etc) in SQL queries. Is there anyway to fix that?
For example, if you try to insert a cash amount as text like “$13” into an SQL table, n8n will replace that text with NULL. I tried all the suggestions listed in the thread - backticks, backslashes, etc - but none of them worked. The only thing I could get to work was separating the dollar sign from the number and then using REPLACE to put the text back. So you’d use something like: REPLACE('$ 13', '$ ', '$')
Here’s an example workflow:
Is there anyway to escape these query parameters in SQL queries?