Get data with encoded where clause

Hi,

I’m getting a perfectly valid error, but unsure on how to properly handle this.

For context: I have a codeless API service that listens to a webhook coming from ActiveCampaign. This works fine and we are able to register user with the data from the webhook. One of the things we ask for is the organisation name, as users are tied to an organisation (separate table). If the organisation exists, we add them to the already existing organisation, otherwise we create a new organisation and add the new user to it.

This all works fine, but recently we discovered an issue where some users would not get registered and we figured out that it has to do with the character: " ’ ". So for the most recent example, someone tried to register with the organistion name: “Kinderhotel 't Hofje” and due to the ’ it didn’t work.

I also know why it doesn’t work, because it messes up the where clause string. We try to get/check the organisation in the following way:

I tried using the encode block, but it doesn’t encode the character.

So, how would I go about fixing this? Perhaps I can also do a different check to see if the organisation already exists, or maybe I should do something else differently? Any help would be appreciated.

The error I get, if it is relevant, is this: (added data form the webhook as well)

8:56:23.342 | Data | DEBUG | {"contact[id]":"30815","contact[email]":"joey+S@springlab.nl","contact[first_name]":"S'joey","contact[last_name]":"V'laf","contact[phone]":"012345678","contact[orgname]":"Hotel 't springertje","contact[customer_acct_name]":"Hotel 't springertje","contact[tags]":"Springloop proefmaand, Webhook Springloop proefmaand","contact[ip4]":"127.0.0.1","contact[fields][privacy]":"||Aangevinkt||","seriesid":"67"}
8:56:23.440 | SERVER_CODE | ERROR | [870980] Error: There seems to be an issue with the query. Please check the following sections for potential errors: - SELECTED properties: [] - WHERE clause: name = 'Hotel%20't%20springertje' - ORDER BY: [] - GROUP BY: [] - HAVING clause: Hint: Offending token 't' is placed between ''Hotel%20'' and '%', probably expected a token '2'. Please review these parts of your query and try again. at checkStatus (/usr/local/lib/node_modules/backendless-coderunner/node_modules/backendless-request/lib/request.js:343:9) at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

Hi @Joey_Fladderak ,

You need to escape ' by replacing it with ''. To do this you should use Replace RegExp block:
image

Regards, Andriy

Thanks! Works like a charm.

1 Like