Search code examples
intuit-partner-platform

How to escape single-quotes in Query Filter

Using QBO V3 API and I'm trying to query for Customers with an apostrophe in their names but I cannot figure out how to properly quote the name.

I have used the API Explorer and when I enter a query like this:

select * from customer where displayname like '%O\'Halloran'

Then the API Explorer works and says the request-uri is really:

https://qb.sbfinance.intuit.com/v3/company/1002341430/query?query=select * from customer where displayname like '%25O%5C'Halloran'

Notice a couple of things in my query:

  1. I have to use single-quotes around the value
  2. I have to escape the single-quote myself.

But then notice in the URI that API Explorer gives back to me: it keeps the single-quote wrapper string but then does NOT escape the single quote inside the value.

If I turn around and use the exact request URI that the API Explorer is using - it does NOT work. I get a query filter exception. So clearly what API Explorer is showing me is not the real query.

Since none of this is documented I'm left trying to reverse-engineer what the incantation is but I cannot determine it. I've looked at the PHP SDK but its not addressed there either, they assume the callers (like us) will know what to do themselves.

Solution

  • You need to escape it with \, and then also URL encode the actual URL request you're sending.

    The actual URL you're hitting (for a customer query of LIKE '%Keith O'Malley%' should be something like:

    https://quickbooks.api.intuit.com/v3/company/730176505/query?query=SELECT+%2A+FROM+Customer+WHERE+FullyQualifiedName+LIKE+%27%25Keith+O%5C%27Mally%25%27+

    With a full HTTP request looking like:

    GET https://quickbooks.api.intuit.com/v3/company/730176505/query?query=SELECT+%2A+FROM+Customer+WHERE+FullyQualifiedName+LIKE+%27%25Keith+O%5C%27Mally%25%27+ HTTP/1.1
Content-Type: text/plain
Authorization: OAuth realm="", oauth_signature_method="HMAC-SHA1", oauth_signature="C9mzgdURjATMEymtAySNe%2BYBW7Q%3D",  oauth_nonce="3XDdP", oauth_timestamp="1394038522", oauth_token="lvprdgeo8rx8r1Ul10Bf474KjHEjcLFSVYttLJjcR2pFtIiD", oauth_consumer_key="qyprdlGJ4gWv4sMW0syilH2o4KirQe", oauth_version="1.0"

    If you're not committed to using the Intuit DevKit that doesn't escape things properly, there's an open-source QuickBooks PHP DevKit available which handles these things correctly:

    Here's an example of specifically what you're looking for:

    Along with a pile of other working examples: