How to stop single quotes causing "Syntax" and "Malformed query" SQL errors

  • 21 January 2022
  • 0 replies
  • 1389 views

Userlevel 7
Badge +11
  • Community Manager
  • 1610 replies

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:

e71f3c6d5f702aecff6ed445591ec6eb.png

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:

9d9f48154de1c16d958428ced80d8461.png
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:

c482e99394c35ccfe701810adce0f9bc.png

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: 

dfcd2bba7ea35ecfbaa6ddbbf29c227a.png

This will then convert "Moe's Tavern" into the escaped version "Moe\'s Tavern":

6ed1a131f85d4073f16d09bb5706aa87.png
Which we can then use in our query:

452fa3d8d9033e987e188044221c93d7.png
dd01c8e2118d1ce5239d9d043f7ff6ea.png
Hurray, it worked! :tada:

Well, that’s all from me for now. I hope you find this useful.

Happy Zapping! :zap:


0 replies

Be the first to reply!

Reply