Escaping ' for MySQL

[mbuk]

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.

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

Thanks in advance.

[Kent1]

Hi,

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

Let me know if it’s work

[mbuk]

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

[Kent1]

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

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

[mbuk]

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.

[sirdavidoff]

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

[mbuk]

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

[Kent1]

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”]) }};

[mbuk]

Thanks, that gives the same error unfortunately.

[Kent1]

Could you show the output of your sql query please ?

[mbuk]

[Kent1]

Can you show the sql query please not the error ?

[mbuk]

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

[Kent1]

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

Write just

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

[mbuk]

That’s worked now Kent.
Thanks so much!