Skip to content

Escaping quotes in a DataSource body attribute

Jump to bottom
Jon Udell edited this page Feb 13, 2025 · 2 revisions

Writing a SQL query, in a DataSource body, that includes single quotes

User: i have a tricky js quoting/escaping problem to solve.

i am an environment called xmlui

i can do this:

<DataSource id="count" url="http://hn.jonudell.info:8081/query" 
    body='{ { "sql": "select count(*) as count from hn" } }'
    method="POST" />

the body attribute is a string

the outer { } wraps a js expression

in this case it returns a json literal

with me so far?

Assistant: Yes, I follow so far.

User: how can i write the query

select count(*) as count from hn where title like '%Ask HN%'

Assistant: First, keep in mind that for your DSL to recognize a valid JSON literal (the { "sql": ... } part), you need double quotes for the JSON keys. Meanwhile, you’re also embedding that JSON literal in a string attribute in XML, so you have to pick an outer quoting style and then escape any conflicting quotes. A common approach is:

  1. Use single quotes around the entire XML attribute, so body='...'.
  2. Inside it, use double quotes for the JSON keys: "sql": "...".
  3. If the SQL itself requires single quotes (around Ask HN), you escape them inside the JSON string.

In short, you get something like:

body='{ { "sql": "select count(*) as count from hn where title like \'%Ask HN%\'" } }'

Step by step:

  • The XML attribute is '...'.
  • That contains a JavaScript expression { { "sql": ... } }.
  • Inside that expression is valid JSON "sql": "...".
  • Within that JSON string, single quotes around Ask HN are escaped as \'Ask HN\'.

Hence, when the DSL processes it, you end up with a JSON object whose sql property is:

select count(*) from hn where title like 'Ask HN'

That should work cleanly in your environment.

User: this environment won't accept \'

Assistant: One workaround is to switch to an HTML/XML entity for the single quotes inside your query string. Since \' isn’t allowed in this environment, you can replace ' (around Ask HN) with &apos; (or &#39;). For example:

<DataSource id="count"
    url="http://hn.jonudell.info:8081/query"
    body='{ { "sql": "select count(*) as count from hn where title like &apos;%Ask HN%&apos;" } }'
    method="POST" />

Breaking it down:

  • The XML attribute is single-quoted: body='...'.
  • The JavaScript expression is inside curly braces: { { "sql": "..." } }.
  • We need "sql": "..." for valid JSON property/value.
  • Inside that JSON string, the single quotes around Ask HN get replaced with &apos; (or &#39;) so that the overall attribute remains valid XML/HTML.

User: that's it!

Assistant: Great! Glad that worked out for you. Let me know if there’s anything else I can help with.

Clone this wiki locally