When text with a single quote ' is passed in a query to an app using SQL (or a similar query language) it can cause an error. This is because strings in SQL queries are delimited with single quotes. So, if a text string in a query contains a single quote, it will end the query early and result in a "Syntax error" or "MALFORMED_QUERY” error. Like this one from Salesforce:
How to prevent these errors?
There are a few ways to help prevent these errors:
-
Option 1: Replace any single quotes used with two single quotes ''.
-
Option 2: Escape the single quote using \'.
-
Option 3: Remove the single quote if it's not essential to the meaning of the string.
Option 3 isn't ideal by any means so let's just focus on the other two!
Option 1
Using '' escapes the second single quote so that it is viewed as an actual single quote in the text, instead of being an end to a SQL string. So you would escape the single quote like this:
And if the value has two consecutive single-quotes, each one has to be escaped. So there would need to be four single-quotes ''''.
Option 2
Using \' is another way of escaping the single quote. For example, if a string contained a single such as Moe’s Tavern you would escape the single quote like so:
Now, the above options are great if we're wanting to manually escape the single quote from the query. But if we want to automate the escaping of the single quote, we'll need to enlist the help of our Formatter app.
To do that, we'll add a Formatter (Text > Replace) action in to convert the single quote. For example:
This will then convert "Moe's Tavern" into the escaped version "Moe\'s Tavern":
Which we can then use in our query:
Hurray, it worked! 
Well, that’s all from me for now. I hope you find this useful.
Happy Zapping! 