Escaping ' for MySQL

Questions
1

Hi
I’ve seen a few solutions posted for similar problems, but I can’t seem to get this working. Hoping the awesome people here can help.
In the results from an http request I have names containing the ’ character. When trying to use the value in MySQL update statement, it fails. I need to escape this character and replace “St. David’s Home” with “St. David/'s Home” to allow the update statement to run successfully.

image

n8n version: Version 0.221.2
Database: MySQL
Installation: AWS EC2

Thanks in advance.

2

Hi,

I think you can use JSON.stringify(var) to escape all special chars.

Let me know if it’s work

3

How do I get that working in a module for my variable?

4

You can use this syntaxe {{ JSON.stringify($json.value) }}

Where $json.value is the variable that you want to escape

6

image
image738×442 39.4 KB

I’ve successfully escaped it using the syntax but still when we try to write to the sql table it’s not seeing it as escaped.

7

Could you please share your workflow? It’s not clear which operation of the MySQL node you’re using, or what the query is.

8

Hi
We’re trying to add the value from the variable into an existing record in the mysql table. Here’s the full statement:

UPDATE care_homes
SET provider_name = ‘{{ $json[“name”] }}’, provider_id = ‘{{ $node[“Set Provider Name”].json[“Provider ID”] }}’
WHERE location_id = ‘{{ $json[“location_id”] }}’;

It’s the “set provider_name” that fails due to the ’ character.

Thanks

9

Try this :

UPDATE care_homes
SET provider_name = {{ JSON.stringify($json[“name”]) }}, provider_id = {{ JSON.stringify($node[“Set Provider Name”].json[“Provider ID”]) }}
WHERE location_id = {{ JSON.stringify($json[“location_id”]) }};

10

Thanks, that gives the same error unfortunately.

11

Could you show the output of your sql query please ?

12

image
image1752×1540 327 KB

13

Can you show the sql query please not the error ?

14

Apologies Kent:
INSERT INTO location_details (
location_name
VALUES(
‘{{ JSON.stringify($json[“name”]) }}’,

15

When you use JSON.stringify you have to remove the quote befofe and after your var.

Write just

{{ JSON.stringify($json[“name”]) }}

16

That’s worked now Kent.
Thanks so much!